import data from Excel properly

JimyBiju

Hi,
I have created a table which contains a field with code data type.Then I wrote code to import data from excel to NAV table.The data is imported ,but not properly.The data is imported by calling the function ReadSheet() in the Excel Buffer table.
Ex.
The excel value is 20000000.But when it is imported to table, the value is changed to 20,000,000. How can I correct that :?:

If I change the format of Excel column to text and press F2 in the cells, then the data will be imported properly. Can I do it from navision using C/SIDE :?:

jordi79

excel treat the value 20000000 as a amount value and it is formatted as such. in excel, it would be good to out a single quote in front of 20000000. so instead of 20000000 then it will be '20000000. but you have to remove first single quotes when imported into NAV.

the 2nd solution is to do an EVALUTE of the imported value from Excel into NAV, and try to evalute the value to a decimal value. then if it is successful, you can format it as text, and remove commas. if you do not evaluate the value, and just remove commas, you may end up removing commas for fields that you intend to import commas from.

JimyBiju

I can't understand first solution. When the system read from excel sheet to Excel buffer table ,the excel value will be with comma symbol.Where do you want to put ' symbol? In excel sheet or in internal code?

I tried the second solution , but the other issue is if the excel value is 0000094 then the imported value will be 94. How can I solve it ?


Plz find a solution :-k

Bulat_Shamsutdinov

When you evaluating numeric value into f.e. integer variable it will always drop those zeros, try evaluating it into CODE var, so it will treat it like a code keeping all zeros.
EVALUATE(CodeVar, '0000094 ');
MESSAGE(CodeVar);
will message you 0000094.
so it will solve the trouble with second solution, but utility depends on what you want to do/achieve with that data.

Bulat_Shamsutdinov

Sry, I didnt see that it was Financials topic, never worked with nav version less that 3.60, but still, chances hi it might be the same there.