import data from Excel properly

JimyBiju
Member Posts: 5
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 :?:
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 :?:
0
Comments
-
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.0 -
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 :-k0 -
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.0 -
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.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions