Join a Feature Layer with a Excel file using arcpy

abhishek_595 — Mon, 25 Sep 2023 13:02:09 GMT

Hi,

I'm attempting to perform a join between an existing feature layer containing country boundary polygons and an Excel file. The relationship between these two datasets is one-to-many. However, when I use the `AddJoin` function from arcpy, the resulting dataset only has a one-to-one join, and the column names in the joined result are not coming from the two original data sources. I've included a code snippet below for reference, and I'm seeking assistance in understanding what might be incorrect with it.

Code:

xlsx_file_path = input("Enter the path to the file: ")

file_path=os.path.join(os.getcwd(),"MyProject1.gdb",'data_table'+str(int(dt.datetime.now().timestamp())))
arcpy.conversion.ExcelToTable(xlsx_file_path, file_path)
arcpy.MakeTableView_management(file_path, 'table_view2')

feature_layer = gis.content.get('b587ef6d8a5b48e382f5fb8f1ccc46ab')
arcpy.MakeFeatureLayer_management("https://services7.arcgis.com/yxpZft6sXAxt864n/arcgis/rest/services/countries/FeatureServer/0", out_layer='outfeatureLayer')

arcpy.management.AddJoin('outfeatureLayer', 'ISO_A3','table_view2', 'ISO_A3','KEEP_COMMON','INDEX_JOIN_FIELDS')

arcpy.CopyRows_management('outfeatureLayer', "C:/new_19.dbf")

Also please suggest any other function if it is applicable here. Currently I am refrencing to https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/add-join.htm tutorial

Thanks & Regards,

Abhishek Kumar Choudhary

Re: Join a Feature Layer with a Excel file using arcpy

TylerGraham2 — Mon, 25 Sep 2023 13:53:39 GMT

Are you getting a successful join that is missing some columns or is it a completely empty output?

In the add join tutorial you are referencing, it does say if you are making a join that doesn't use ObjectIDs you only get a 1 to 1 join based on the first match found. If I recall correctly, hosted feature layers don't really like ObjectIDs so if you are getting a 1:1 join, that is probably why it is happening.

In my experience though, I've not ever been able to get arcpy to work with hosted content, so if you're getting empty outputs, or really strange results that might be the problem. I think if you try the add join through the ArcGIS for Python API, you'd have better luck. There's documentation on the tool here with an example farther down the page.

https://developers.arcgis.com/documentation/mapping-apis-and-services/spatial-analysis/feature-analysis/combine-data/join-features/

topic Re: Join a Feature Layer with a Excel file using arcpy in ArcGIS Pro Questions

Join a Feature Layer with a Excel file using arcpy

Re: Join a Feature Layer with a Excel file using arcpy