topic Re: Problem with xlsx file conversion and creation of XY event layer in Python Questions

Problem with xlsx file conversion and creation of XY event layer

EricSchultheis — Tue, 28 Feb 2012 19:54:46 GMT

I have a bunch of xlsx files with XY data for health care facilities. As an exercise to learn python better, I'm trying to (1) convert the xlsx files to dBase files, (2) create an event layer from the dBase files using the XY attribute fields, and (3) copy the event layers to shp files. Any help would be greatly appreciated. My code is bellow (note: all xlsx files have a single sheet called 1. The XY data in the xlsx files are in fields/columns POINT_X and POINT_Y.


import arcpy, os, sys, traceback

arcpy.env.workspace = 'D:\\AG_Vet\\ForImportGDB\\'
outPath = 'D:\\AG_Vet\\ForImportGDB\\Output\\'

# Local variables

xVal = "POINT_X"
yVal = "POINT_Y"

# Factory code for NAD 1983
factory_code_CS = 4269

sr = arcpy.SpatialReference()
sr.factoryCode = factory_code_CS
sr.create()

# List all files in folder then list all tables in file
fileList = arcpy.ListFiles("*.xlsx")
print 'Listing .xlsx files in workspace'

try:

    # Loop through each file
    for file in fileList:

        # Use splitext to set the output name of the table
        dbfFile = os.path.splitext(file)[0] + ".xlsx"

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

        print 'File conversion. Converted ' + file

    #List dBase tables
    tableList = arcpy.ListTables('',dBase)
    print ' List dBase tables in workspace'

    # Loop through each table
    for table in tableList:
    
        # Make XY event layer
        arcpy.MakeXYEventLayer_management(table, xVal, yVal, "points_" + table, sr)

        # Save output
        arcpy.CopyFeatures_management("points_"+table, outpath + table)

        print 'Created XY shp file for ' + table

    print 'Script complete.'


except:

    tb = sys.exc_info()[2]
    tbinfo = traceback.format_tb(tb)[0]
    pymsg = "PYTHON ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n     " +        str(sys.exc_type) + ": " + str(sys.exc_value) + "\n"
    msgs = "ARCPY ERRORS:\n" + arcpy.GetMessages(2) + "\n"

    arcpy.AddError(msgs)
    arcpy.AddError(pymsg)

    print msgs
    print pymsg
    
    arcpy.AddMessage(arcpy.GetMessages(1))
    print arcpy.GetMessages(1)

Re: Problem with xlsx file conversion and creation of XY event layer

MathewCoyle — Sun, 12 Dec 2021 04:12:22 GMT

You were very close, just a few workspace issues (you need to change the workspace to the actual .xlsx file to access the sheet, then back to the folder level to export the table and create the shapefile) and a couple small things. Give this a try, changing to your paths of course.

import arcpy, os, sys, traceback

arcpy.env.workspace = r"C:\GIS\xlsxTest"
outPath = r"C:\GIS\xlsxTest\output"
arcpy.env.overwriteOutput = True

# Local variables

xVal = "POINT_X"
yVal = "POINT_Y"

# Factory code for NAD 1983
factory_code_CS = 4269

sr = arcpy.SpatialReference()
sr.factoryCode = factory_code_CS
sr.create()

# List all files in folder then list all tables in file
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:\GIS\xlsxTest", 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

    #List dBase tables
    arcpy.env.workspace = outPath
    tableList = arcpy.ListTables("", "dBASE")
    print ' List dBase tables in workspace'

    # Loop through each table
    for table in tableList:
        print table
    
        # Make XY event layer
        arcpy.MakeXYEventLayer_management(table, xVal, yVal, "points_" + table, sr)

        # Save output
        arcpy.FeatureClassToShapefile_conversion("points_"+table, outPath)

        print 'Created XY shp file for ' + table

    print 'Script complete.'


except:

    tb = sys.exc_info()[2]
    tbinfo = traceback.format_tb(tb)[0]
    pymsg = "PYTHON ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n     " +        str(sys.exc_type) + ": " + str(sys.exc_value) + "\n"
    msgs = "ARCPY ERRORS:\n" + arcpy.GetMessages(2) + "\n"

    arcpy.AddError(msgs)
    arcpy.AddError(pymsg)

    print msgs
    print pymsg
    
    arcpy.AddMessage(arcpy.GetMessages(1))
    print arcpy.GetMessages(1)

Re: Problem with xlsx file conversion and creation of XY event layer

NilsBabel — Fri, 08 Mar 2013 15:43:36 GMT

Does this actually work in ArcGIS 10.1 SP1? I'm all of a sudden unable to convert or export any excel files to any other format. I've used just about every gp tool there is: TableToTable, TableTodBase, TableToGeodatabase, CopyRows. They all give me the same error: "input data does not exist or is not supported"

Have you run into this problem?

Re: Problem with xlsx file conversion and creation of XY event layer

KevinHibma — Fri, 08 Mar 2013 15:49:27 GMT

Sounds like you might be using 64bit Background Processing.
Excel as an input data type isn't supported there.

If you have it installed, I'd give a quick read of http://resources.arcgis.com/en/help/main/10.1/#/Background_Geoprocessing_64_bit/002100000040000000/