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

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