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 · Accepted solution]

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, '')

[ssintelmann]

Thanks much. That worked perfectly.  I am new to Python.  I see this uses the re.sub() to replace various characters.  In your pattern 

[^0-9a-zA-Z]+

what exactly is that doing.  Does the '^' indicate do not match the number, or upper and lower case letter pattern for the replacement?  Thanks for the lesson, it helped.

[JakeSkinner]

@ssintelmann here is some info from the help:

• Characters that are not within a range can be matched by complementing the set. If the first character of the set is '^', all the characters that are not in the set will be matched. For example, [^5] will match any character except '5', and [^^] will match any character except '^'. ^ has no special meaning if it’s not the first character in the set.

[^a-zA-Z0-9_]:  Matches any character which is not a word character