Using Arcpy to join tables?

691
5
Jump to solution
05-15-2021 03:15 PM
DanielFuller
New Contributor III

Hello

I'm using ArcMap and Python 2.7 to do a project

I have been given a shape file and some .CSV's, i need to workout the average value for a field in a given  polygon using python

CSV's:

  • LAD_to_LAU1_to_NUTS3_to_NUTS2_to_NUTS1_January_2018_Lookup_in_the_UK.csv
  • SportsStatisticsforPython

the two CSV's  join well using the ArcMap UI, however when running the join tool both from the Data Management toolbox and the in ArcMap Python Console i get the following error after trying to link "counties" of the SportsStatisticsforPython.csv to the "LAU118CD" field of LAD_to_LAU1...etc (see screenshots at the end of post)

ExecuteError: ERROR 000339: Input LAD_to_LAU1_to_NUTS3_to_NUTS2_to_NUTS1_January_2018_Lookup_in_the_UK.csv does not have OIDs Failed to execute (AddJoin).

can someone explain why its not working and how could go about fixing it, preferably in Python 2.7 as i will need to write a script that joins them together then finds the mean participation value for each county, which will then need to be linked to the relevant county in my shape file.

screenshots of the CSV and shapefile attribute tables 

SportsStatisticsforPython.CSV

DanielFuller_1-1621115864402.png

LAD_to_LAU1..etc...etc..

DanielFuller_4-1621116859281.png

 

Shape File Attribute Table

DanielFuller_3-1621116804587.png

 

0 Kudos
1 Solution

Accepted Solutions
DavidPike
MVP Frequent Contributor

Ah, it looks like a csv isn't considered a table.  A solution is to use CopyRows_management to create a table from the csv.  I would just create this in memory.

# create in memory path
memory = 'in_memory'
mem_table_path = os.path.join(memory, 'csv')

#create the in memory table
arcpy.CopyRows_management(PathToYourCSV, mem_table_path)   

#do the join
arcpy.JoinField_management(in_fc_path, in_field, mem_table_path, join_field, [fields])

View solution in original post

5 Replies
DavidPike
MVP Frequent Contributor

Possibly the join table needs to be Table View, try creating a table view from your CSV and then use that view to join against.

I'd recommend using Add Join Data Management to permeate the join.

Make Table View (Data Management)—ArcGIS Pro | Documentation

import arcpy

arcpy.MakeTableView_management("C:/data/myCSV.csv", "myCSV_view")
0 Kudos
DanielFuller
New Contributor III

Unfortunately that didn't work, reading the error the tables need object ID's but that doesn't make sense in the context of my project?

0 Kudos
DavidPike
MVP Frequent Contributor

Ah, it looks like a csv isn't considered a table.  A solution is to use CopyRows_management to create a table from the csv.  I would just create this in memory.

# create in memory path
memory = 'in_memory'
mem_table_path = os.path.join(memory, 'csv')

#create the in memory table
arcpy.CopyRows_management(PathToYourCSV, mem_table_path)   

#do the join
arcpy.JoinField_management(in_fc_path, in_field, mem_table_path, join_field, [fields])

View solution in original post

DavidPike
MVP Frequent Contributor

did that work for you Dan? 

0 Kudos
DanielFuller
New Contributor III

yes thankyou!