ArcMap’s data import nightmare

The problem

The table on the left (downloaded census data from social explorer in csv format) does not have the leading zeroes when importing into ArcMap, while the census tract boundary file on the right does show the leading zeroes. These datasets will NOT match.

Anybody who has worked extensively with census data and ArcMap knows about this perennial problem:  text vs numeric data type nightmare.  Here is the problem.  Census data FIPS identifiers are numeric in nature.  For example, the State of California’s FIPS code is “06”.  This is because every state is a 2 digit number, and California is the 6th state in the country.  This means that every California identifier, according to the census, begins with the number “0” (zero).  For example, the FIPS code for Los Angeles County is:

06037

A census tract in Los Angeles would have a FIPS code that looks like this:

06037265301 (UCLA's census tract)

The problem lies in the leading number that happens to be a zero.  The census, and sites like Social Explorer, provide the data correctly, with leading “zeroes” where appropriate.  However, applications that handle this data, such as Excel and ArcMap, interpret this data as numeric in nature, and automatically chops off the leading zero.  This means that when you import FIPS code into ArcMap (for version 10.1 at the time of this writing), the program converts the data without the leading zero.  Therefore, Los Angeles County becomes:

6037

and UCLA’s census tract becomes:

6037265301

While this may not seem as much of a big deal, it actually is.  Most GIS users keen on mapping demographic profiles will do so by downloading census boundary files (like the TIGER files from the census bureau), and then download data such as race, income or age from the census.  Then you will attempt to join the data so that it can be mapped.  However, you cannot map non-matching data types.

The solution

The solution is that we need to designate the FIPS code from the census data file to be a “text” data type, rather than letting ArcMap automatically convert it to a number and chopping off the all important leading zero.  Since ArcMap does not allow you to specify data types on import, we are going to have to do this elsewhere.

The Excel solution

Assuming you have the data in CSV format, follow these steps:

  1. open Excel (do not open the CSV file by double clicking on it)
  2. go to Data, From Text, and choose the csv file you wish to import
  3. in Step 1 of the Text Import Wizard, choose Delimited
  4. in Step 2 choose the correct delimiter (in most cases it will be a comma)
  5. in Step 3, highlight the column that has the FIPS code (for social explorer data, it will be the first column with the header “GeoFIPS”)
  6. Choose “text” as the column data format
  7. click finish
  8. Now you are ready to save the file as an .xls format.  Go to file, save.
  9. for “save as type”, choose “Excel 97-2003 Workbook”

Import your newly saved .xls file into ArcMap.  Open the attribute table, and double check that the FIPS column has been imported correctly, and that it has the leading zeroes.