Solved! Go to Solution.
import os import xlrd import arcpy """ Modified function ExcelToTable example 2 (stand-alone script) from: http://resources.arcgis.com/en/help/main/10.2/index.html#//001200000055000000 """ def importallsheets(in_dir, out_gdb): arcpy.env.workspace = in_dir xlsxfiles = arcpy.ListFiles('*.xls*') for xlsx in xlsxfiles: workbook = xlrd.open_workbook(xlsx) sheets = [sheet.name for sheet in workbook.sheets()] print('{} sheets found: {}'.format(len(sheets), ','.join(sheets))) for sheet in sheets: out_table = os.path.join(out_gdb, arcpy.CreateUniqueName( arcpy.ValidateTableName( "{0}_{1}".format(os.path.basename(xlsx), sheet), out_gdb), out_gdb)) print('Converting {} to {}'.format(sheet, out_table)) # Perform the conversion arcpy.ExcelToTable_conversion(xlsx, out_table, sheet) if __name__ == '__main__': # usage: importallsheets( input_directory, output_geodatabase ) importallsheets(r'C:\Users\whitley-wayne\Desktop', r'C:\Users\whitley-wayne\Desktop\New File Geodatabase.gdb')
import os import xlrd import arcpy """ Modified function ExcelToTable example 2 (stand-alone script) from: http://resources.arcgis.com/en/help/main/10.2/index.html#//001200000055000000 """ def importallsheets(in_dir, out_gdb): arcpy.env.workspace = in_dir xlsxfiles = arcpy.ListFiles('*.xls*') for xlsx in xlsxfiles: workbook = xlrd.open_workbook(xlsx) sheets = [sheet.name for sheet in workbook.sheets()] print('{} sheets found: {}'.format(len(sheets), ','.join(sheets))) for sheet in sheets: out_table = os.path.join(out_gdb, arcpy.CreateUniqueName( arcpy.ValidateTableName( "{0}_{1}".format(os.path.basename(xlsx), sheet), out_gdb), out_gdb)) print('Converting {} to {}'.format(sheet, out_table)) # Perform the conversion arcpy.ExcelToTable_conversion(xlsx, out_table, sheet) if __name__ == '__main__': # usage: importallsheets( input_directory, output_geodatabase ) importallsheets(r'C:\Users\whitley-wayne\Desktop', r'C:\Users\whitley-wayne\Desktop\New File Geodatabase.gdb')
xlsxfiles = arcpy.ListFiles('*.xls*') for xlsx in xlsxfiles: result = arcpy.ListTables() print xlsx, result
from arcpy import env env.workspace = r'whatever root directory file path you're listing files from' xlsxfiles = arcpy.ListFiles('*.xls*') for xlsx in xlsxfiles: env.workspace = xlsx result = arcpy.ListTables() print xlsx, result
11 years later, still an issue. I just expected Iterate Files to work. It doesn't. There's all kinds of workarounds but Esri should just make this work out of the box. I'm only doing this large model today anyway because Transfer Domain Descriptions has been a bug for 15 (20?) years. Yes, that setting in Environments, which doesn't work in ArcMap or Pro 3.5. Only GP tool without the bug is FC to Excel. So I'm iterating to join Excel to a blank SHP by GUID. (python doesn't work either. the sample straight from the docs doesn't work, on a simple file gdb)
The Excel to Table works. Iterate Files to do the same thing, doesn't. I get the worksheet thing. But make some GUI to deal with worksheets. Excel is a common enough thing this warrants fixing at some point.