palleagermark: Easy reusable method to load postal codes (zipcodes) or how to save a spreadsheet as XML

[Blog bot]

Источник: http://www.agermark.com/2013/10/easy...ad-postal.html
==============

Something that needs to be done for all AX implementations, and that no one really wants to spend time on, is to load default postal codes.

For a limited set of countries AX comes with job able to import postal codes from a file. These countries are Belgium, Netherlands, Sweden and USA. I have never seen any information about where you obtain the correctly formatted file though.

I have been experimenting with a solution where the postal codes are just loaded with no user interaction at all. This is supposed to happen during the Partition initialization, in the "Create reference data" process:

During "Create reference data", AX is already population address data, like a total list of countries.

There are four major steps to get this far:

1. Obtain a valid file with the postal code.
2. If the file is not in XML format, you should convert it for easier import.
3. Develop the import code.
4. Hook the code up to the Partition initialization process.

So, I have obtained a comma separated spreadsheet file from the danish postal service. It looks something like this:

Postnr = Zip code
Bynavn = City
Gade = Street


First I remove unwanted rows and columns from the sheet:

Now convert the columns to a Table; select the columns and choose Insert / Table from the ribbon:

And the result:

The file can be converted to XML from Excel, but first you must enable the Developer tab:

If it is not enabled, you can enable it from File \ Options \ Customize Ribbon:

You'll now need to write a proper schema for the XML. For my purpose it looks like this:

In Excel go to the Developer tab and click on XML \ Source:

Click XML Maps \ Add to load the schema you just defined:

Choose the proper root from the schema and click OK twice:

Now the XML Source pane should look like this:

Select the relevant rows, right-clik on ZipCodeTable and choose "Map element":

Click OK:

Now you can save the spreadsheet as XML from File \ Save As and choose XML as the file type. The result will look something like this:

This file is loaded into Dynamics AX as a Resource. Go to the Resources node in the AOT, right-click, choose "Create from File" and select your XML file.


Next create a class to import from the resource node. There are two central methods you should have.


One method retrieves the XML from the resource node:

One method to loop through the XML:

And you'll of course need a method to actually process the postal code, which above is the processZipCode method, inserting data into the right tables. You'll have to figure out what to put in that by yourself.


If you'll want to use this in Denmark, you'll have to modify the spreadsheet and code a bit to account for the cases where a postal code belongs to a certain side of a street or just for a limited set of house numbers on a street.


To have the zipcodes generated automatically when new Partitions are created, you must write a new class that implementing the SysSetup interface, which calls your new class.


A final learning I got from this, is that in Denmark, or actually Greenland, the postal code for Santa Claus is 2412 (of course).

Источник: http://www.agermark.com/2013/10/easy...ad-postal.html