AX 2012 Data Export/Import

Meant to do a blog on data import in AX 2012 for a long time but didn't have time. In previous AX versions, data can be import/export using Excel template in AX. Data migration in AX 2012 is very different because of the data structure changes in tables. For some reason it's been a mysterious puzzle in AX 2012 implementation for a lack of documentation, and it's become a non-intuitive process. Based on sources available from AX online community and Microsoft support, the followings are the options for data import in AX 2012:

1. Write code to use AIF web services. This is actually recommended by Microsoft support. Problem is you'll need a developer.
2. Use AX export/import definition group. This option exists in previous versions too and it creates a .dat file and .def file. Problem is the exported data is hard to edit and the .def file is difficult to understand. It's a good tool make a duplicate company but not for loading master records.
3. Microsoft just released an online service RapidStart last October, which is meant to facilitate initial company config and data import. Though I had a bad dream with this service for the first time played it, I still have my faith in this tool. Microsoft engineer has come back to us and asked for more testing, but I just didn't get time to take on this thread for now. Will report back if I get time to test it more. Feel free to drop me a line if you have experience with the service.
4. Use Excel AX add-on. This is by far the most efficient tool we've found for data import. Similar to the old Excel template and easy to use.

Here is how to load items into AX 2012 using Excel add-on. To make our example more interesting, I'll load products and items (i.e. released product) together. Assume product and item setups have been properly configured in AX. 

Open Excel and click Dynamics AX tab. Click Connections, select Legal Entity and click Ok

Select Add Data - Add Table, and add the following tables and click Ok

• EcoResProduct
• EcoResTrackingDimensionGroupProduct
• EcoResStorageDimensionGroupProduct
• EcoResProductTranslation 
• EcoResProductDimensionGroupProduct
• InventTable 
• InventModelGroupItem
• InventItemGroupItem
• InventTableModule (optional)
• InventItemSetupSupplyType  (optional)

InventItemSetupSupplyType is optional. But, if it's not included, item won't show on the item dropdown on purchase order lines.

Fill in data on each worksheet. If need to look up field value, can click Field Lookup button on the ribbon. A few tricks on working with the tables:

• Close the Field Chooser side column before your edit data
• Only include table fields you'll put data in.
• Make sure your cursor is outside the work area, or otherwise Excel would complain your data is incomplete. 
• DO NOT tab at the end of the table, or otherwise it'll generate a new line at the end and cause data incomplete error.

After filled in all data needed, click on Publish Data - Publish Options. You must tell AX the sequence how to load the tables, for example, the above listed tables are in a correct sequence. Or otherwise, when you try to load data, you'll get an error saying data doesn't exist in other tables. 

Finally, click Publish Data - Publish All to load data. If everything goes well, you should get a separate worksheet and it shows the number of records that have been loaded successfully or how many were failed. 

Here is the template file I generated. Very simple and you can expand it to include more fields to fit your needs.

Excel AX add-on seems like a great tool for data migration. I'm still learning all the tricks myself too. Just find another blog on the same topic today Dynamics AX 2012 Products Import using Excel Add-in. It has great details. If you have any experience or comment on data migration in AX 2012, feel free to let me know.