0

So I have been trying to get this to work all day and have had no success. I am attempting to join a CSV file with a Shapefile. The CSV is from the Hud's location affordability portal (http://www.locationaffordability.info/lai.aspx?url=download.php) and the shapefile is coming from tiger.

I have tried moving the file into microsoft access and converting it to a dbf it tells me that I "cannot define field more than once" and when I do a find replace to in excel it changes the number to scientific notation. I have tried changing the format of the column and leaving it alone in hopes that it would not be effected, but that is not the case.

any thoughts?

  • 1
    Do you have a duplicated field name? You're definitely barking up the right tree. CSV is horrible to join too, and so is Excel, possibly use TABLE TO TABLE (Conversion) to convert to something else that will work. DBF has a limit of 10 characters per field so it only needs to be the first 10 characters the same to be a duplicate field. Do you have Microsoft Access or the open office equivalent? – Michael Stimson Jul 16 '14 at 22:21
2

Walter: the HUD data uses ID's for the blockgroups (BLKGRP) with single quotes around them... the TIGER blockgroup ID's (GEOID) do not have single quotes:

enter image description here

I would say that you should create a new column using Excel / Open Office to truncate the first and last values from the field into the new column...but I can't remember how to do that right now!!!

Once you have that new field with the blockgroup ID's with no single quotes, you should be able to do the join just fine.

HUD should really not be putting single quotes around those ID's...

  • Dude, he's using ArcGis desktop. That's really good information to know (+1 for that), to cleanse the single quotes use field calcuator on the field and use the replace function or mid to get rid of the single quotes: blkgrp = replace([blkgrp],"'",""); it would be best to calculate to a new field just in case the values are corrupted. The issue that Walter is experiencing is Esri (at 9.3) has a real problem joining to CSV, shapefiles have a limit on field lengths, so it is definitely best to convert to personal geodatabase to join. – Michael Stimson Jul 16 '14 at 23:22
  • 1
    @MichaelMiles-Stimson DUDE where's my ArcGIS license? Glad you found the real problem. I used QGIS to look at the data. – DPSSpatial Jul 17 '14 at 14:54
  • 1
    I just got it to work. I had converted the CSV to DBF using arcgis (table to Geodatabase). Then I had to create a new field (text format) and use the field calculator using Michael's code and then was able to do a join. Thanks for everyone's help. @mapBaker I forgot to write down that I was aware of the quotations. I was looking for a way to remove them and join it sorry. – AvidDabbler Jul 17 '14 at 15:50
  • good stuff @Walter.K.Jenkins! – DPSSpatial Jul 17 '14 at 15:52
  • 1
    @mapBaker, it's only half an answer, more like some tips to make the process work better. I suggest that you edit your answer to include the calculation and mention that Esri has issues joining to CSV (a very common issue) and it would work better as a pGDB, fGDB or DBF table. I've already voted for your answer and it's been accepted so I don't think another answer would be warranted. – Michael Stimson Jul 17 '14 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.