4

I have a large set of data that has several places on the East coast that have leading Zeros in the Zip. How do I map these when leading zeros are automatically removed?

  • Are you using the zip code to geocode inside CartoDB? Or do you just need to display zip code? – user3697700 Jan 13 '15 at 22:27
  • had the same problem (on Mac OS 10.8.5, latest safari) bringing data from excel (office 2011 for mac) in tab delimited format. used text wrangler to check to make sure leading zeros were present post excel (they were). changing column title in text file to "Zipcode" did the trick for me. fallback might be to switch to CSV format (dangerous) and put quotes around the field. apparently zip + 4 won't geocode, with or without the hyphen before the +4. – user48091 Feb 25 '15 at 18:07
2

Files from CSVs and Excel files which contains numerical columns are directly casted to numbers in CartoDB, this means, as you say, that leading zeros are removed.

At the moment of my response here, there was no way to disable the type guessing from the User Interface.

If you are using the CartoDB UI, make sure that you disable the "Let CartoDB automatically guess data types and content on import." checkbox for the import options. Related: Text codes keep coming in as numbers, stripping out leading zeros

Without using the UI, by using the Import API, you can:

Import your file with the import API disabling the type guessing for the CSV/Excel file. You can find a detailed guide here: https://gist.github.com/Xatpy/7ffdbf6ad1f17bf57d20, whose content is:

Using MacOSX.
1) Open your browser and log in your CartoDB account.

2) Get your API key:

2a) Click on your "user-->Your API keys" option. (Up-right section) 2b) Copy your api key (you can click on the icon).

3) Open your terminal (bash).

4) We are going to use cURL and this command:

curl -F file=@/path/file.csv "https://{{account}}.cartodb.com/api/v1/imports/?api_key={{api_key}}&type_guessing=false"

4a) Get the path file you want to upload and replace it in the last command. Example:

file=@/Users/jaimechapinal/workspace/datasets/test_import_2.csv 4b) Replace your name account in {{account}} section. Example: https://xatpy.cartodb.com/

 4c) Replace your API key (you got it in the second step). 

 4d) Final example:

curl -F file=@/Users/jaimechapinal/workspace/datasets/test_import_2.csv "https://xatpy.cartodb.com/api/v1/imports/?api_key=ab444afbbbb11165b237c3a4c37fadbb7fddc111&type_guessing=false"

5) Press Enter.
6) If it was OK, you'll see the following message in your console (with another id):

{"item_queue_id":"74872d0b-13e5-4b5b-812b-ae1770260d78","success":true}% 7) Now you can check in your dashboard that you have your data without guessing (so all fields are strings).

Also, you can do it by means of SQL queries. For example, if your zipcodes are 5 characters long, but with the 0-removal some of them were reduced to 4 characters, you can: * Set your column to type text * Apply a query like:

UPDATE tablename set description = '0'||zipcode where char_length(zipcode) = 4

(You should change accordingly the values "tablename", "zipcode" (name of your zipcode column) and the number 4, in case it doesn't match with your specific case.

PD: Remember to create a copy of your table or export it before playing with SQL, as those changes cannot be undone!

2

This is old, but a problem I still have.

My solution, which I think is easiest:

  1. Before uploading, add a new row to the top of the doc in Excel.
  2. Type some text ("this is a string, dummy") in the new cell for the zip code column(s) with the leading zeroes.
  3. Save and upload to CartoDB.
  4. CartoDB sees the text on the first row and guesses it's string type, leaving the zeroes intact.
  5. Delete the extra row in CartoDB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.