A Zip Address Import Source file is the ASCII file from which you will be Importing Zip Addresses. The ASCII file can be comma or tab-delimited, or fixed position.

The Zip.txt file included in the ZIPsales Database is an example of this type of file.

Required fields are:

• ZIP Code (10 characters maximum)
• State (2 characters maximum)
• City (30 characters maximum)

Optional fields are:

• County (25 characters maximum)
• Country Code (6 characters maximum)
• Country (30 characters maximum) for builds prior to Build 72

Geocode (10 characters maximum)

Geocode is used to uniquely identify the combination of city, county and state. The primary key on the DZ Zip Address table is Geocode and ZIP Code. As a result, if geocode is blank, there can only be one city per ZIP Code. If that city name being imported is the USPS city, this should not be a problem. However this could be a problem if your source file contains actual city names as opposed to the USPS Preferred City Name.

If your import source file does not contain geocodes and you want more than one city name per ZIP Code, you can modify the file to add geocodes. Here is one way you can create your own geocodes:

1. Open your import source file with Excel being sure to flag the ZIP Code column as text (while opening) so that the leading zeros will not be dropped.
2. Add a column for the geocode.
3. Sort the file by state then by city. (So that within each state group, the cities are sorted.)
4. If you import any states from the Rate & Boundary Database delete all rows from this source file for addresses within those states.
5. In the new geocode column enter the state code followed by seven zeros and a 1 in the first row for each state. For example, in the first row for Alaska you would enter AK00000001.
6. Then have Excel create a series based on that first row all the way to the end of each state. This will give the second row in Alaska the geocode of AK00000002, etc.
7. Save the Excel file. (If you save it as an Excel spreadsheet, you will be able to open it again easily. However you must also save it as a Text file (tab or comma delimited) or as Formatted Text (space delimited) so that you can import the results.

When importing from a source other than the ZIPsales Database ZIP.txt file existing but obsolete ZIP Codes are NOT purged from the DZ Zip Address table If you want to clean out obsolete ZIP Codes, you will need to do that with a SQL Query. Here is an example SQL Query to use for that purpose:

USE DYNAMICS

DELETE
FROM DZ40400
WHERE KDUpdtNmbr = 999 – Replace 999 with the Update Number of the prior Zip Address import from a source other than the ZIPsales Database.

To help identify the update number you can use the following SQL Query:

USE DYNAMICS

SELECT *
FROM DZ40400
WHERE KDUspsCity = – Prior to Build 72 the USPS City field was not populated by the import from a source other than the ZIPsales Database. or WHERE KDCnty = – Assuming that you did not import county names

or

WHERE KDGeoCode = '' – Assuming that you did not import geocodes

Even when importing from a source other than the ZIPsales Database ZIP.txt file, the DynamicZip setup option Use Naming Rules for New Cities & Counties¹⁾ is effective. You should consider turning this option off while performing the Zip Address Import.

Glossary