Table Join to Shapefile issue [picture]

325
4
04-05-2012 08:33 PM
SamanthaM
New Contributor
I downloaded median house hold value by census tract from American Fact Finder 2. I converted it from data retrieved from csv to a standard excel workbook. Even though the shapefile and the data table share a common trait in which to do a join, it will not let me. In the shapefile the common attribute is stored as a string, and in the excel workbook I believe it is stored as number if that makes a difference. If it does please see image 3 attached where I try making a new column with field calculator.

Image 1: Shows a picture of ARCgis attribute window and the excel table side by side to see that the two share a common field, the census tract number which is termed "name" in the attribute table for the shape file, and "geography" in the excel data table (marked in red)
[ATTACH=CONFIG]13288[/ATTACH]

Image 2: When I select the shapefile to perform the join, nothing comes up in the combo box.
[ATTACH=CONFIG]13289[/ATTACH]

Image 3: If the reason for not being able to join is because the name field in the shape file is being stored as a string, while in the excel file its a number. To try and fix this, assuming this is the problem, I made a new field and tried using field calculator to copy over the string as text
[ATTACH=CONFIG]13290[/ATTACH]

Thanks
0 Kudos
4 Replies
AndrewChapkowski
Esri Regular Contributor
If you are going to convert the values from a string to a number, you probably want to use double since you have decimals.

To convert from a string to a number using the python option on the field calculator: float(!MYFIELDNAME!)

Did you try changing the field format in excel from a number to a string then perform a join?
0 Kudos
by Anonymous User
Not applicable
Original User: SamanthaM

If you are going to convert the values from a string to a number, you probably want to use double since you have decimals.
To convert from a string to a number using the python option on the field calculator: float(!MYFIELDNAME!)
Did you try changing the field format in excel from a number to a string then perform a join?



Yes I can quickly format the data in excel from number to string, and the join will work; however, I want to figure out how to make it work with field calculator to learn new things. Also you are correct on needing a double, that was user oversight error.

Did I do something incorrectly? It gave me an error message.

[ATTACH=CONFIG]13301[/ATTACH]

Also would it be possible to see the same solution to this problem, converting a text field to numbers with field calculator, with VB script? For VB script I thought it would simply be [name] in the code field with number as the type and making sure you have a double field.
0 Kudos
AndrewChapkowski
Esri Regular Contributor
Try float(fieldName) not Float(fieldname).  It's case sensative.
0 Kudos
by Anonymous User
Not applicable
Original User: SamanthaM

Try float(fieldName) not Float(fieldname).  It's case sensative.



Thanks! I was able to get it to work with python float
[ATTACH=CONFIG]13302[/ATTACH]

Also was able to get it to work with VB script
[ATTACH=CONFIG]13303[/ATTACH]

Thanks a great deal. Is there a reputation system on this forum, because there doesn't appear to be. I would love to ad to yours if there is.
0 Kudos