Select to view content in your preferred language

Join a Feature Layer with a Excel file using arcpy

501
1
09-25-2023 06:02 AM
Labels (3)
abhishek_595
Occasional Contributor

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

0 Kudos
1 Reply
TylerGraham2
Frequent Contributor

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-analy...

0 Kudos