Import postal- or ZIP codes with a leading zero

2024-01-16

POSTAL CODE DATA, ZIP CODE BOUNDARY DATA, SOLVE IMPORT ERRORS, TROUBLESHOOTING, ZIP CODES WITH LEADING ZEROS

If you want to import and map postal codes, you'll quickly notice that when linking them, postal codes are displayed as text (strings) and not as numbers data type. There are a few reasons for this. The first is that not all countries use only numbers in their postal notations. Countries like Canada and the United Kingdom, for instance, use letters in their postal notations.

Import ZIP or Postal Codes with Leading Zeros

Another, and very important, reason to represent postal codes as text is the fact that there are postal areas that start with a 0. This may seem harmless, but not when you want to import these postal codes. When importing postal codes that consist only of numbers, most systems automatically convert them into a numeric (integer) field. This results in postal codes starting with a 0 being automatically stripped of this 0 during import. The consequence is that these postal codes can no longer be linked to postal areas.

Solution before importing

If you want to prevent a postal code that starts with a 0 from being imported as text (string), there is a simple workaround.

1. Open the file you want to import.

2. Add a letter to the first postal code in the column where you indicate the postal code.

3. Save the file and import it again.

4. After the import is complete, go to the relevant dataset, and you will see that the postal code is now imported as text (string). Don't forget to remove the letter from the field.

5. Proceed with mapping your data at the postal code level.

Solution after importing

If you have already imported data at the ZIP code level and the ZIP code field has been imported as a number with the leading zero removed, there is also a way to retroactively add the zero to the beginning of the postcode field. Follow these steps:

1. Go to the MY DATA section.

2. Select the dataset with data at the ZIP code level.

3. Change the data type from number to string. For more information, read our article on changing data types.

4. Then, click on the SQL option on the left and enter the following query. Modify {{DATASET NAME}}, {{POSTAL CODE FIELD}}, and {{NUMBER OF CHARACTERS}} accordingly.

UPDATE {{DATASET NAME}} SET {{POSTAL CODE FIELD}} = LPAD({{POSTAL CODE FIELD}}, {{NUMBER OF CHARACTERS}}, '0')

For example, the query could look like this:

UPDATE us_locations SET zipcode = LPAD(zipcode, 5, '0')

5. Then click APPLY QUERY, and you will have added a zero to the beginning of each truncated ZIP code.