How to process an excel file with several tabs.

[JoseSanchez]

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

[BobBooth1]

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

[TonyAlmeida]

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

[MoKanRPC]

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 

[TonyAlmeida]

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

[TonyAlmeida]

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