DIXF – Importing data using Excel and ODBC

Consider a scenario where you are running AX 2012 R3 and you want to import data from Excel files using ODBC. Having recently worked a case on this topic, I thought I’d share some of the details on how this can be set up.

1. Background

The main reason we were looking into this way of importing Excel data was that trying to import the data in Batch using Excel files would in some scenarios result in this error:

The version of Microsoft Excel is not supported.-Exception from HRESULT: 0xC020801C

S0

Working with a LCS Repro VM we were able to reproduce the issue, and then establish an alternative approach that I will outline below.

There are lots of ways to import data into Microsoft Dynamics AX and this is only one of them. The best tool for the job and approach will depend on many factors, including the degree of control you have over source data formats (CSV File, Excel File, XML File, AX, ODBC), the frequency of the imports, the complexity of the entities being used, security considerations, etc.

The aim of this blog post is simply to outline one approach which you may be considering.

2. Prepare the environment

In this example, we are working with a very simple 2 column Terms of delivery entity Excel input file.

// Disclaimer:
// Microsoft provides programming examples for illustration only, without warranty
// either expressed or implied, including, but not limited to,
// the implied warranties of merchantability or fitness for a particular purpose.
// This mail message assumes that you are familiar with the programming language that
// is being demonstrated and the tools that are used to create and debug procedures.
//
// This source code is freeware and is provided on an "as is" basis without warranties of any kind,
// whether express or implied, including without limitation warranties that the code is 
// free of defect, fit for a particular purpose or non-infringing. The entire risk as to the 
// quality and performance of the code is with the end user.

A. Download the 64-bit version of Microsoft Access Database Engine 2010 Redistributable:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

B. Copy the installer to a local folder and run the install from command line using the /passive parameter:

C:\MSFT\AccessDatabaseEngine_x64.exe /passive

S1

C. Backup the registry and rename the key mso.dll in the following location:

Warning: Incorrectly editing the registry may severely damage your system. At the very least, you should back up any valued data on the computer before making changes to the registry.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths

S2

D. In Windows, create a 64-bit ODBC data source pointing to the Excel file to be imported:

S3a

E. In AX, create an ODBC source data format using the 64-bit ODBC data source created before:

S3b

F. In AX, create a Processing group using the source data format just created. The query should look like: select * from [worksheet$], for example:

Select * from [S1$]

S5

G. In AX, generate the mapping and test:

S4

3. Run the DIXF Excel file / ODBC source data format import as a Batch

We can now proceed and run the DIXF import using Batch processing:

S6

The Batch job is executing:

S7

The Batch job has ended successfully:

S8

We can verify that the job ran as expected by checking the Execution history, too:

S9

4. Testing

As always, ensure you’ve set up a proper Dynamics AX TEST environment first, and that you are familiar with the impact of your DIXF actions before you perform them in PROD.