5

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).
4
  • Could you give an example of a table name in your dataset?
    – Aaron
    Nov 13, 2012 at 14:44
  • Incident$ Operation_Period$
    – jay
    Nov 13, 2012 at 15:00
  • I've used openpyxl in the past to read from .xlsx files from Python and generate feature classes from the data. If you're interested I can write up a full code sample as an answer here.
    – dmahr
    Nov 13, 2012 at 15:25
  • That might work, though it seems like arcpy should do it, which would be better for my application than having to download other addons. The end goal is to update existing feature classes in a gdb using the excel data, though I'm starting to think I'm just doing this all wrong.
    – jay
    Nov 13, 2012 at 15:45

3 Answers 3

2

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'
1

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
0

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')
1
  • I've seen this one, couldn't get it to work for some reason. Maybe I'm just going about this all wrong. The end goal is to populate empty feature classes in a geodatabase from excel data, therefore farming out the data entry away from the gis operator.
    – jay
    Nov 13, 2012 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.