Excel Import Workaround

Link to macros referenced below

How to Import data into Axapta / Microsoft Dynamics AX without using the Excel routines.

This is a method of importing data into Axapta / Dynamics AX by taking data put in an Excel template and importing it using the Axapta "Standard" method, which is usually used for Axapta to Axapta importing, thus avoiding the Excel import method, which might be causing problems. This method doesn't use X++.

This method can involve a lot of rote manual work looking up column data types and changing Excel sheets to reflect those types.

Although this method and code were used to successfully import data to an Axapta installation where the Excel import method rejected the data the method and code given are given with no warranties, express or implied, and with no guarantee of its suitability for any particular purpose. It was developed to solve a particular problem and both are given here for educational purposes. You use them at your own risk.

Note that assumptions are made about the structure of Axapta import files and this structure may not be documented and may change or be different for other or later versions or installations.

Note also that the steps given here are partially from memory. You must be sufficiently familiar with your installation to solve any problems that you may find.

This procedure requires that you have Excel templates filled with the data you want to import and access to the new Axapta installation. All Axapta operations listed are to be done in the new installation.

Procedure

1. Open the Excel template file to be imported that has been filled with the correct data.
2. Unhide rows 2 through 5
3. Find the name of the Axapta table in Excel cell 2B
4. In Axapta, export the table with that name. It does not matter if it has data in it yet. Set up an export definition as type "Standard", only export that one table named in cell 2B, uncheck the notes box, use file type of "Comma". Exporting the table produces a .def file and a .dat file. These may be copied to a working folder.
5. Run the Excel macro called "excel_to_axapta" and tell it both the correct sheet and the name and location of the .def file. This will make a new Excel workbook and sheet that has the raw Axapta columns in it, and the names of the columns, which you will have to later delete. "excel_to_axapta" will halt on any error values in the cells in the Excel template.
6. Highlight all of the new Excel sheet and change the format of the cells to "General".
7. In the new sheet in the column called "recversion" put a '1' into every cell for every row that has data except the header cell. I use find-and-replace.

Now for each column in the new sheet that has a header name you will set the type of data the column contains.

1. In Axapta, go to the application object tree (AOT), go to the tables, go to the table name being imported here, right-click on it and select "find...". In the Search box select "All Nodes" and put "type" in the "containing text" box. Go to the "properties" tab and select "ExtendedDataType" and click "Find now". This is a list of all columns in the Axapta table and their data types.
2. You must set up the data columns for the second Excel macro, which takes the Excel format of the topmost data cell (not text header) for each column and uses that to determine how to write the data in that column. For each column in the new sheet find its type in the search you just executed and change the format of the first data cell, the cell just below the text header of each column, to "numeric" for any column that is a number data type in the Axapta search, change the format to "date" for any date data type, and leave it as "general" for text or any other type except "enum". Enum column require more work.Note that multiple-line text and text with special characters may not transfer correctly. Note also that can be columns in the Excel sheet that aren't in the Axapta search and columns in the Axapta search that aren't in the sheet. Ignore both.
3. Columms B through E don't have text headers. Change the topmost cell in each to numeric.

The macro will let you know if there are duplicate column names. Treat them the same as any other column.

The macro will color the text header cell red for any column that has no data and will color it red with a strikethrough for any column that is in the .def file, that is, in the Axapta table, but not in the Excel sheet. Both should be set to the proper type.

I've included a short macro, "cyclecelltypes", that cycles a cell range's format and color, numeric (green), date (cyan, blue), general (yellow).

For enum columns

Use the search results to determine which Excel columns have enum types. An enum type is a text representation of a number. The Excel templates have the text value in them, the Axapta Standard import routine requires the numeric value. Go through every Excel column that has data and holds an enum type and use Excel find-and-replace to manually change each enum to its numeric value. If it's a NoYes enum use find-and-replace to turn every no into a '0' and every yes into a '1' for that column. For an enum type you don't know, right click the field name in the Axapta search, goto add-ins/open new window/open used enum. The properties for each enum has its value. Use find-and-replace for each of these values. Now change the format of the first data cell of the column to "numeric".

Continuing...

1. In the new sheet highlight only the header cells with the text names of the columns and delete them, moving cells up. Don't delete the entire row and don't only make the header cells blank.
2. Run the Excel macro "writeaxaptadata". The output file is the .dat file that will be imported. Make sure the last Excel sheet viewed before running the macro is the one you've been adjusting.
3. Open both the .def file and the .dat file in a text editor. In the .def file change the "RECORDIDMIN" value to 1 and both the "RECORDIDMAX" and "RECORDCOUNT" values to the number in the third column of the last row in the .def file, the count of all rows being imported. Since Axapta normally uses this file to import from another Axapta installation, where they would already have their own arbitrary record IDs, the Record ID numbers here will not change the IDs the records will be given when they are imported.
4. Copy both the .dat and .def files to wherever you want to import them from.
5. Make an import definition in Axapta that imports only the desired table. Make sure "include system and shared tables" is not checked. Make sure that "Delete company accounts..." is not checked. This last if checked will delete the data in many or all tables for that Axapta company definition. This may be useful if you want to be sure you are starting clean, but be sure you know exactly what will happen. Check "update existing..." to save current data already in the table and add the imported data to it, clear to erase the current data in the table about to be imported. Using the update method may cause problems if the Excel templates didn't include the primary key for the table, which in my case was true for journal entries -- using the update method more than once for these tables resulted in new, duplicate rows being created instead of re-importing over the same rows. Use the table browser to be sure the data was imported. If not, you might try importing with the update check box cleared the reimporting with it checked.

Do this for every table you want to import.

If any of this needs to be done repeatedly, for many tables, one might be able to automate most all of it, since the results in the "find..." box can be copied in their entirety to the clipboard, pasted into a text file, and imported into Excel (Tab delimited).