Joining Excel table to shapefile in ArcMap?

Question

I have a shapefile of lower layer super output areas (LSOA) for England and Wales and I have a spreadsheet of the indices of deprivation for each of the LSOA's in England. I want to join the deprivation data to the shapefile so it can be mapped in ArcMap. I tried joining the table through the Join and Relates > Join option and put in the fields for the LSOA data for both shapefile and spreadsheet (the data that matches). the attribute table added the datafields to the attribute table from the excel spreadsheet but all of the data is blank!!

I have tried looking at the field "type" as ive read that it has been an issue before but it seems to be OK.

Is there anything anyone can suggest?

Answer 1

Here is the ESRI help page on joining a shapefile to an Excel file: http://support.esri.com/en/knowledgebase/techarticles/detail/31793

I've followed those guidelines in the past when I had problems and I was able to get the data joined.

For some more information, here is a whitepaper from ESRI on working with Excel in ArcGIS:

http://www.esri.com/news/arcuser/0104/files/excel.pdf

Answer 2

Are the types the same (i.e. both integers/both doubles) both in Excel and ArcMap? Also you may need to make the excel file into a database or CSV in order to join them (just use 'save as' in Excel to change the file type). I've had trouble with Excel files in the past, sometimes it is also because it's the .xlsx (new filetype for the new MS Office) rather than .xls (which should work with Arc).

Answer 3

In my experience, you need to add an objectID to your Excel spreadsheet, giving each post a unique number (integer) before making the join in ArcMap. You can add the extra field and then start typing 1 in the first row, 2 in the next, then mark up the two values and drag the markup downwards - then Excel fills out the blanks for you.

Answer 4

Make sure there are no spaces in filenames, sheetnames or fieldnames.

Answer 5

Check all your numeric fields for hidden padding. Excel can be very naughty, silently tacking on zeroes AND hiding them from you. I've experienced this on several occasions, most recently in a column of census tract FIPS codes. (Anybody know why?)

Get into a blank cell and pull the first digit from a value that should not start with zero using LEFT([cell],1). Then flip it around and check a value that should not end with zero using the RIGHT([cell],1) function. You can use Excel's trimming functions if you do find stowaways.

Answer 6

do the fields you are trying to join match each other exactly?, if the rows are not in the same order in both tables you will always get blank spaces.

if you make a copy the shapefile and open the dbf of the copy in excel you can paste your data into the copy and apply "save as" and overwrite data to new shapefile

Answer 7

@ghostfacemapper the records do not need to be in the same order. Not true at all.

Now, if you are getting an error that says:

ERROR 000339: Input [YOUR FILE NAME] does not have OIDs Failed to execute (Join Field).

You can fix the problem by 'adding' an objectID field by using arcGIS to make a new table that has an objectID. You can do this by exporting your file into dBASE Table format. To do so, add the file (table) to a map, you will be able to see the file in the TOC under the List By Source tab:

1. right-click on the table
2. click Data
3. click Export
4. click on the browse button
5. change the "Save as Type" to "dBase Table"
6. give it a new name and location
7. click save
8. use the new file that you just made for the join
9. find someone to high-five!