Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!! ive 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? am I doing this wrong? is there a better way?

share|improve this question
    
See the related thread at gis.stackexchange.com/questions/12242/… –  whuber Aug 5 '11 at 16:23
1  
Export it to some other format, such as CSV, Access or DBF. Excel is terrible for data consistency- whuber's answer to this related question explains why. –  blah238 Aug 7 '11 at 2:51

7 Answers 7

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).

share|improve this answer
    
Ive tried saving it as a CSV but it doesnt let me join it, i imported the table into a database but that wont show up as one of the files to join with! anything else? –  dpc1989 Aug 5 '11 at 16:00
    
Do you have the database open elsewhere while you're trying to join it in Arc? Also you can select it from its location on your computer as well, perhaps it's just not showing up properly? –  Emily Aug 5 '11 at 16:14
    
the databases arnt showing up in Arc catalogue either? ive done it through Access... its just spreadsheets and things like that –  dpc1989 Aug 5 '11 at 16:28
    
Also you can do the join in ArcCatalogue, reflecting on Dave's point. You would use the Add Join function in the toolbox (under Data Management). –  Emily Aug 5 '11 at 16:29
    
remove spaces and special characters from the field names –  Brad Nesom Aug 7 '11 at 2:53

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

share|improve this answer

In my expirience, 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.

I hope this is helpful for you.

Regards from Steen ARTOGIS a/s Denmark

share|improve this answer

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.

HTH

share|improve this answer

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

share|improve this answer

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

share|improve this answer
    
"if the rows are not in the same order in both tables" - I don't believe this is true. Row position is unrelated for joining; an example of this would be a one-to-many join which can be done with a shapefile and an excel table. –  djq Aug 7 '11 at 20:45
    
this seems to happen for me every time i try to join spatial data with non spatial data i end up with blank data fields. if i sort the rows to match then it joins perfectly. but i think you are right celenius this should not happening any ideas why this is happening? –  ghostfacemapper Aug 7 '11 at 21:02
    
Are you sure you have matching data types? For example '1' stored as a string and '1' stored as a float will not match each other, even if the field name is the same. I think a safe way of doing joining is to export the data as a csv, making sure that there are no spaces in filenames or fieldnames, making sure that your data types match. I would be careful about editing the shapefile directly - I think things could go wrong quite easily. –  djq Aug 7 '11 at 21:39
    
thanks celenius next time i will try to do a join i will use a csv file and see what happens –  ghostfacemapper Aug 10 '11 at 10:24

@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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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