Using Arcpy to join tables?

[DanielFuller]

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

LAD_to_LAU1..etc...etc..

Shape File Attribute Table

[DavidPike]

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

[DanielFuller]

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?

[DavidPike · Accepted solution]

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

[DavidPike]

did that work for you Dan? 

[DanielFuller]

yes thankyou!