8 replies to this topic

[benbakelaar]

I am working with the New York City census tract's shapefile, available from nyc.gov. They store the CTLabel (Census Tract Label) as a 7 character string.

I have other census variables that I want to join to the shapefile so I can display census demographic data, but ArcGIS/ArcMap 9.1 only imports in DBF, CSV (comma delim), or TXT (tab delim), and since the census tract is a number, not text, it automatically imports it as such. Therefore, I cannot "join" to the CTLabel field in the census tract shapefile.

I am at a loss, I've tried encapsulating the number in quotations, but ArcMap just overrides that and imports it as an integer anyway.

[paul]

Try importing the .dbf into ArcMap, adding a new field of Text type, and then filling this new text field with your existing integer field. The integers will be converted to "text" through the calculation, and you should be able to link your table.

You could also try linking directly to an .xls document. I'm not sure if it will retain the text formatting or not. See link below on how to do this:

http://www.esri.com/news/arcuser/0104/file...n%20ArcGIS'

BTW ArcGIS 9.2 is supposed to support direct import of .xls files...no more cumbersome .dbf's or ODBC linking!

[mike]

do you have Microsoft access? What you can do is import your table into Access and convert your data fields into whatever format you prefer, text, double, long, etc. Then you can export it as a DBF file and then import that into 9.1.

[benbakelaar]

First, thanks Paul, that was a good idea, and I can't wait for ESRI to support XLS (10 years late in my opinion!).

do you have Microsoft access? What you can do is import your table into Access and convert your data fields into whatever format you prefer, text, double, long, etc.  Then you can export it as a DBF file and then import that into 9.1.

<{POST_SNAPBACK}>

I do Mike, and I will try that, but I have tried the same thing in Excel, maybe it's not specific enough though.

[benbakelaar]

Well, DBF didn't work, but using Microsoft Access and exporting to type "Text files" and naming it a CSV, it did maintain the quotations around the text field, which ArcGIS interpreted correctly. However, when I join, still the same problem... all the joined values are <Null>'s. Including the field I joined on.

CTLabel = String (7)
NAME = String (255)

CTLabel might = 10.1, 63, 1, 4, etc.
NAME exactly the same.

[paul]

Do the quote marks appear in the csv table? If so, you could remove them in the join table or add them to the shapefile to make the two match.

Getting joins to work can be frustrating...

If you want to upload or email me a subset of your shapefile and table, I wouldn't mind playing around with the data and see if I can get it to join.

[benbakelaar]

That would be most appreciated Paul, if you find the time.

Census Tracts: http://www.nyc.gov/h...ricts.shtml#cbt

Attached is a tab delimited TXT file, no quotations. The board wouldn't let me upload a CSV.

Attached Files

•  racnewyo.txt   28.4K   64 downloads

[paul]

Okay, got it to work (assuming I was looking at the right fields).

1) Convert .txt file to .dbf and add to arcmap
2) Find common fields to link
A) shapefile (nyct2000.shp): CTLabel (string, 7)
B) table (racnewyo.dbf): TRACT (long integer)
3) The TRACT field in the table needs to be divided by 100 in order to correspond with the CTLabel field, which contains decimals. For example, it appears that CTLabel "2.01" corresponds to TRACT "201". In the racnewyo.dbf table, I added two new blank fields: TractDbl (double) and TractTxt (string, 7). I then filled the TractDbl field with the field calculator expression: [TRACT] / 100, which converted the long integer to double-precision decimal. Next, I filled the TractTxt field with the filed calculator expression: [TractDbl], which converted the double-precision number to a text string. At this point it is ready to join.
4) Joining to the shapefile works, although some tracts are still null because the table does not appear to contain all of the CTLabel values. However, when joining the shapefile to the .dbf table, there are no nulls.

I think the key is to do all this in ArcMap rather than Excel.

Hope that helps.

--Paul

Attached Files

•  racnewyo.zip   16.36K   52 downloads

[benbakelaar]

Awesome Paul, thanks a lot. I think my comp or software has some issues. If I open your DBF, I can add fields. But if I open any of my TXT, CSV, or DBF files, it is not an option. And I think you are right, working in ArcMap is the answer, but it seems my ArcMap is not working properly! Or the permissions on my comp... I'm gonna try on a different computer tonight.

Okay, got it to work (assuming I was looking at the right fields).

1) Convert .txt file to .dbf and add to arcmap
2) Find common fields to link
    A) shapefile (nyct2000.shp): CTLabel (string, 7)
    table (racnewyo.dbf): TRACT (long integer)
3) The TRACT field in the table needs to be divided by 100 in order to correspond with the CTLabel field, which contains decimals. For example, it appears that CTLabel "2.01" corresponds to TRACT "201". In the racnewyo.dbf table, I added two new blank fields: TractDbl (double) and TractTxt (string, 7). I then filled the TractDbl field with the field calculator expression: [TRACT] / 100, which converted the long integer to double-precision decimal.  Next, I filled the TractTxt field with the filed calculator expression: [TractDbl], which converted the double-precision number to a text string. At this point it is ready to join.
4) Joining to the shapefile works, although some tracts are still null because the table does not appear to contain all of the CTLabel values. However, when joining the shapefile to the .dbf table, there are no nulls.

I think the key is to do all this in ArcMap rather than Excel.

Hope that helps.

--Paul

<{POST_SNAPBACK}>