Home > Blog > Importing Address Information into Dynamics AX 2012

Importing Address Information into Dynamics AX 2012

When we first set up Dynamics AX 2012 internally to run our business, we just added the bare bones address information to get by (Zip Codes, Cities, States and Counties), but since we were refreshing our environment, I figured it was a good time to see what options exist for importing all that information in one shot, rather than trying to enter it on the fly.

I'll first start by saying this isn't the "Cadillac" solution; the best way to manage address information in AX 2012 is to use AX Services to manage changes in Zip Codes, Cities, etc. from an external source (like the US Postal Service) so you can update your system in the future any time there are changes.  If you are a logistics company or someone for whom having up-to-date address information is critical, that would be the way to go.  For the rest of us, who can afford not to know that zip code 91210 now covers Santa Monica (I don't know if that's true, just throwing something out there), when we find out that a city or zip code was added, we can add those manually to AX.

There's a function in AX called "Import ZIP/Postal Codes", but it's actually a bit of a misnomer - it not only imports Zip Codes but also Cities, States and Counties.  You can find this window in Organization Administration > Setup > Addresses.

I looked for documentation on how it works all over the Internet, and while there was a post explaining how it works, the only thing I found that actually showed you how to do it was a reply to a comment on a forum.  Now, this function only works for the following countries - United States, Netherlands, Sweden, Belgium, Japan and China.  This post will explain how to do it for US Zip Codes.  Without further ado, this is how you use it.

1. Zip Code Source - the first thing you need is a source for data.  This is harder than it sounds because there aren't places where you can just find all this information for free.  There are different places you can get this info, such as the Postal Service or ZipInfo.com, but I used the ZIP code database by ZIPCodeSoft. It's $29 for a Standard license for a year's subscription, and that's the best deal I could find.
2. Converting Zip Code data into AX's prefered format - AX has a very particular format it wants to use for importing this data.  The format is as follows:
   1. The first character is the capital lettter "D"
   2. Characters 2-6 are the 5 digit zip code
   3. Next you need 56 characters of white space (hint: use this function in Excel to generate this: =REPT(" ",56)
   4. Starting with character 63, enter the city name
   5. Generate blank values up to character 100, so use this formula =REPT(" ",37-LEN(City))
   6. Enter the 2 digit state value
   7. Generate 3 more blank characters
   8. Enter the county value up to 25 characters (I used the REPT function to make sure each line had the same number of characters).
3. Create a text file for the import - copy the values out of the resulting column (Column Q if you're using my spreadsheet) and put them in a text file with a return after each line.  Save that file to a place where AX can find it.
4. Import the file using the AX window - go under Organization administration > Setup > Addresses and click on Import ZIP/Postal Codes.  Choose United States and find the file and click OK.  You'll get an InfoLog showing how many records where read and how many failed.

I hope this helps to make the process easier to get AX setup - with these tools and $29, you can make sure your users aren't fat-fingering cities, counties or putting the wrong zip code on account information.

Eric Newell

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.