D365 Business CentralImporting Excel file in Microsoft Dynamics NAV using Excel Buffer
This article is to import data from the Excel file into existing NAV table. This import matches the excel entries with the existing NAV table and modifies the records of the table on import of the excel file. To accomplish this, we create a report using Excel Buffer as the DataItem.
- Microsoft Dynamics NAV 2017
- Microsoft Excel.
1. Create a table with fields and populate it with data.
2. Create a excel file which you want to import.
3. A page is created with action button to Import data from excel.
4. A Report is created using Excel Buffer(table id 370) as a Data Item. The report property Processing Only is set to yes. Standard ‘ReadSheet’ function of the table Excel buffer is used to read the data from the Excel file.
5. A function is used to calculate the total number of rows and columns using Excel Buffer table.
6. On the PreReport function of the report we write the following code.
7. In the Insert Data function it initializes the table created in step 1, sets the range from the matching fields and if found it inserts values in the table.
8. To open the Excel file and selection of sheet we write a code in the Request Page in the Report. We use File Management codeunit to upload file.
9. Now run the page and click on the action Import then select the excel file.
10. The import is completed and we can view the modified changes on the page after the excel file import.