Working with leading zeros in Northeast zip codes
In this article:
- Your Excel file contains a column of zip codes
- Your data contains a large number of incorrect zip codes
If you have a national constituent base or are located in the Northeast you can't escape those leading zeros on zip codes, but Excel and other spreadsheet programs often remove them. We provide a couple pointers for keeping your zip codes intact and accurate before importing them into LGL.
Your Excel file contains a column of zip codes
You have an Excel file with a column of zip codes. On screen the zip codes contain the leading zero (for example, "08904"), but the values stored in the cell are lacking the leading zero (they look like this: "8904"). You can tell this by clicking in a cell and seeing what the actual text is in the value/contents area of Excel.
If you try to import this Excel file into LGL, your zip codes will accurately reflect the value Excel has in the cell: or "8904". This will create an incorrect zip code upon import.
- When you have your Excel file looking perfect (displaying the leading zero is okay), do a "Save As" in Excel and save the file as a *.csv formatted file. (One of the reasons we strongly encourage using the CSV format is that you are better able to see what will actually happen on import.)
- Check the CSV file by opening it with a plain text tool (don't double-click it) such as Notepad or something. Do this to confirm you are seeing the leading zeros for addresses that have them. Close the file without updating/changing it.
- Import the CSV file with the appropriate mapping. Before accepting the import, check a few records in the preview (click the -Full Record- option) to be sure you are seeing the leading zeros.
Note: If you have an Excel file where all the leading zeros are not present, you can work with the above directions once you have corrected the zip codes to display properly.
Your data contains a large number of incorrect zip codes (they should have leading zeros but don't)
Say a large number of addresses that lack the leading zero were imported into your account, resulting in many bad addresses.
- Option 1: Turn on SmartyStreets in your LGL account (read this article to learn how this works) and let the computer do the work for you.
- Option 2: If you are able to "Undo" the import that created those addresses, you could do that and then fix and re-import the zip codes per the instructions above.
- Option 3: As a last resort, you can export the relevant addresses and re-import them after correcting the zip codes as described above.
Exporting and Importing Addresses:
a. In the Constituents tab, perform an advanced search and search on the State/States field.
b. In the criteria fields, type the states that have leading zeros separated by commas. Do not add spaces after the commas. Your state/states search criteria should look something like this: "ME,NH,VT,MA,RI,CT,NJ"
Note: This presumes you have uniformity in your state abbreviations/text fields. If you don't, you'll have to use search terms that match your values. Also note that PR (Puerto Rico) and VI (Virgin Islands) have leading zeros in their zip codes.
a. When you have the results you expect, click the Export Results button in the upper right of your screen.
b. Use a custom export, and choose the following fields:
- From Constituent Fields, select LGL Constituent ID
- From Contact Info. Fields, select Preferred Address
- Customize your Preferred Address field (by clicking Customize next to the field's name), and then check the "Split" option under Street, being sure to include the LGL Address ID).
The screen shot below shows what this should look like in your account.
Move to the Save Export step, and make sure the options for "spreadsheet friendly" and "inactive info" are not selected. Then download your report and open it in Excel or the equivalent. Format the zip codes so they have the leading zero. Then follow the instructions above for formatting your Excel column to display leading zeros.
When you have your CSV file, import it back into LGL making sure that your LGL Constituent ID and LGL Address ID and other mappings are correct.