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 you do not mind multiple counties that relate to the different duplicated information you have for each county in your excel data...
- Convert your data in excel into a csv file (excel sheets can now be directly imported to ArcGIS but CSV has less issues).
- 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.
- 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.
If you want multiple records created for each county, and therefore multiple county points, you can do this by:
- Creating a geodatabase
- Import your excel table into the GDB as a table
- Import your county data as a feature class
- Join the table data to the county data
- 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")