Importing Zip Codes to Business Central
The default zip codes that come with Microsoft D365 Business Central are not useful. When I create a new production company, I see a few sample zips as below. They all need to be deleted from the Zip Code Table (# 225).
Go to the Simple Maps site to download a free file of 33,099 US Zip Codes (simplemaps.com/.../us-zips)
After making some adjustments to the zip code file in Excel, you will be importing it (copy and paste) into BC Table # 225, which is the Zip Code Table. Search for that List Page and choose "Edit in Excel". BC will give you a blank spreadsheet ready for import (Publish).
First we need to prepare the Simple Maps' Excel file so BC can accept the data properly. There are 2 adjustments we need to make to the Source file. The first column (Zip) should be transformed to a Text column, in order to save leading zeros. A new column for Country should be inserted after "City" and before "State_ID".
Open a fresh Excel file (blank) and "Get Data" from the Simple Maps .csv file.
In the "Get Data" step you will transform the first column as Text, so as to show the leading zeros of the zip code.
Change the data type of the first column to Text.
Now "Close and Load" the Simple Maps .csv to the new Excel file.
All 33,099 US Zip codes are in your new Excel file. The Range was converted to a Table. You will add the Country column in the next step.
Country column added and filled in with "US", no quotes. These first four columns will be pasted into the blank BC spreadsheet you will get when you do Page->>Edit in Excel for the Zip Code table.
Copy rows (just 4 columns) from the Source file as above, and paste them into the destination file created when you went to Page->>Edit in Excel. Start with a small test of 20 lines. Publish those. Close the destination Excel file without saving. Inspect the Zip Code List Page in Business Central to see that the published lines arrived.
In the screen shot below, I recommended pasting all 33,099 rows. Don't do that. Only 5000 at a time will be accepted. I tried all rows, but the import died at row 5800 .
Here is an example of pasting rows 10,001 to 15,000; then Publishing to Business Central. Remember that BC needs the State in the "County" column in Table #225.
After importing the List Page for Zip Codes in Business Central will have all the codes properly inserted to Table #225.
It actually works. Typing in a zip into a blank Customer Address, will fetch the corresponding City, State, and Country.
You can Bing Map it!
