com.billpringle.utils.wrputils
Class WrpBuildZipCodes

java.lang.Object
  extended by java.awt.Component
      extended by java.awt.Container
          extended by java.awt.Window
              extended by java.awt.Frame
                  extended by javax.swing.JFrame
                      extended by com.billpringle.utils.wrputils.WrpBuildZipCodes
All Implemented Interfaces:
java.awt.event.ActionListener, java.awt.event.ComponentListener, java.awt.image.ImageObserver, java.awt.MenuContainer, java.io.Serializable, java.util.EventListener, javax.accessibility.Accessible, javax.swing.RootPaneContainer, javax.swing.WindowConstants

public class WrpBuildZipCodes
extends javax.swing.JFrame
implements java.awt.event.ActionListener, java.awt.event.ComponentListener

This class rebuilds the zip code database from the US Post Office CityState system. This database is accessed by the WrpValidateCsv class, which validates city / state / zipcode combinations.

The zip code information can be obtained from the U.S. Post Office. The AIS City/State National information CD is received periodically (monthly or quarterly). The information is received as a large text file with fixed length records and no end-of-line sequence. The file contains valid zip codes, along with the valid cities for that zip code. The format for this file is described below.

The zip code validation information is stored in a MySQL database table zipcode with the following fields:

This class reads the USPS zipcode data file and creates a CSV file that can be used to replace the current database content. The user should perform the following steps:

Copy the contents of the USPS CD into a directory
This will create (or replace) the sub-directories: crlf, ctystate, and layout.
The directory you care about is ctystate; the other directories are not needed. The crlf directory contains a MS-DOS program that can insert CR-LF characters at the end of each line. The layout directory contains documentation on the file format, as well as information on the crlf.exe program.
Unzip the file ctystate.zip
Within ctystate directory should be a file ctystate.zip, which should be unzipped back into the ctystate directory, which will create the file ctystate.txt. This is a large text file with fixed-length records and no CR-LF characters, so don't try to read it with a text editor.
Execute the program extract_citystate
This is a Windows executable program that executes this class. It was built using the program JSmooth, which creates windows executable wrappers for Java programs.
The program should open up a window with the message "Log Created"
Select the Input File
Select "File / Open USPS Input File" and navigate to the input file ctystate.txt
Select the Output File
Select "File / Open Output/Input CSV File" and navigate to zipcode.csv (if present) in the original directory. (you will need to go up one directory from the input file.)
If this is the first time you have used this program, or if you created a new directory for this conversion, then the file zipcode.csv won't be there.
Select Convert Zipcode Information
This option will cause the input file that you selected (probably ctystate/ctystate.txt) to be converted into the output CSV file that you selected (probably zipcode.csv.)
Note:
The name of the CSV file must be the same as the table name in the MySQL database in order for the import to work correctly.
Run the import.bat program
The import.bat calls the mysqlimport program to import the output CSV file into the database, replacing the current content of the table.
This program assumes the output file was zipcode.csv so that it can be inserted into the MySQL database table zipcode. If any other names are used, the command file must be updated. A sample version of import.bat is as follows:
mysqlimport -h127.0.0.1 -czipcode,state,city -uemail -punisys -L --delete --fields-terminated-by=, --lines-terminated-by="\r\n" email zipcode.csv

After the above steps have been completed, the zipcode database table will have the latest USPS information.

The USPS AIS City/State National file is a text file with fixed-length records rather than the typical text lines terminated with CR-LF type sequences. Because of this, normal input text routines such as readLine() can't be used. This program uses the inner worker class ZipFileReader to extract the zip code data in the background. The input file contains several different record types, but the only type that is used is the "D" type record, which contains the detailed zip code information.

The format of this file is:

Start and End locations are for substring function
StartEndDescription
01 Record type. Type "D" is the type of record that contains the detailed zipcode/city/state information.
16The 5-digit zip code
1341A city name for this zip code. There can be multiple records for the same zip code, one for each city associated with the zip code.
4154City abbreviation. This field isn't used often, and when it is, the same abbreviation often appears in another detail record under city name.
99101The two character USPS state abbreviation

Although there is a field for abbreviation, normally separate records appear for the zip code, with the city name in one record, and the abbreviation in another record.

Creative Commons License Creative Commons License Symbols Unless noted otherwise, all materials available for download from my site are copyrighted by Bill Pringle, and are licensed under a Creative Commons License.

Author:
Bill Pringle
See Also:
Serialized Form

Nested Class Summary
private  class WrpBuildZipCodes.ConvertWorker
          This class converts the USPS input text file to a CSV text file in the background.
(package private)  class WrpBuildZipCodes.ZipFileReader
          Class to read the input USPS City/State National zip code text file.
 
Nested classes/interfaces inherited from class javax.swing.JFrame
javax.swing.JFrame.AccessibleJFrame
 
Nested classes/interfaces inherited from class java.awt.Frame
java.awt.Frame.AccessibleAWTFrame
 
Nested classes/interfaces inherited from class java.awt.Window
java.awt.Window.AccessibleAWTWindow
 
Nested classes/interfaces inherited from class java.awt.Container
java.awt.Container.AccessibleAWTContainer
 
Nested classes/interfaces inherited from class java.awt.Component
java.awt.Component.AccessibleAWTComponent, java.awt.Component.BaselineResizeBehavior, java.awt.Component.BltBufferStrategy, java.awt.Component.FlipBufferStrategy
 
Field Summary
private  javax.swing.JDesktopPane desktop
          desktop for main window
protected  javax.swing.JFileChooser fc
          file chooser
protected  java.io.File fileCsv
          CSV file converted from input file
protected  java.io.File fileIn
          input file from USPS with zipcode information
private  javax.swing.JList logList
          list of log messages
private  javax.swing.DefaultListModel logModel
          list model for log
private  javax.swing.JPanel logPane
          panel for scrolling log messages
private  javax.swing.JScrollPane logScroll
          scroll pane for log messages
protected  java.io.PrintStream prt
          print stream for output file
protected  java.io.BufferedReader rdr
          reader for input file
(package private) static long serialVersionUID
          sequencing identifier
protected static int USPS_CITYABBR_END
          ending location for optional city abbreviation
protected static int USPS_CITYABBR_START
          starting location for optional city abbreviation
protected static int USPS_CITYNAME_END
          ending location of the city name
protected static int USPS_CITYNAME_START
          starting name of the city name
protected static java.lang.String USPS_DETAIL
          starting string for zipcode detail record
protected static int USPS_RECORD_SIZE
          the record size for the input USPS text file
protected static int USPS_STATE_END
          ending location for state abbreviation
protected static int USPS_STATE_START
          starting location for state abbreviation
protected static int USPS_ZIP_END
          ending location of zipcode
protected static int USPS_ZIP_START
          starting location of zipcode
 
Fields inherited from class javax.swing.JFrame
accessibleContext, EXIT_ON_CLOSE, rootPane, rootPaneCheckingEnabled
 
Fields inherited from class java.awt.Frame
CROSSHAIR_CURSOR, DEFAULT_CURSOR, E_RESIZE_CURSOR, HAND_CURSOR, ICONIFIED, MAXIMIZED_BOTH, MAXIMIZED_HORIZ, MAXIMIZED_VERT, MOVE_CURSOR, N_RESIZE_CURSOR, NE_RESIZE_CURSOR, NORMAL, NW_RESIZE_CURSOR, S_RESIZE_CURSOR, SE_RESIZE_CURSOR, SW_RESIZE_CURSOR, TEXT_CURSOR, W_RESIZE_CURSOR, WAIT_CURSOR
 
Fields inherited from class java.awt.Component
BOTTOM_ALIGNMENT, CENTER_ALIGNMENT, LEFT_ALIGNMENT, RIGHT_ALIGNMENT, TOP_ALIGNMENT
 
Fields inherited from interface javax.swing.WindowConstants
DISPOSE_ON_CLOSE, DO_NOTHING_ON_CLOSE, HIDE_ON_CLOSE
 
Fields inherited from interface java.awt.image.ImageObserver
ABORT, ALLBITS, ERROR, FRAMEBITS, HEIGHT, PROPERTIES, SOMEBITS, WIDTH
 
Constructor Summary
WrpBuildZipCodes()
          Default (and only) Constructor
 
Method Summary
 void actionPerformed(java.awt.event.ActionEvent e)
          React to action command.
 void addLog(java.lang.String msg)
          Add a message to the log.
 void buildLog()
          Build a scrolled pane for log messages.
 void buildMenu()
          Build the menu for the window.
 void buildPage()
          Build the page contents for the window.
 void componentHidden(java.awt.event.ComponentEvent e)
          called when window is hidden
 void componentMoved(java.awt.event.ComponentEvent e)
          called when window is moved
 void componentResized(java.awt.event.ComponentEvent e)
          called when the window is resized.
 void componentShown(java.awt.event.ComponentEvent e)
          called when the window becomes visible
 void convertZipCodes()
          Convert input file to output file in the background.
 void exitWindow()
          Terminate the program.
 java.io.File getFile(java.lang.String desc)
          Allow user to select the specified file.
 void init()
          Initialize object before building menu and page.
static void main(java.lang.String[] args)
          Main driver for this class.
 void resizeScroll()
          Page resized - adjust things.
 
Methods inherited from class javax.swing.JFrame
addImpl, createRootPane, frameInit, getAccessibleContext, getContentPane, getDefaultCloseOperation, getGlassPane, getGraphics, getJMenuBar, getLayeredPane, getRootPane, getTransferHandler, isDefaultLookAndFeelDecorated, isRootPaneCheckingEnabled, paramString, processWindowEvent, remove, repaint, setContentPane, setDefaultCloseOperation, setDefaultLookAndFeelDecorated, setGlassPane, setIconImage, setJMenuBar, setLayeredPane, setLayout, setRootPane, setRootPaneCheckingEnabled, setTransferHandler, update
 
Methods inherited from class java.awt.Frame
addNotify, getCursorType, getExtendedState, getFrames, getIconImage, getMaximizedBounds, getMenuBar, getState, getTitle, isResizable, isUndecorated, remove, removeNotify, setCursor, setExtendedState, setMaximizedBounds, setMenuBar, setResizable, setState, setTitle, setUndecorated
 
Methods inherited from class java.awt.Window
addPropertyChangeListener, addPropertyChangeListener, addWindowFocusListener, addWindowListener, addWindowStateListener, applyResourceBundle, applyResourceBundle, createBufferStrategy, createBufferStrategy, dispose, getBufferStrategy, getFocusableWindowState, getFocusCycleRootAncestor, getFocusOwner, getFocusTraversalKeys, getGraphicsConfiguration, getIconImages, getInputContext, getListeners, getLocale, getModalExclusionType, getMostRecentFocusOwner, getOwnedWindows, getOwner, getOwnerlessWindows, getToolkit, getWarningString, getWindowFocusListeners, getWindowListeners, getWindows, getWindowStateListeners, hide, isActive, isAlwaysOnTop, isAlwaysOnTopSupported, isFocusableWindow, isFocusCycleRoot, isFocused, isLocationByPlatform, isShowing, pack, postEvent, processEvent, processWindowFocusEvent, processWindowStateEvent, removeWindowFocusListener, removeWindowListener, removeWindowStateListener, reshape, setAlwaysOnTop, setBounds, setBounds, setCursor, setFocusableWindowState, setFocusCycleRoot, setIconImages, setLocationByPlatform, setLocationRelativeTo, setMinimumSize, setModalExclusionType, setSize, setSize, setVisible, show, toBack, toFront
 
Methods inherited from class java.awt.Container
add, add, add, add, add, addContainerListener, applyComponentOrientation, areFocusTraversalKeysSet, countComponents, deliverEvent, doLayout, findComponentAt, findComponentAt, getAlignmentX, getAlignmentY, getComponent, getComponentAt, getComponentAt, getComponentCount, getComponents, getComponentZOrder, getContainerListeners, getFocusTraversalPolicy, getInsets, getLayout, getMaximumSize, getMinimumSize, getMousePosition, getPreferredSize, insets, invalidate, isAncestorOf, isFocusCycleRoot, isFocusTraversalPolicyProvider, isFocusTraversalPolicySet, layout, list, list, locate, minimumSize, paint, paintComponents, preferredSize, print, printComponents, processContainerEvent, remove, removeAll, removeContainerListener, setComponentZOrder, setFocusTraversalKeys, setFocusTraversalPolicy, setFocusTraversalPolicyProvider, setFont, transferFocusBackward, transferFocusDownCycle, validate, validateTree
 
Methods inherited from class java.awt.Component
action, add, addComponentListener, addFocusListener, addHierarchyBoundsListener, addHierarchyListener, addInputMethodListener, addKeyListener, addMouseListener, addMouseMotionListener, addMouseWheelListener, bounds, checkImage, checkImage, coalesceEvents, contains, contains, createImage, createImage, createVolatileImage, createVolatileImage, disable, disableEvents, dispatchEvent, enable, enable, enableEvents, enableInputMethods, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, firePropertyChange, getBackground, getBaseline, getBaselineResizeBehavior, getBounds, getBounds, getColorModel, getComponentListeners, getComponentOrientation, getCursor, getDropTarget, getFocusListeners, getFocusTraversalKeysEnabled, getFont, getFontMetrics, getForeground, getHeight, getHierarchyBoundsListeners, getHierarchyListeners, getIgnoreRepaint, getInputMethodListeners, getInputMethodRequests, getKeyListeners, getLocation, getLocation, getLocationOnScreen, getMouseListeners, getMouseMotionListeners, getMousePosition, getMouseWheelListeners, getName, getParent, getPeer, getPropertyChangeListeners, getPropertyChangeListeners, getSize, getSize, getTreeLock, getWidth, getX, getY, gotFocus, handleEvent, hasFocus, imageUpdate, inside, isBackgroundSet, isCursorSet, isDisplayable, isDoubleBuffered, isEnabled, isFocusable, isFocusOwner, isFocusTraversable, isFontSet, isForegroundSet, isLightweight, isMaximumSizeSet, isMinimumSizeSet, isOpaque, isPreferredSizeSet, isValid, isVisible, keyDown, keyUp, list, list, list, location, lostFocus, mouseDown, mouseDrag, mouseEnter, mouseExit, mouseMove, mouseUp, move, nextFocus, paintAll, prepareImage, prepareImage, printAll, processComponentEvent, processFocusEvent, processHierarchyBoundsEvent, processHierarchyEvent, processInputMethodEvent, processKeyEvent, processMouseEvent, processMouseMotionEvent, processMouseWheelEvent, removeComponentListener, removeFocusListener, removeHierarchyBoundsListener, removeHierarchyListener, removeInputMethodListener, removeKeyListener, removeMouseListener, removeMouseMotionListener, removeMouseWheelListener, removePropertyChangeListener, removePropertyChangeListener, repaint, repaint, repaint, requestFocus, requestFocus, requestFocusInWindow, requestFocusInWindow, resize, resize, setBackground, setComponentOrientation, setDropTarget, setEnabled, setFocusable, setFocusTraversalKeysEnabled, setForeground, setIgnoreRepaint, setLocale, setLocation, setLocation, setMaximumSize, setName, setPreferredSize, show, size, toString, transferFocus, transferFocusUpCycle
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait
 
Methods inherited from interface java.awt.MenuContainer
getFont, postEvent
 

Field Detail

serialVersionUID

static final long serialVersionUID
sequencing identifier

See Also:
Constant Field Values

USPS_DETAIL

protected static final java.lang.String USPS_DETAIL
starting string for zipcode detail record

See Also:
Constant Field Values

USPS_ZIP_START

protected static final int USPS_ZIP_START
starting location of zipcode

See Also:
Constant Field Values

USPS_ZIP_END

protected static final int USPS_ZIP_END
ending location of zipcode

See Also:
Constant Field Values

USPS_CITYNAME_START

protected static final int USPS_CITYNAME_START
starting name of the city name

See Also:
Constant Field Values

USPS_CITYNAME_END

protected static final int USPS_CITYNAME_END
ending location of the city name

See Also:
Constant Field Values

USPS_CITYABBR_START

protected static final int USPS_CITYABBR_START
starting location for optional city abbreviation

See Also:
Constant Field Values

USPS_CITYABBR_END

protected static final int USPS_CITYABBR_END
ending location for optional city abbreviation

See Also:
Constant Field Values

USPS_STATE_START

protected static final int USPS_STATE_START
starting location for state abbreviation

See Also:
Constant Field Values

USPS_STATE_END

protected static final int USPS_STATE_END
ending location for state abbreviation

See Also:
Constant Field Values

USPS_RECORD_SIZE

protected static final int USPS_RECORD_SIZE
the record size for the input USPS text file

See Also:
Constant Field Values

fc

protected javax.swing.JFileChooser fc
file chooser


fileIn

protected java.io.File fileIn
input file from USPS with zipcode information


fileCsv

protected java.io.File fileCsv
CSV file converted from input file


rdr

protected java.io.BufferedReader rdr
reader for input file


prt

protected java.io.PrintStream prt
print stream for output file


desktop

private javax.swing.JDesktopPane desktop
desktop for main window


logPane

private javax.swing.JPanel logPane
panel for scrolling log messages


logList

private javax.swing.JList logList
list of log messages


logModel

private javax.swing.DefaultListModel logModel
list model for log


logScroll

private javax.swing.JScrollPane logScroll
scroll pane for log messages

Constructor Detail

WrpBuildZipCodes

public WrpBuildZipCodes()
Default (and only) Constructor

Method Detail

init

public void init()
Initialize object before building menu and page.


buildMenu

public void buildMenu()
Build the menu for the window.


buildPage

public void buildPage()
Build the page contents for the window. The page for this program consists basically of a scrolled list containing log messages.


buildLog

public void buildLog()
Build a scrolled pane for log messages. This method builds a scrolled list for log messages.


addLog

public void addLog(java.lang.String msg)
Add a message to the log. This method will add the specified message string to the scrolled log messages. It will also attempt to reposition the log scroll so that the most recently added messages are visible.

Parameters:
msg - message to be added to the scroll

resizeScroll

public void resizeScroll()
Page resized - adjust things. This method will resize the panel containing the scrolled log messages.


actionPerformed

public void actionPerformed(java.awt.event.ActionEvent e)
React to action command. This method responds to an event triggered by a user action.

Specified by:
actionPerformed in interface java.awt.event.ActionListener

getFile

public java.io.File getFile(java.lang.String desc)
Allow user to select the specified file. This method brings up a file chooser and allows the user to select a file. The string passed as a parameter can be used to identify to the user what file they are selecting.

Parameters:
desc - a term to specify what file is to be chosen.
Returns:
the selected file.

convertZipCodes

public void convertZipCodes()
Convert input file to output file in the background. This method creates a ConvertWorker object to perform the actual conversion in the background.


exitWindow

public void exitWindow()
Terminate the program. This method exits, closing the window.


componentHidden

public void componentHidden(java.awt.event.ComponentEvent e)
called when window is hidden

Specified by:
componentHidden in interface java.awt.event.ComponentListener

componentMoved

public void componentMoved(java.awt.event.ComponentEvent e)
called when window is moved

Specified by:
componentMoved in interface java.awt.event.ComponentListener

componentResized

public void componentResized(java.awt.event.ComponentEvent e)
called when the window is resized. This method resizes the log window to fit in the new window.

Specified by:
componentResized in interface java.awt.event.ComponentListener

componentShown

public void componentShown(java.awt.event.ComponentEvent e)
called when the window becomes visible

Specified by:
componentShown in interface java.awt.event.ComponentListener

main

public static void main(java.lang.String[] args)
Main driver for this class. This program will instantiate the class and run it.

Parameters:
args - not used