Importing Excel file in Microsoft Dynamics NAV using Excel Buffer
Dynamics 365 business central development is an ERP or Enterprise Resource Planning solution from Microsoft. It is undoubtedly one of the best in the market. A lot of Small and mid-sized companies are using this software to increase their efficiency and productivity of their teams.
It is easy to install and deploy this software. It is also quite affordable. The best part about it is that your employees can learn how to use it in a few days. Now, this is one of the key reasons why a lot of companies are happy to get this software. There are a lot of resources that are available to help people who are stuck with a problem to get over it quickly.
Introduction:
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.
Pre-requisites
- Microsoft Dynamics NAV 2017
- Microsoft Excel.
Steps:
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.
