topic Re: How to process an excel file with several tabs. in Python Questions

How to process an excel file with several tabs.

JoseSanchez — Fri, 19 Apr 2024 19:11:17 GMT

Good afternoon,

How can I process an Micrsoft Excel file with several tabs. Each tab represents a department, and inside the tab the structure is similar, but not identical, some tabs have more columns than others.

Thanks

Re: How to process an excel file with several tabs.

BobBooth1 — Fri, 19 Apr 2024 19:33:46 GMT

You can read Excel data into dataframes and process them.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

https://developers.arcgis.com/python/guide/part3-introduction-to-pandas/

You can also import Excel sheets to geodatabase tables with Excel to Table.

https://pro.arcgis.com/en/pro-app/latest/tool-reference/conversion/excel-to-table.htm

Re: How to process an excel file with several tabs.

TonyAlmeida — Fri, 19 Apr 2024 20:14:13 GMT

What do you mean by "process"? Are you joining them to a feature class or exporting them to .dbf, csv file?

Re: How to process an excel file with several tabs.

MoKanRPC — Fri, 19 Apr 2024 21:26:59 GMT

Yeah pretty much, I'm joining multiple tabs of thematic county level ag census data by 6 specific fips codes, copy features to gdb and then remove the join and do the next one. If you have to do this over and over you're "processing" it

Re: How to process an excel file with several tabs.

TonyAlmeida — Mon, 22 Apr 2024 15:55:43 GMT

It's untested, but try this,this takes all the sheets from the excel file and adds a join to a feature class, and exports the new feature class along with the join sheet. Hope it helps.

Adjust the script to your needs, line 6,9,12,34,35.

import arcpy import os from openpyxl import load_workbook # Set the workspace (folder or geodatabase) where the Excel file is stored arcpy.env.workspace = "C:/Temp" # Path to the Excel file excel_file = "C:/Temp/Test.xlsx" # Path to the feature class feature_class = "FeatureClass" #path to your feature class # Load the Excel workbook workbook = load_workbook(excel_file) # Get the list of worksheet names worksheet_names = workbook.sheetnames # Loop through each worksheet for worksheet_name in worksheet_names: # Create a unique name for the table view by replacing problematic characters table_view_name = f"View_{worksheet_name.replace('$', '_')}" # Create a full path to the Excel worksheet worksheet_path = os.path.join(excel_file, f"${worksheet_name}") print(f"Attempting to open worksheet: {worksheet_path}") # Create a table view from the Excel sheet arcpy.MakeTableView_management(in_table=worksheet_path, out_view=table_view_name) print(f"Table view created for {worksheet_name}") # Define the join field name, common between the feature class and the Excel sheet join_field_fc = "JoinField" # Join field for the feature class join_field_sheet = "JoinField" # Join field for the sheet # Add a join field from the Excel sheet to the feature class arcpy.JoinField_management(in_data=feature_class, in_field=join_field_fc, join_table=table_view_name, join_field=join_field_sheet) # Optionally, you can export the joined data to a new feature class output_feature_class = f"Joined_{worksheet_name.replace('$', '_')}" arcpy.CopyFeatures_management(in_features=feature_class, out_feature_class=output_feature_class) print(f"Joined feature class created: {output_feature_class}")

Re: How to process an excel file with several tabs.

TonyAlmeida — Mon, 22 Apr 2024 18:48:23 GMT

I test my previous code and it didn't work. I tested this one and it works inside a file geodatabase. It will export the excel file sheets into the file geodatabase, then preforms the join to the layer and exports them to the file geodatabase.

Change the layer, table and join fields.

import arcpy import os from openpyxl import load_workbook # Set the workspace (geodatabase) where the tables are stored arcpy.env.workspace = "C:/Temp/Testdelete/Testdelete.gdb" # Path to the feature class feature_class = r"C:/Temp/Layer.shp" # to your layer # Load the Excel file excel_file = "C:/Temp/table.xlsx" # to your table # Load the Excel workbook workbook = load_workbook(excel_file) # Get the list of worksheet names worksheet_names = workbook.sheetnames # Create a feature layer from the feature class feature_layer = "TaxParcels_Layer" arcpy.MakeFeatureLayer_management(in_features=feature_class, out_layer=feature_layer) # Loop through each worksheet for worksheet_name in worksheet_names: # Create a unique name for the table by replacing problematic characters table_name = f"Table_{worksheet_name.replace('$', '_')}" # Create a full path to the Excel worksheet worksheet_path = os.path.join(excel_file, f"{worksheet_name}$") print(f"Attempting to open worksheet: {worksheet_path}") # Create a table view from the Excel sheet arcpy.MakeTableView_management(in_table=worksheet_path, out_view=table_name) print(f"Table view created for {worksheet_name}: {table_name}") # Convert the table view to a standalone table table_out = os.path.join(arcpy.env.workspace, table_name) arcpy.TableToTable_conversion(in_rows=table_name, out_path=arcpy.env.workspace, out_name=table_name) # List fields in the feature class feature_fields = [field.name for field in arcpy.ListFields(feature_class)] # List fields in the join table table_fields = [field.name for field in arcpy.ListFields(table_out)] # Check if "ACCOUNT" exists in both feature class and join table if "LyrJoinFied" in feature_fields and "Acct" in table_fields: # Join the feature layer with the table arcpy.AddJoin_management(in_layer_or_view=feature_layer, in_field="LyrJoinField", join_table=table_out, join_field="TableJoinField") # Export the joined data to a new feature class output_feature_class = f"Joined_{worksheet_name.replace('$', '_')}" arcpy.CopyFeatures_management(in_features=feature_layer, out_feature_class=output_feature_class) # Remove the join to prepare for the next iteration arcpy.RemoveJoin_management(feature_layer) print(f"Joined feature class created: {output_feature_class}") else: print("Field 'ACCOUNT' or 'Acct' does not exist in feature class or table.") print("All joins completed.")