Looping through Excel Worksheets within a Workbook, then Creating XY points

ssintelmann

I am trying to loop through a bunch of Excel Worksheets within a Workbook and finally creating XY points inside a GDB.  It seems what I have done works on the first worksheet only, but it doesn't do anything with the other worksheets.  Maybe I have the for loop part wrong since it never gets past first sheet.  Any ideas please?

 

 

import arcpy
import os

#set local variables
arcpy.env.overwriteOutput = True
xlsxWS = r'C:\Data\working\EBS16\raw_sonar_files'
outGDB = r"C:\Data\working\EBS16\EBS_16_analysis_display\default.gdb"
finalOUT = r"C:\Data\working\EBS16\EBS_16_analysis_display\default.gdb\em710vals\em50_"
arcpy.env.workspace = xlsxWS

lf = arcpy.ListFiles()
try:
    for xlxs in lf:
        arcpy.env.workspace = os.path.join(xlsxWS, xlxs) # sets the arcpro workspace to the workbook
        lTables = arcpy.ListTables() # lists worksheets in the xlsx
        for tab in lTables:
            outFC = tab.replace("$", '') #remove invalid characters from sheet names
            outLyr = outFC + '_lyr' # create name for temp layer in XY event theme
            #if want to create a table
            #arcpy.TableToTable_conversion(tab,outGDB, outFC)
            arcpy.MakeXYEventLayer_management(tab,'easting', 'northing', outLyr)
            outFeat =  finalOUT + outFC # naming convention
            arcpy.management.CopyFeatures(outLyr, outFeat) # make lyr permanent
except Exception as e:
    #dump errors
    print("Error: " + e.args[0])

 

 

JakeSkinner

Hi @ssintelmann,

I would recommend using the Excel to Table function to convert the excel sheets.  To iterate through an excel workbook to get the sheets, you could use pandas to do this.  Ex:

 

import pandas as pd
import arcpy, os, re

# Variables
arcpy.env.workspace = r"C:\Projects\Documents"
outputGDB = r"C:\Projects\Data.gdb"

for file in arcpy.ListFiles("*.xlsx"):
    f = os.path.join(arcpy.env.workspace, file)
    # Get excel sheets
    sheetNames = pd.ExcelFile(f).sheet_names

    # iterate through sheets
    for sheet in sheetNames:
        # Replace non alphanumeric characters with underscore
        sheetName = re.sub(r'[^0-9a-zA-Z]+', r'_', sheet)
        # Convert sheet to GDB table
        arcpy.conversion.ExcelToTable(f, os.path.join(outputGDB, sheetName), sheet, 1, '')