4

I'm trying to merge census tract data with a census tract shapefile in CartoDB.

Despite saving the tract ID codes as text/string in both .csv and .xlsx (including reimporting the .csv as text into Excel and resaving) formats, CartoDB keeps stripping out the leading zeros and treating the IDs as numbers when I import the files.

When I convert it back to a string in CartoDB doesn't put the leading zeros back in.

This is the only column the two datasets have in common, unlike some other county data I've worked with where I can merge on the Name of the polygon.

Any other ideas?

  • Are you importing into a previously defined table in CartoDB? If yes, make sure that that column is actually a string. If not, you will likely need to add a dummy record as the first line of your .csv (under your header, if you have one) that has XXXXXX for your strings and 9999 for numbers, or similar. – MaryBeth Mar 2 '16 at 20:42
1

CartoDB, unless specified, automatically guesses Excel and CSV files with respect to types.

In order to avoid this behaviour, make sure that you disable the "Let CartoDB automatically guess data types and content on import." checkbox for the import options:

checkbox

  • I'd like to add several points: Once you disable the auto detect, you need to change your lon/lat column types to number manually and make geom column recognize the lon/lat column. If you are sync with file in google drive/dropbox, all these customization after import is only available when sync is disabled. If you want to keep sync, a workaround is to use curl to setup sync, which have parameters to control the auto detect behavior, for example no auto detect for quoted columns, this solved the problem for me. – dracodoc Nov 3 '16 at 18:45
  • I found even the extra option provided by upload API didn't always get the columns right. For example numeric column contains NA will become string. I need to replace NA with empty space, and concatenate "_" with my numbers to keep leading 0. – dracodoc Nov 9 '16 at 14:42
0

A SE SuperUser Question gives some helpful alternatives here: Excel CSV import treating quoted strings of numbers as numeric values, not strings

Especially if you are using Excel and not just a text csv, this may be the easiest (second answer in link above, complete with pictures):

To keep the leading zero(s), you can set the format of that specific column to Text in the Text Import Wizard.

Start the Text Import Wizard by going to the Data tab & clicking From Text.

Select the CSV file you want to import.

Pick Delimited & hit Next.

Uncheck Tab (default). Choose Comma. Make sure Text Qualifier is set to ". Hit Next.

This is where you can specify the formats. Click the header of the column whose leading zero(s) you want to preserve. Select Text.

  • This question have nothing to do with Excel. – dracodoc Oct 30 '16 at 15:30
  • I would argue that it is a workaround for a csv file, which the original poster is having issues with. Because the OP never answered the question that I had for him in the comments, I replied with something that could work if that did not. – MaryBeth Oct 31 '16 at 17:59
  • this is not a workaround. OP want to import csv into Carto, an online map platform. How will importing csv into Excel help with that goal? – dracodoc Nov 3 '16 at 18:39
  • Sometimes reformatting the source data outside of a mapping platform is the quick and easy fix. I've done it many times. – MaryBeth Nov 8 '16 at 21:32
  • The ideal solution is that Carto providing a preview stage and let user assign column types. That's easy to implement, simple to understand, consistent to all kinds of inputs. – dracodoc Nov 9 '16 at 14:43

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.