KampData Knowledge Base

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Zip Address Import Source ======

A Zip Address Import Source file is the ASCII file from which you will be [[dynamiczip:features:import:Import Zip Addresses|Importing Zip Addresses]]. The ASCII file can be comma or tab-delimited, or fixed position.

The Zip.txt file included in the [[dynamiczip:glossary:zipsales_database|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 [[dynamiczip:install:mods:release:build72:build72|Build 72]] 

**[[dynamiczip:glossary:Geocode]] (10 characters maximum)** 

Geocode is used to uniquely identify the combination of city, county and state. The primary key on the [[dynamiczip:tables: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 [[dynamiczip:glossary:usps_city_name|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:

  - 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.
  - Add a column for the geocode.
  - Sort the file by state then by city. (So that within each state group, the cities are sorted.)
  - If you import any states from the [[dynamiczip:glossary:rate_and_boundary_database|Rate & Boundary Database]] delete all rows from this source file for addresses within those states.
  - 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.
  - 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.
  - 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 [[dynamiczip:features:import:Import Zip Addresses|import]] the results.

When importing from a source other than the [[dynamiczip:glossary:zipsales_database|ZIPsales Database]] ZIP.txt file existing but obsolete ZIP Codes are NOT purged from the [[dynamiczip:tables: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 [[dynamiczip:glossary: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 [[dynamiczip:install:mods:release:build72:build72|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 [[dynamiczip:glossary:zipsales_database|ZIPsales Database]] ZIP.txt file, the DynamicZip setup option [[dynamiczip:windows:dz_naming_rules_window|Use Naming Rules for New Cities & Counties]]((This feature was discontinued in Build 74)) is effective. You should consider turning this option off while performing the [[dynamiczip:features:import:Import Zip Addresses|Zip Address Import]].

----

[[dynamiczip:glossary:Glossary]]