17 Apr’17

D365 Business CentralImporting Excel file in Microsoft Dynamics NAV using Excel Buffer

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

  1. Microsoft Dynamics NAV 2017
  2. 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.

Written by

Chris D'Silva

Software Developer

One thought on “Importing Excel file in Microsoft Dynamics NAV using Excel Buffer”

  1. Hello Chris D’Silva,

    thank you for this helpful article.
    In Step 8 you used the UploadFile Function, I don’t understand the second Parameter in this Function, where does it come from and what is it’s usage?

    Thanks

    Timo Beermann

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Reading more then 10K records in D3FOE OData API

    11 July’ 2018

    Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

    Read more
  • Paging in D365 Customer Engagement v9.0

    10 July’ 2018

    Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

    Read more
  • Set up Dynamics 365 connection in Microsoft Social Engagement

    10 July’ 2018

    Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

    Read more
  • Voice of the Customer failed to install

    10 July’ 2018

    Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

    Read more
  • Scribe Insight AX as a Web Service Find Block issue

    10 July’ 2018

    Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

    Read more