Table to table script using Excel sheets (not workbooks) arcpy

Question

So what I'm trying to do is to create feature classes from the sheets in an Excel table. I can get it to work if I save each excel sheet as its own workbook, however this isn't really what I want. I an a complete newbie so I'm sure the answer is pretty obvious. I think it is something to do with the $ in the table names, I can't work out how to strip it though. Any help would be much appreciated, Thanks

Working code

fileList = arcpy.ListFiles("*.xlsx")
print 'Listing .xlsx files in workspace'

try:

# Loop through each file
    for file in fileList:
        print file
        arcpy.env.workspace = os.path.join(r"C:\\Users\\Jane\\Documents\\GIS Project\\BasicTest", file)

    # Use splitext to set the output name of the table
        dbfFile = os.path.splitext(file)[0] #+ ".xlsx"
        if arcpy.Exists(dbfFile+".dbf"):
        print "deleting"
        arcpy.Delete_management(dbfFile+".dbf")

        # Convert excel to DBF
        arcpy.TableToTable_conversion("Sheet1$", outPath, dbfFile+ ".dbf")

        print 'File conversion. Converted ' + file

Not working code:

tableList = arcpy.ListTables()
print 'Listing table files in sheet'


for table in tableList:
    print table
    arcpy.env.workspace = os.path.join(r"C:\\Users\\Jane\\Documents\\GIS Project", table)

    # Use splitext to set the output name of the table
    dbfFile = os.path.splitext(table)[0] #+ ".xlsx"
    if arcpy.Exists(dbfFile+".dbf"):
        print "deleting"
        arcpy.Delete_management(dbfFile+".dbf")

    # Convert excel to DBF
    arcpy.TableToTable_conversion(table, outPath, dbfFile+ ".dbf")

    print 'File conversion. Converted ' + table
    

Error:

Traceback (most recent call last):
  File "C:/Users/Jane/Documents/GIS Project/BasicTest/3rd Script.py", line 33, in <module>
  arcpy.TableToTable_conversion(table, outPath, dbfFile+ ".dbf")
  File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\conversion.py", line 1904, in TableToTable
  raise e
ExecuteError: ERROR 000354: The name contains invalid characters
Failed to execute (TableToTable).

Answer 1

This will loop though files in a folder and output the worksheets to a file geodatabase table. It can output straight to FC too, but I've commented that out as you need to change the X and Y entries to match what represents coordinates in your xls worksheet.

Just make sure you only have the xls files in your folder.

If you are using xlsx, try resaving them as xls (Excel 2003). I don't think ArcGIS 10.0 supports xlsx.

I've tested it on 10.1 and both xls and xlsx worked.

Finally, don't have the xls open in Excel, as it creates file locking issues with ArcGIS.

import arcpy
import os

arcpy.env.overwriteOutput = True

inWS = r'C:\temp\xls'
outGDB = r'C:\temp\MyFGDB.gdb'
arcpy.env.workspace = inWS

lf = arcpy.ListFiles()

for xls in lf:
    arcpy.env.workspace = os.path.join(inWS, xls) #Set workspace to the workbook

    ltabs = arcpy.ListTables() #List worksheets in xls workbook

    for tab in ltabs:
        outFC = tab.replace('$', '') #Name for output FC
        outTab = outFC + '_TAB'     #Name for output table
        outLyr =  outFC + '_lyr'    #Temp layer name for XY event lyr

        #*** Output to a flat table ***
        arcpy.TableToTable_conversion(tab, outGDB, outTab)

        #*** Output to a feature class ***
        #*** Change X and Y to column names found in xls with co-ords ***
##            arcpy.MakeXYEventLayer_management(tab, 'X', 'Y', outLyr)
##            arcpy.FeatureClassToFeatureClass_conversion(outLyr, outGDB, outFC)

print 'Process complete'

Answer 2

Here in ESRI's page about Excel files in ArcGIS, they mention that the $ is deliberately appended to table names.

Your error message could also indicate invalid field names. Check out the error description here.

Assuming you know what field names your script will be processing, you could strip or replace invalid characters before processing them:

fieldName.replace('$','')  # strip $

or

fieldName.replace(' ','_')  # replace space with underscore

Answer 3

I found a script from UA that appears to accomplish what you are working on. Hopefully, you can either use this script or gather some ideas from it.

# Import module and set workspace
import arcpy
arcpy.env.workspace = r'C:\NRM638\Data\climate_stations'


# List xcel files in the workspace
xcelFiles = arcpy.ListFiles('*.xls')


#Create an empty list
layerList = []


# Loop through the xcel files
for file in xcelFiles:


    # Determine the name of the file; use it to create names for
    #temporary tableview and point feature

    fileName = file[:-4]
    tableName = fileName + '_TableView'
    layerName = fileName + '_PointLayer'


    # Create a table view of the current excel file
    arcpy.MakeTableView_management(file + '\\sheet1$', tableName)


    # Create a point feature layer from the values in the table
    arcpy.MakeXYEventLayer_management(tableName,"Longitude","Latitude", layerName)


    # Add the point layer name to the list
    layerList.append(layerName)


# Merge the point layers into one point feature class
arcpy.Merge_management(layerList,'climate_stations_points')