Importing Address Information into Dynamics AX 2012

by | Updated May 26, 2015 | Dynamics AX, Set Up

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.

Importing Address Information into Dynamics AX 2012

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

Related Posts

  • I put together some certification information on the requirements for Microsoft Dynamics AX 2012 and a proposed training roadmap to get to each of the core certifications.  Rather than have this…

  • When importing model files from an ISV or another vendor you may trigger an error within Dynamics AX. We recently faced this error in Dynamics AX 2012 when importing modelstore …

  • The Safari browser is now supported natively on Dynamics AX 2012 R2 as well as being supported on Dynamics AX RTM with a hotfix.  Taken from the updated System Requirements…

3 Comments

  1. Roger

    For countries difrent tha US? I saw the available countries are too short. I am interested to import France, spain, germany. Europe in fact.

  2. Brandon Carmichael

    Hey Roger:
    The imports you are requesting are custom imports; more specifically, they are coded for a specific format. Each has a specific method in the AOT for doing the import. To figure out what fields are for each of them (other than for the US, which Eric provided in his blog), you’d need to look at the actual method. These have been in AX for quite a while, so the countries that show up in that import dropdown are mostly legacy code that’s been in existence for a long time.

    Those imports are custom imports, specifically coded for a specific format. Each has a specific method in the AOT for doing the import. To figure out what the fields are for each of them (other than for US, which Eric provided in his blog), you’d need to look at the actual method. These have been in AX for quite a while, so the countries that show up in that import dropdown are mostly legacy code that’s been in existence for a long time.
    Just a quick note, the country code is the same length for all countries. You can view a list of the ISO country codes here: (http://www.nationsonline.org/oneworld/country_code_list.htm).

    To my knowledge there isn’t a generic, one-size-fits all, country postal code import, partially because each country’s national postal service provides data in a different format (normally for a fee).

    Thanks,
    Brandon

  3. Francis Omoruto

    Thank you for the time saver!

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

august

12aug10:00 am10:30 amWhy Levridge Grain? How to Achieve Efficient and Accurate Scale Tickets

12aug12:00 pm1:00 pmThe Three Paths to Dynamics 365 Finance and Supply Chain from Dynamics AX

13aug11:00 am12:00 pmConfab with Stoneridge - Livestream - Inspire Keynote Breakdown

19aug10:00 am11:00 amWhat is Levridge? An Overview of the Ultimate Ag Solution

19aug12:00 pm12:30 pmThe Modern Manufacturer - Death by Safety Stock

27aug11:00 am12:00 pmConfab with Stoneridge - Livestream - Dynamics 365 2020 Wave 2 Preview

september

02sep10:00 am10:30 amThe Modern Manufacturer - Cycle Count Management

09sep10:00 am11:00 amWhat is Levridge? An Overview of the Ultimate Ag Solution

16sep10:00 am10:30 amThe Modern Manufacturer - Product Lifecycle Management

30sep10:00 am10:30 amThe Modern Manufacturer - Return Management

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X