Skip to main content

NAV 2013 R2 - Import Excel File Using Excel Buffer.

Hi All,


This article is based on a friend request. The Requirement was 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.


Just for the Demo suppose i have a table where with following fields as shown below -


And the file that i want to import looks like something 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 -

1. 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 like to article and it being used by you in any business scenarios.

For Objects used in Demo please download same from my SkyDrive.

File Name - Saurav.NAV#ExcelImport.rar

Stay Tuned for More.

Regards,
Saurav Dhyani

Comments

  1. Sorry but this is not helpful for someone who is not a good nav developer.

    ReplyDelete
    Replies
    1. I totally agree a new developer still require to learns basics of NAV first and look for processes which require customization and then it will make sense.

      Sorry i was unable to write something that it did not help you.

      Delete
  2. Great blog. I created a report and its working fine. I am facing the uploading time issue. I have around 800 lines and some validations before inserting the record in database. Report is taking too much time. For 800 it took around an hour or more which I feel is not a feasible solution. Any help in that?

    ReplyDelete
    Replies
    1. @Ramiz Arshad,
      I totally agree for these many lines. I would suggest to use Xmlport to import data.

      Delete

Post a Comment

Popular posts from this blog

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.