I’m trying to import an Excel file into NAV using a report instead of a report. The Excel file format must be able to handle sheets.
When I’m performing the import a status bar displaying something like ‘100%’ is shown, yet no data are imported. No error messages are displayed.
What could be the problem?
Hope someone could help me!
Hello,
could you be a little more specific. I tried to understand but did not get it.
Are you importing the Excel-sheet into the NAV Excel Buffer? If yes, HOW do you do it?
However, your data might be imported but they are “just” in the Buffer. What do you want to do with this data? Or do you (want to) import a Budget?
Check out Report 81 - import budget from Excel. The Excel Buffer table knows how to read an Excel sheet into that table. From there you will have to analyze and process the data yourself.
Instead of using a dataport to import from an Excel file I’m trying to use a report. The data in the Excel file are first imported to the Excel Buffer table and from there to the Item and Item Vendor tables. When I check the Item and Item Vendor tables the data haven’t been imported.
What could be the problem? The Excel file or something else?
As you don’t get any error message, there is something “missing” or not working in your code. Are you sure you insert your item record created from the ExcelBuffer?
Can you post your code/report here?
Hi!
Thank you for your answer.
I have tried the message function to return inserted values from excel buffer, it seems you’re right, nothing seems to have been inserted from the excel file into excel buffer.
But still a dialog box appears, showing something like ‘100%’, indicating that the excel file is actually being read. However, it seems the values in the file are not inserted into excel buffer.
What could be the problem? The file to be imported is an xls file.
An XLS file cannot be imported directly because this file format is not supported by NAV.
A possibility to import the data from the XLS file would be to convert it to a format which is supported by NAV, for example to CSV format (this could be easily done by saving the file from exel and specifying the format CSV). After that you could import the CSV file with a “simple” dataport.
Annother possibility would be the direct import from excel as an automation server… - but I think you should prefer the first method.
An xls file CAN be import directly into Navision by using the read sheet function in the excel buffer.
Have you tried using the Excel Buffer not as a tempory table and seeing if it is populated?
My coding adapted so that it can run under NAS (no window!)
IF NOT CREATE(xlapp,TRUE) THEN;
xlapp.Workbooks.Open(FileName);
xlwrkbk := xlapp.ActiveWorkbook;
i := 1;
EndOfLoop := xlwrkbk.Worksheets.Count;
WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN
xlwrkshts := xlwrkbk.Worksheets.Item(i);
IF SheetName = xlwrkshts.Name THEN
Found := TRUE;
i := i + 1;
END;
IF Found THEN
xlwrksht := xlwrkbk.Worksheets.Item(SheetName)
ELSE BEGIN
xlwrkbk.Close(FALSE);
xlapp.Quit;
CLEAR(xlapp);
ERROR(Text004,SheetName);
END;
xlrange := xlwrksht.Range(ExcelBuf.GetExcelReference(5)).SpecialCells(11);
maxi := xlrange.Row;
maxj := xlrange.Column;
i := 1;
REPEAT
j := 1;
ExcelBuf.VALIDATE(“Row No.”,i);
REPEAT
ExcelBuf.VALIDATE(“Column No.”,j);
ExcelBuf.“Cell Value as Text” := DELCHR(FORMAT(xlwrksht.Range(ExcelBuf.xlColID + ExcelBuf.xlRowID).Value),’<’,’ ');
IF ExcelBuf.“Cell Value as Text” <> ‘’ THEN BEGIN
ExcelBuf.INSERT;
END;
j := j + 1;
UNTIL j > maxj;
i := i + 1;
UNTIL i > maxi;
xlwrkbk.Close(FALSE);
xlapp.Quit;
CLEAR(xlapp);
Variables:
xlrange Automation ‘Microsoft Excel 10.0 Object Library’.Range
xlwrksht Automation ‘Microsoft Excel 10.0 Object Library’.Worksheet
xlwrkshts Automation ‘Microsoft Excel 10.0 Object Library’._Worksheet
xlapp Automation ‘Microsoft Excel 10.0 Object Library’.Application
xlwrkbk Automation ‘Microsoft Excel 10.0 Object Library’.Workbook
maxi Integer
maxj Integer
i Integer
j Integer
EndOfLoop Integer
Found Boolean
Thanks for your help and patience! The problem has been solved. I’ve imported an xls excel file using a report based on the “Excel Buffer” table. NAV by default added a 1000 separator to some of the figures I tried to import, that’s why I had problems importing the file. I’ll soon post you a detailed description on how to correct this kind of problem.
Hello Kdm,
I’ve just send this post
http://dynamicsuser.net/forums/p/25792/168412.aspx#168412
but I wonder why you don’t use the native functions from the Table 370. And what about this sentence?
“Have you tried using the Excel Buffer not as a tempory table and seeing if it is populated?”
How can I decide whether using the Excel Buffer Table as a tempory table? It’s related to any kind of transaction?
Thanks!!