Joining American Community Survey files to Shapefiles

12-12-2015 09:24 AM
New Contributor II

I used to have no trouble joining ACS files to shapefiles such as county tracts or block groups.  Recently, I find that I cannot.  ACS files come with "" fields that are numeric and do not join to shapefiles whose fields are all text.  I have tried numerous ways in Excel to turn the ACS numerical location fields to text, but when I load those files into ArcMap, ArcMap turns them all back into numerical fields.

HELP.  Vary frustrated that I can't figure out how join anymore.  And frankly, the Census Bureau pdf describes the process, but the process no longer works.  What's changed?   Me?  They?  Any and all recommenations will be welcome.

0 Kudos
2 Replies
MVP Frequent Contributor

There are a variety of reasons that can cause this process to go awry, so the first step is to elicit some details:

  •   What Version of ArcMap are you using?
  •   What format are the ACS files in?  Excel, .dbf, another format?
  •   Are the ACS files unmodified (same as when you got them from whatever source you have) or have they been modified?  If they have been modified, what has been done to them?
  • If the ACS files are in a format that can be viewed in ArcCatalog, if you look at the properities for the field, what does it display?  In ArcCatalog, does it indicate this field is String (text), Single, Double, etc?
  • Is the fieldname really GEO.ID, with the period between the two?  I ask as the period between the GEO and the ID could be the issue.  I'm assuming in this specific case the the fieldname reads GEO.ID before any Join was made.

Chris Donohue, GISP

0 Kudos
New Contributor II

Thanks for the thoughts, Chris. You've listed many of the key issues, and for the most part, I've taken those into account.

The key bugaboo, however, has been my inability (now resolved) to transform the Geo.Id2 data listed in the ACS files into a text-formatted number. For some reason, ACS seems to treat Geo.Id2 as a number to be reported in scientific notation. FIPs numbers are not, in my mind, real numbers. They cannot, should not, be manipulated arithmetically. I attempted over and over again to copy/paste those Geo.Id2 numbers into an Excel text-formatted column. But those attempts never resulted in a field that ArcMap would allow to join to my block-group files.


I did everything that ACS said I should in one of their publications, but even their example process did not work.

I finally stumbled upon a special process for pasting the Geo.Id2 numbers in a way that they stayed as numbers-in-text. And now the joins take. I'm not a klutz with Excel, but this one did confound me. I still don't know why the Census B. puts that Geo.Id2 in scientific notation, and I hope to find out (by complaining a bit).

Thanks again for the reply. If you know of any published ways to convert Geo.Id2 numbers into numbers-in-text, do tell.

Thanks again.


0 Kudos