25 Oct’17

D365 Business CentralWays to Import Export Data from Microsoft Dynamics NAV to Excel

Introduction:

In a scenario where a company uses Excel to keep the data and wants to move its ERP to Dynamics NAV.  A lot of data needs to be added in NAV in order to get a full system working. Thus, copying each entry could be a time-consuming task. Microsoft Dynamics NAV is enabled with the feature to Import and Export Data to and from Excel.

Procedure:

Ways to Import Export Data from Microsoft Dynamics NAV to Excel

There are two ways to Import Export Data to and from NAV

  1. Using XMLPORT(Server Side)
  2. Using Configuration Packages(Client Side)

XMLPORT Method Steps:

1. Create a new XMLPORT in NAV Server Object Designer Window

2. Adding Entries in the XML Port

  • NodeName: Root > Node Type: Element > Source Type: Text > Data Source: <Root>(By Default)
  • NodeName: Table Name(Anyname is fine) > NodeType: Element > SourceType: Table > Data Source: Table(Select the Table click on the Rollup Button)
  • NodeName:Field Name > NodeType: Element > SourceType: Field > Data Source: Field(Select from the Rollup)

3. Indent the Entries as

Root(Indent: 0) > Table(Indent: 1) > Fields(Indent :2)

4. Changing the properties of the XMLPORT

  • Delimiter: <‘’>
  • Separator: <TAB>

5. Change the Properties of the Table

  • Format: Variable Text
  • AutoUpdate: Yes
  • AutoReplace: No

6. Save the XMLPORT and RUN

  • For the first time Choose the Direction as ‘Export’ > Save > Filename.xls

7. Open the file, make change the entries and close the file

8. Run the XMLPORT and choose direction as ‘Import’ and select the file

Verify whether the import is successful by checking the entries in the table.

IMPORT EXPORT using Configuration Packages

1. >Open the Microsoft Dynamics NAV (Client Application) and search Configuration Packages.

2. Create a New Configuration Packages > Enter the following values in required fields as Code: 123(Appropriate Number) and Package Name: Excel Import(Appropriate Name) > Select the Table ID:50001(Using Drop Down) and Table Details will be updated.

3. Under the ‘Tables’ ribbon click on Excel > Export to Excel > Save as appropriate file

4. Open the Excel File and change the entries

5. From the ‘Tables’ ribbon, select Import from Excel and Select the file

6. By this time the data is not written to the table just imported. Thus have to apply the data.

After applying, it gives the data inserted or modified or errors if any.

Conclusion:

The fields are mapped first, thus all the fields to be Imported and Exported are to be known first.

Only one excel file per table can be used. The file extension needs to be .xls and not .xlsx as it gives an encoding error while opening the file. Importing or Exporting computed fields cannot be done by this method.

Written by

Olister Rumao

Software Engineer

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