The post is to import the data in a table From an Excel File.
Most of us know this feature is there in standard objects of Microsoft Dynamics NAV from a long time.
If you are one of those who don't know about it please go through the article.
Let`s say i have a table with following fields as shown below -
And the Excel file that i want to import as shown below -
The Target is to import the data from excel in the Navision Table.
I have Created a Page to view imported entries and on the same page there is an action of Import From Excel.
For Importing data from Excel we will be using Standard ReadSheet Function in Excel Buffer Table in a Report. The Report will be Processing Only and will have options to Add the Entries or Replace the Entries.
Below is the code that have been used to achieve desired result -
First the Report Checks the option that user selected in Request Page of Replacing Entries or Adding Entries.
Then using Standard Function of Excel Buffer the Excel File Selected by User is Opened.
Then Using ReadSheet Function Excel Buffer Reads the Data in Selected Excel File.
Then System using a function try to get No. of Rows and Column in Selected Excel Sheet.
As First Row in Our Case Contain Caption, so it start inserting data in table from Row No 2 To Last Row.
What system does in Function GetLastRowandColumns -
What system Does in Function Insert Data -
System read the data in a row and assign it to actual table fields.
To Open Excel File and Sheet Selection Code on Request Page is -
Now let's try to execute the process from the Page -
Select Import From Excel and Select Option to Add / Replace Entries. Once Clicked Ok System will ask for the Excel File to Import, as shown below -
If you are one of those who don't know about it please go through the article.
Let`s say i have a table with following fields as shown below -
And the Excel file that i want to import as shown below -
The Target is to import the data from excel in the Navision Table.
I have Created a Page to view imported entries and on the same page there is an action of Import From Excel.
For Importing data from Excel we will be using Standard ReadSheet Function in Excel Buffer Table in a Report. The Report will be Processing Only and will have options to Add the Entries or Replace the Entries.
Below is the code that have been used to achieve desired result -
First the Report Checks the option that user selected in Request Page of Replacing Entries or Adding Entries.
Then using Standard Function of Excel Buffer the Excel File Selected by User is Opened.
Then Using ReadSheet Function Excel Buffer Reads the Data in Selected Excel File.
Then System using a function try to get No. of Rows and Column in Selected Excel Sheet.
As First Row in Our Case Contain Caption, so it start inserting data in table from Row No 2 To Last Row.
System read the data in a row and assign it to actual table fields.
Now let's try to execute the process from the Page -
Select Import From Excel and Select Option to Add / Replace Entries. Once Clicked Ok System will ask for the Excel File to Import, as shown below -
Once File is selected and Excel File have multiple sheets, System will prompt to select the sheet also.
If everything goes well a confirmation message will appear and the data will be updated as shown below.
Hope you find above post helpful.
For Objects used in this post please download same from my Google Drive.
Click Here to Download - Prateek.ExcelImport#NAV
Click Here to Download - Prateek.ExcelImport#NAV
No comments:
Post a Comment