Jump to content

 
Photo

Joining ZIP codes, tract numbers, etc. in ArcGIS

- - - - -

  • Please log in to reply
10 replies to this topic

#1
Dennis McClendon

Dennis McClendon

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,084 posts
  • Gender:Male
  • Location:Chicago
  • Interests:map design, large-scale maps of cities
  • United States

Has anyone figured out a way to tell ArcGIS that the field type in a table is something other that what it guesses?

 

I want to JOIN the client's list of ZIP codes to a ZIP code shapefile and eventually make them different colors.  But when Arc imports the Excel or CSV file, it looks at the first eight records, sees that the ZIP codes are all numeric digits, and declares the field type to be Double rather than String.  Thus, the JOIN command will not show the field I want to join on.  I cannot find any way to instruct Arc that I want the field type of a column in a newly imported table to be String.


Dennis McClendon, Chicago CartoGraphics
chicagocarto.com

#2
jlmurack

jlmurack

    Newbie

  • New Member
  • Pip
  • 4 posts
  • Gender:Female
  • United States

If the field is set as a string in Excel, then it should import into ArcGIS as a string. I've found that saving the spreadsheet as .xlsx, rather than .csv or .xls gives me less problems.


Edited by jlmurack, 03 June 2013 - 12:50 PM.


#3
katzmoye

katzmoye

    Contributor

  • Validated Member
  • PipPip
  • 14 posts
  • Gender:Female
  • Location:Portland, OR
  • United States

You can also add a fake temp first line in the excel spreadsheet that is xxxxxx in that field, forcing ArcGIS to recognize it as a text field.



#4
mfarmer

mfarmer

    Contributor

  • Validated Member
  • PipPip
  • 38 posts
  • United States

I've frequently found it useful to create a new string field in the target layer (ZIP code, tract, etc.), and copy the ZIP Code (or etc.) 'as string' to the new field. That provides flexibility for other data sets that come your way.



#5
frax

frax

    Hall of Fame

  • Associate Admin
  • PipPipPipPipPipPipPip
  • 2,320 posts
  • Gender:Male
  • Location:Stockholm, Sweden
  • Interests:music, hiking, friends, nature, photography, traveling. and maps!
  • Sweden

In Excel, to instruct it that a string of numbers should be text, precede it with a '

 

In a CSV file, text fields should be enclosed in double quotes.


Hugo Ahlenius
Nordpil - custom maps and GIS
http://nordpil.com/
Twitter

#6
Dennis McClendon

Dennis McClendon

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,084 posts
  • Gender:Male
  • Location:Chicago
  • Interests:map design, large-scale maps of cities
  • United States

Well, I finally got it to work by manually editing the CSV to add quotes before and after all the ZIPs.  Seems much harder than it should be for such a common task.

 

I formatted the field as Text in Excel.  Is there something I'm missing about setting the field to String?


Dennis McClendon, Chicago CartoGraphics
chicagocarto.com

#7
David Medeiros

David Medeiros

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,089 posts
  • Gender:Male
  • Location:Redwood City CA
  • Interests:Cartography, wood working, wooden boats, fishing, camping, overland travel, exploring.
  • United States

Well, I finally got it to work by manually editing the CSV to add quotes before and after all the ZIPs.  Seems much harder than it should be for such a common task.

 

I formatted the field as Text in Excel.  Is there something I'm missing about setting the field to String?

 

The only thing you should have to do is set the column format to Text in Excel before saving. That should work everytime for Arc, but if it doesn't or you forget, you can simply add a new field to the table in Arc, set that field as String and use the field calculator to make it equal to the numeric join field . Fin.


GIS Reference and Instruction Specialist, Stanford Geospatial Center.

 

www.mapbliss.com

 


#8
wick

wick

    Contributor

  • Validated Member
  • PipPip
  • 27 posts
  • Gender:Male
  • United States

You can edit the schema.ini file to specify data types of each field before you add it to ArcMap.
http://msdn.microsof...3(v=vs.85).aspx

The contents of your schema.ini file might look like this:

[FileName.csv]

ColNameHeader=True
Format=CSVDelimited
Col3="FieldNameYouWantToBeText" Text


If using Excel, you need to either import the csv as text so you can assign data types to each field. If you have already imported the data and the numbers are stored as numbers instead of text, you can use the Text to Columns menu item to convert a field to text.


Jesse Wickizer
Maps.com

#9
Dennis McClendon

Dennis McClendon

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,084 posts
  • Gender:Male
  • Location:Chicago
  • Interests:map design, large-scale maps of cities
  • United States

I guess I don't understand why ArcGIS has no tool to change the field type.


Dennis McClendon, Chicago CartoGraphics
chicagocarto.com

#10
David Medeiros

David Medeiros

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,089 posts
  • Gender:Male
  • Location:Redwood City CA
  • Interests:Cartography, wood working, wooden boats, fishing, camping, overland travel, exploring.
  • United States

I guess I don't understand why ArcGIS has no tool to change the field type.

 

Ditto. Although I'm guessing it has someething to do with preventing Arc from melting down when you change a field type that was being used for a dynamic selection or join etc.


GIS Reference and Instruction Specialist, Stanford Geospatial Center.

 

www.mapbliss.com

 


#11
AndrewM

AndrewM

    Contributor

  • Validated Member
  • PipPip
  • 27 posts
  • United Kingdom

You can't change the field type, but I think you should just be able to add a new field and set it to string, then use the field calculator to make new field = old field, giving you a new string field with the same values as the original.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

-->