When attempting to upload a CSV file containing zip codes with a leading zero the leading zero is dropped when attempting to save the file for import. How do I keep a leading zero in a CSV import file?
Add a comment
|
2 Answers
This isn't really a CiviCRM issue, but it is helpful for getting data into CiviCRM or any system like it.
A postal code is a string of text despite looking (in most of the US) like an integer. A spreadsheet program doesn't know this, however, so it will save 012345 as 12345 by default.
If you're using a spreadsheet program to edit a file for import, just precede the zip code with a single quote ('). It will disappear, but the zip code will be interpreted as a string of text.
If you have a whole column of zip codes that are appearing as integers, you should insert a new column and use a function to rewrite the ones that are wrong. In LibreOffice, if you have the original zipcodes in column A, you'd enter the following formula in row 2 of your new column:
=IF(A2>9999,A2,"0"&A2)
Basically, it tacks a 0 on the front of the value if it's 9999 or lower.
Finally, if you're assembling a CSV from scratch, just be sure to quote the zip codes that have leading zeroes.
When setting up the CSV file, make sure that the zip code (and zip code suffix) columns are defined as TEXT format columns, and that the keyed-in / imported data has the appropriate leading zeros. Then the export / upload processes should work as expected.
