Excel Buffer import: NAV removes leading zeroes of code fields

Business Central/NAV Developers Forum
1

I have a data sheet that I have successfully exported to Excel including the leading zeroes in front of some of the code fields (ex. 000010). This was done using NumberFormat := ‘@’.

But when I’m trying to import this spreadsheet back into Navision, then the leading zeroes are not imported and the content of the “Cell Value as Text” is only 10.

I think that I have seen a solution to this problem, but now I simply cannot find it again.

Can you help?

2

I always put a character before the code and then remove it after Something like a # or $ or ~ especially if there will be any user interaction involved, as they tend to mess up leading zeros.

3

Thanks David,

I like the suggestion, except I feel it is almost like cheating! [;)] But I guess sometimes you need to drive a longer way, if it’s going to take you home faster than the direct way…

4

Hi Erik,

Is it Data Migration tool?

Best regards,

4BzSoftware.

5

No it’s special code using the Excel Buffers.

6

Hi Erik,

I tested on standard Excel Buffer table, it imports successfully [:O] (“Cell Value as Text” contains 001, 002 …):

WITH lRec_ExcelBuffer DO BEGIN
lTxt_FileName := lCoU_ComDlg.OpenFile(‘Test’, ‘’, 2{=Excel}, ‘’, 0{=Open});
lTxt_SheetName := SelectSheetsName(lTxt_FileName);

IF (lTxt_FileName = ‘’) OR (lTxt_SheetName = ‘’) THEN
EXIT;

RESET;
DELETEALL;
OpenBook(lTxt_FileName, lTxt_SheetName);
ReadSheet();
END;
CU 50099 - Test Import from Excel.fob (3.09 KB)