Importing Zip Codes to Google sheets, Excel, Numbers and QGIS

ZIP codes produce problems for many people who are setting up a map in QGIS or Excel. I\’m going to focus on the missing \”0\” when importing a CSV file into QGIS or Excel.  The frustration people feel about this is totally warranted. 

CSV stands for Comma Separated Variable. It\’s a text file where each column is separatedby a comma.  So the CSV I\’m using in these examples is very simple.  Two data points. It\’s saved with the suffix .csv and can be opened by any spreadsheet or GIS.

Illustration of the problem

The file opens like this (with the missing zero)

QGIS Solution

Instead of going to the trouble of creating a CSVT file, just uncheck the checkbox labeled \”Detect Field Types\”. After years of using CSVT files, I appreciated this solution from MrXsquared on gis.stackexchange.com.  See the video above for a demo.

For when you need to use a CSVT file in QGIS, this video shows how.

Google Sheets Solution

I used this solution.  Go to format, number, more formats, and create a custom format that has five zeros.

Excel Solution 

In Excel,  the ZIP code is in the number format as Special.

Numbers Solution

Select custom field type and set three preferences for the number format: Remove separator, add a digit (to make five), and show zeros for unused digits.

I think it\’s kind of funny that none of the spreadsheet programs makes it easy to use ZIP Codes.  It\’s almost as if they the developers copy each other rather than serving the needs of their audiences in a thoughtful way.