Open topic with navigation

ZIP Table Update Utility

This utility is launched by navigating to X:\Inetpub\WWWRoot\Mill\Tools\ziptableupdate.exe using the Microsoft Windows Explorer, and double clicking that file. It executes in its own window - not from within the Abila Millennium Interface.

Caution! Do not run this utility if you subscribe to the CorrectZip service.

The ZIP Table Update Utility is designed to update the ZIP Code lookup table to bring each into conformity with changes made by the US Postal Service. The utility uses a text file that lists the changes that have been made to the City, State, County, and/or Time Zone that are associated with a particular Code. A text file of changes is made available from Abila, or you may generate your own file using the required file layout specifications.

When the utility is run, the text file is compared to the existing entries in the ZIP Code lookup table. When discrepancies are found, the corresponding row in the lookup table will be updated. If the utility finds that the text file includes a ZIP Code that is not contained in the lookup table, a new row containing that information will be added to the lookup table. Any such changes or additions are registered in the ZIP Code lookup table and will automatically be reflected in any US Address rows that are inserted or edited from that point onward. (This utility does not update existing Address data rows. That function is performed by the ZIP Code Address Update utility.)

Utility Classification

The utility includes the option of overwriting existing lookup table entries and existing Address data. Therefore it is classified as a system administrator tool.

Specifications for the ZIP Code Text File

Abila will periodically make a text file available for download, containing any changes to the ZIP Code table that the USPS has made since the creation of the previous text file. If you prefer, you may create your own text file, according to the specifications shown below.

Once the file has been downloaded or created, it must be placed in a directory that is accessible to the workstation where the utility will be run.

The utility requires a fixed length text file that conforms to the following layout:

Using the ZIP Code Utility

In addition to the specifications for the text file to be used, there are some pre-conditions involved in setting up the utility for use at your institution. The following describes and discusses these procedures. Following the descriptions, you will be given a set of step by step instructions for using the ZIP Table Update utility.

Log File

This utility will produce a log file that includes start and end times, as well as a list of new ZIP Codes and updated ZIP Codes. It is named ZipTableUpdate.txt.

Workstation and Login

You must consider the following requirements when deciding which of the workstations you will use to run this utility. The workstation must have:

• Microsoft Windows
• ODBC driver that can connect to the system database
• VB40032.DLL, THREED32.OCX, COMDLG32.OCX, PICCLP32.OCX

The ZIP Table Update Utility is run outside of the interface you use to access Abila Millennium, in it's own application window. It must be activated by locating the program file (.exe) using the Microsoft Windows Explorer or other appropriate tool, and then double clicking the program name.

You will first be prompted to login to the Abila Millennium Database with ODBC, specifying Web SQL (or another Data Source Name that you may have set up.) You must login as either the Database (sa or MILLORA User ID) Administrator or as a Millennium system administrator.

This utility does not use the SQL Views for the Abila Millennium data tables.

Application Window

Upon executing the utility, a pop-up message box will remind you to select the Open File icon as your first step. When you do so, you will be prompted to browse to the selected input text file that will be used to update the ZIP Code table. It will also prompt you to log in to the ODBC.

Following a successful login to ODBC, the application window will be displayed again. Click the Start Button , or click on the File menu Start item to begin the processing of the updates. A message window will be displayed to indicate the progress of the utility.

Using the ZIP Code Utility

The following instructions assume that you have downloaded or created an appropriate text file containing the ZIP Code changes to be made, and that it has been placed in a location accessible by the workstation and known to the user who will run the utility. For more information about any procedure, see the hyperlinked subject.

1. Use the Windows Explorer or other tool to locate the ZipTableUpdate.exe program file and double click on it to launch the utility. It should be found in wwwroot\mill\tools.
2. As prompted, use the Open file icon to navigate to the text file containing the zip code changes. You will also be prompted to select a machine data source. This will probably be Web SQL but could be another data source you may have set up.
3. You will be prompted to login using a valid Abila Millennium User ID and password. You must login as either the Database (sa or MILLORA User ID) Administrator or as a Millennium system administrator. After a successful login, an application window will be presented.
4. Click the Start Button . Or, on the File menu, click Start to begin processing. As it runs, the Records Read box will display the number of records from the text file that have been examined and processed.
5. When the run is finished, you will be given a message box indicating that the process is complete. Click OK to clear it.
6. A log will be written that contains begin and end times, as well as a list of ZIP Codes that were added or updated. The log is named ZipTableUpdate along with a timestamp and is located in the same folder as the ZipTableUpdate.exe.