4

I have ArcGIS 10.1. I am trying to make a shapefile out of an excel sheet that has county level data. I can't join this data based on the 'county' field to a county shapefile because I have multiple records in the same county (same county is repeated with a different information in other fields) . I tried the Join One to Many but you perform this tool on an excel sheet. Any suggestion?

  • If it truly has multiple records, it might be better as a relate than a join but you said "same county is repeated with different information in other fields". If the information is in other fields, of course, that is not a problem. – jbchurchill Oct 6 '15 at 13:32
  • 1
    County name isn't a particularly good join column, since many states have the same county names. FIPS code (5 digit identifier) is a much better join column – Vince Oct 6 '15 at 13:54
  • Thanks for your responses. I need the table that I have to be converted to shapefile. The table contains point data. The only spatial information that I have is the 'County Name'. and in one county, there are multiple points. – Mas Oct 6 '15 at 14:11
  • I'd suggest importing the Excel sheet to a csv or dbf first. Sometimes Excel sheets have quirky, unseen things that can interfere with joins. – recurvata Oct 6 '15 at 14:57
  • What @Vince said, unless this is just a learning exercise, joining based on just county names will lead to error due to duplicated county names in different states, does the excel data have additional information on state name or code, if so it is best to use it along with the county names. – yanes Oct 6 '15 at 16:55
1

If you do not mind multiple counties that relate to the different duplicated information you have for each county in your excel data...

  1. Convert your data in excel into a csv file (excel sheets can now be directly imported to ArcGIS but CSV has less issues).
  2. Import your csv table into ArcGIS and Join it to your county shape file data. Make the county shape file a target and the table the join. To avoid confusion during the join, check the box (Keep only joined records) that retains only joined counties.
  3. While the table is still joined with the shape file right click on the layer and export it in to a new shape. This is important as it is after you export it into another shape file you can obtain your 1:many joined shapefile.

Check the number of features in your newly exported file and see if it matches the records in your table. Remove the join on your original county shapefile.

  • The problem is that I have multiple data within the same county (I have mentioned this in my original question). After join, I will only get 1 record for 1 county... which means I am loosing all other data that is in the same county. Sorry if I caused any confusion. Thanks. – Mas Oct 6 '15 at 18:02
  • no you will get all the joins, once you did step 3 above. Just after the join that is after step 2, all you see is the join without the duplicates. I do not know why ArcGIS doesn't show the one to many join in the native shapefile where the table is joined. But as soon as you export the joined shapefile, it will give you the multiple records. I've done it multiple times, unless it is a version difference it should work. let me know. – yanes Oct 6 '15 at 18:07
  • It might be version problem that I am not getting all my 8800 records from my table in the state's shapefile. I just get one polygon per each record after I export my shapefile (total of 250 counties). Thanks anyways. – Mas Oct 6 '15 at 19:40
  • did you export it after joining the shape with the table, while it is joined? what version do you have? mine is 10.0. right after the join you will see 250 counties or less (if you used keep only matching option). Then when you export it you will get the full 8800 (or the ones that actually matched, as there might be a spelling difference, matching strings can be tricky). The export should happen from the geometry (right clicking the layer) not from the table of the joined shape. I've just done it now, just to make sure on a sample table and it worked. Also it doesn't work from model builder. – yanes Oct 6 '15 at 20:19
0

If you want multiple records created for each county, and therefore multiple county points, you can do this by:

  1. Creating a geodatabase
  2. Import your excel table into the GDB as a table
  3. Import your county data as a feature class
  4. Join the table data to the county data
  5. Export as a featureclass into the GDB

This should create multiple points for each county with the excel data.

Try the following script:

import os
import arcpy

folder = r"/path/to/folder"
result = arcpy.CreateFileGDB_management(folder, "my_gdb")
gdb = result.getOutput(0)
arcpy.env.workspace = gdb

excelfile = r"path/to/excel/file"

sheet = "sheet_name" 
table = arcpy.ExcelToTable_conversion(excelfile, "exceltable", sheet)

counties_shapefile = r"path/to/shapefile"
fc = arcpy.FeatureClassToFeatureClass_conversion(counties_shapefile, gdb, "counties")

temp_fl = arcpy.MakeFeatureLayer_management(fc, "temp_fl", "", "in_memory")
arcpy.env.qualifiedFieldNames = False

arcpy.AddJoin_management(temp_fl, "COMMON_FIELD", table, "COMMON_FIELD", "KEEP_COMMON")
arcpy.env.qualifiedFieldNames = False

arcpy.CopyFeatures_management(temp_fl, "counties_table_data")

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.