python script tool - need to join csv

[Seyed-MahdySadraddini]

I am building a toolbox. I am stuck at the last step in my python script tool. I have csv file located outside the project folder (outside ArcGIS env). I need to join the csv to a featureclass in the project. I have tried arcpy.management.AddJoin() method to directly join the csv table to fc, but that doesn't work; I assume because the csv table is outside ArcGIS env.

Then I tried to convert the csv to dBase using TableToDBASE_conversion and TableToTable_conversion to see if that works, but the conversion is not happening, seemingly the input csv table cannot be read:

ERROR 000735: Input Rows: Value is required

Anyhow, the objective is to join the csv table from outside ArcGIS to the featureclass.

Is there a straightforward way to accomplishing this task by relying on arcpy and os libraries? I have seen a suggestion of geopandas but I do not want a geopandas based solution.

Here is the code I have...

# set the environment variables
arcpy.env.overwriteOutput = True
arcpy.env.workspace = gdb

# Export Table conversion for bringing the table into ArcGIS
arcTable = arcpy.TableToDBASE_conversion(Input_Table=t, Output_Folder=folder)

# t is a pandas dataframe that is exported to_csv()

# Process to creating a new layer
s = arcpy.management.AddJoin(in_layer_or_view=appropriate_shapefile
,in_field=Join_Field_on_Shapefile
,join_table=arcTable
,join_field='UID'
,join_type='KEEP_COMMON')
arcpy.conversion.ExportFeatures(s, layer_name)
arcpy.management.RemoveJoin(s)

[TonyAlmeida]

The join feature has be converted into a feature layer (arcpy.management.MakeFeatureLayer(shapefile, feature_layer) prior to joining.

# Create a feature layer from the shapefile
arcpy.management.MakeFeatureLayer(shapefile, feature_layer)

# Perform the join
joined_layer = arcpy.management.AddJoin(
    in_layer_or_view=feature_layer,  # Use the feature layer here
    in_field=Join_Field_on_Shapefile,
    join_table=csv_table,
    join_field='UID',
    join_type='KEEP_COMMON'
)

[Seyed-MahdySadraddini]

Thanks for the suggestion. I added the feature layer. But I think the issue at hand is the fact that csv file from outside ArcGIS env is not read properly. I got ERROR 000735 again!

I modified the script by reading the csv file into in-memory dataframe (table2) in pandas. Value Error is raised saying: Invalid file path or buffer object type: <class 'NoneType'>. I know that somehow I have to modify the csv table outside ArcGIS within the ArcGIS env.

If I was using ArcGIS GUI, then I would drag and drop the csv into table of contents. I know I need to do something similar so the csv file is readable by feature classes (inside ArcGIS env).

How should I proceed with this problem?

t = table.to_csv(os.path.join(folder, f'{table_name}.csv'), index=False)

# set the environmental variables
arcpy.env.overwriteOutput = True
arcpy.env.workspace = gdb

# Read csv into dataframe
table2 = pd.read_csv(t)

# Process to creating a new layer
# Create a feature layer from the shapefile
arcpy.management.MakeFeatureLayer(appropriate_shapefile, layer_name)

# Join data and export
s = arcpy.management.AddJoin(in_layer_or_view=layer_name
,in_field=Join_Field_on_Shapefile
,join_table=table2
,join_field='UID'
,join_type='KEEP_COMMON')
arcpy.conversion.ExportFeatures(s, layer_name)

[TonyAlmeida]

Have you tried converting the csv file with arcpy.conversion.TableToTable prior to the join?

[Seyed-MahdySadraddini]

Yes. I tried tabletotable conversion as well but that caused ERROR 000735 as well.

[Seyed-MahdySadraddini · Accepted solution]

Thanks for walking with me through my problem @TonyAlmeida . I figured out the issue was the way I was supplying the csv file into the tool. Please consider this problem resolved!

[DanPatterson]

Does the first row of the csv contain the field names?

Can you post a few rows to see if there is something wrong with the csv?