topic Re: Using Arcpy to join tables? in Python Questions

Using Arcpy to join tables?

DanielFuller — Sat, 15 May 2021 22:15:37 GMT

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

Re: Using Arcpy to join tables?

DavidPike — Sat, 15 May 2021 22:50:11 GMT

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

Re: Using Arcpy to join tables?

DanielFuller — Sun, 16 May 2021 09:59:06 GMT

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?

Re: Using Arcpy to join tables?

DavidPike — Sun, 16 May 2021 13:08:03 GMT

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

Re: Using Arcpy to join tables?

DavidPike — Mon, 17 May 2021 12:28:09 GMT

did that work for you Dan?

Re: Using Arcpy to join tables?

DanielFuller — Thu, 20 May 2021 11:12:46 GMT

yes thankyou!