topic Looping through Excel Worksheets within a Workbook, then Creating XY points in Python Questions

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

ssintelmann — Thu, 28 Apr 2022 17:52:08 GMT

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])

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

JakeSkinner — Thu, 28 Apr 2022 18:16:23 GMT

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

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

ssintelmann — Thu, 28 Apr 2022 19:23:53 GMT

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.

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

JakeSkinner — Fri, 29 Apr 2022 12:49:36 GMT

@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