SImple model works, resulting script doesn't

Question

The goal is to convert an XY layer (from Excel) into a feature class. The "MakeXY" portion works flawlessly, the arcpy.FeatureClassToFeatureClass_conversion portion inevitably returns a 999999 "Table was not found" message. I can build a model and run it and it all works. The exported script, recycled through the Python window, also fails on a 999999. I figure it's got to be an acrpy.env.workspace issue (but may be wrong) and so have added that line to the exported script. Still get the 999999. So what's this newbie doing wrong?

import arcpy
arcpy.env.workspace = r"C:\\Temp\\MuleDeer\\AllKLM5.gdb"

# Local variables:
X12XY = "X12XY" 
AllKLM5_gdb = "C:\\Temp\\MuleDeer\\AllKLM5.gdb"

# Process: Feature Class to Feature Class
arcpy.FeatureClassToFeatureClass_conversion(X12XY, AllKLM5_gdb, "CDM_9c_FC", "", "Freq \"Freq\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Freq,-1,-1;Date \"Date\" true true false 8 Date 0 0 ,First,#,SOB$_Features,Date,-1,-1;Time \"Time\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Time,-1,-1;TTF \"TTF\" true true false 8 Double 6 15 ,First,#,SOB$_Features,TTF,-1,-1;Northing \"Northing\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Northing,-1,-1;Easting \"Easting\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Easting,-1,-1;Zone \"Zone\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Zone,-1,-1;Altitude \"Altitude\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Altitude,-1,-1;Maxsnr \"Maxsnr\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Maxsnr,-1,-1;Hdop \"Hdop\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Hdop,-1,-1;Vdop \"Vdop\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Vdop,-1,-1;Satt \"Satt\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Satt,-1,-1;Fix \"Fix\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Fix,-1,-1;V1 \"V1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,V1,-1,-1;V2 \"V2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,V2,-1,-1;T1 \"T1\" true true false 8 Double 6 15 ,First,#,SOB$_Features,T1,-1,-1;T2 \"T2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,T2,-1,-1;H1 \"H1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,H1,-1,-1;H2 \"H2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,H2,-1,-1;S1 \"S1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S1,-1,-1;S2 \"S2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S2,-1,-1;S3 \"S3\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S3,-1,-1;S4 \"S4\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S4,-1,-1", "")

(The fuller script is a bit more elegant in its use of variables, etc. but fails in the same place with the same error.)

= T =

P.S. Get Spatial, here's the current script which fails as is. The larger script which creates the XY Layer is also available if you want me to post it but it's a pretty plain vanilla "arcpy.MakeXYEventLayer_management" call. I continue to wonder if the "X12XY" reference incomplete. I've also tried a string with contents like "C:\Temp\CDM\X12.xlsx" and "C:\Temp\CDM\x12.xlsx\X12XY" with no success.

arcpy.env.workspace = r"C:\Temp\MuleDeer"

# Local variables:
X12XY = r"X12XY"

AllKLM5_gdb = r"C:\Temp\MuleDeer\AllKLM5.gdb"

# Process: Feature Class to Feature Class
arcpy.FeatureClassToFeatureClass_conversion(X12XY, AllKLM5_gdb, "CDM_9c_FC", "", "Freq \"Freq\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Freq,-1,-1;Date \"Date\" true true false 8 Date 0 0 ,First,#,SOB$_Features,Date,-1,-1;Time \"Time\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Time,-1,-1;TTF \"TTF\" true true false 8 Double 6 15 ,First,#,SOB$_Features,TTF,-1,-1;Northing \"Northing\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Northing,-1,-1;Easting \"Easting\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Easting,-1,-1;Zone \"Zone\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Zone,-1,-1;Altitude \"Altitude\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Altitude,-1,-1;Maxsnr \"Maxsnr\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Maxsnr,-1,-1;Hdop \"Hdop\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Hdop,-1,-1;Vdop \"Vdop\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Vdop,-1,-1;Satt \"Satt\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Satt,-1,-1;Fix \"Fix\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Fix,-1,-1;V1 \"V1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,V1,-1,-1;V2 \"V2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,V2,-1,-1;T1 \"T1\" true true false 8 Double 6 15 ,First,#,SOB$_Features,T1,-1,-1;T2 \"T2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,T2,-1,-1;H1 \"H1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,H1,-1,-1;H2 \"H2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,H2,-1,-1;S1 \"S1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S1,-1,-1;S2 \"S2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S2,-1,-1;S3 \"S3\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S3,-1,-1;S4 \"S4\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S4,-1,-1", "")

P.P.S Here's the script creating the X12XY object. Works first time, every time. The eventual goal is to batch-process a set of (identical) Excel files into feature classes. The only workbook in the target directory is named XL12.xlsx; the worksheet of interest is labeled "SOB" (Note: The indentation is off as I had to tweak each line manually after pasting it here).

import arcpy, os, sys, traceback

arcpy.env.workspace = r"C:\Temp\CDM"
outPath = r"C:\TEMP\CDM"
arcpy.env.overwriteOutput = True

# Local variables
sWorksheetName = "SOB$"
sProjection = "PROJCS['NAD_1983_UTM_Zone_10N',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Transverse_Mercator'],PARAMETER['false_easting',500000.0],PARAMETER['false_northing',0.0],PARAMETER['central_meridian',-123.0],PARAMETER['scale_factor',0.9996],PARAMETER['latitude_of_origin',0.0],UNIT['Meter',1.0]];-5120900 -9998100 10000;-100000 10000;-100000 10000;0.001;0.001;0.001;IsHighPrecision"

# 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 fileT in fileList:
    fileName = os.path.splitext(fileT)[0]
    sDest = fileName  + "XY"
    sSrc = fileT + "\\" + sWorksheetName
    print fileT + " : " + fileName + " : " + sSrc
      # Toolbox / Data mgt / Layers and Table vuews" 
    arcpy.MakeXYEventLayer_management(sSrc, "Easting", "Northing", sDest, sProjection, "")


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)

P.P.P.S

Spatial guy, I get the concept of the MakeXY table existing only in memory. Less clear on the datatype issue (love non-typed languages - NOT!). The following code puts it all within the "for" loop yet still fails -- but perhaps for a new reason. The Python output is: Listing .xlsx files in workspace

    X12.xlsx : X12 : X12.xlsx\SOB$

    Reality check: C:\Temp\MuleDeer\AllKLM5.gdb is there

    X12XY is there 

    About to call FeatureClassToFeatureClass_conversion()

    ARCPY ERRORS:
    ERROR 999999: Error executing function.
    The table was not found. [X12FC]
    Failed to execute (FeatureClassToFeatureClass).


    PYTHON ERRORS:
    Traceback Info:
      File "<string>", line 42, in <module>

    Error Info:
         <class 'arcgisscripting.ExecuteError'>: ERROR 999999: Error executing function.
    The table was not found. [X12FC]
    Failed to execute (FeatureClassToFeatureClass).

I'm assuming (dangerous move) that if output feature class does not already exist the FeatureClassToFeatureClass() will create it. That's sort of the point, isn't it? So am I now battling a syntax error of some sort or just fundamentally unclear about FeatureClassToFeatureClass?

Finally for the night, this system seems happy with the "str(sys.exc_value)" line. Don't ask, don't tell, just keeping my fingers crossed.

import arcpy, os, sys, traceback

arcpy.env.workspace = r"C:\Temp\CDM"
outPath = r"C:\TEMP\CDM"
arcpy.env.overwriteOutput = True

# Local variables

sXColName = "Easting"
sYColName = "Northing"
sWorksheetName = "SOB$"
sProjection = "PROJCS['NAD_1983_UTM_Zone_10N',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Transverse_Mercator'],PARAMETER['false_easting',500000.0],PARAMETER['false_northing',0.0],PARAMETER['central_meridian',-123.0],PARAMETER['scale_factor',0.9996],PARAMETER['latitude_of_origin',0.0],UNIT['Meter',1.0]];-5120900 -9998100 10000;-100000 10000;-100000 10000;0.001;0.001;0.001;IsHighPrecision"
sXLSMapping = "Freq \"Freq\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Freq,-1,-1;Date \"Date\" true true false 8 Date 0 0 ,First,#,SOB$_Features,Date,-1,-1;Time \"Time\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Time,-1,-1;TTF \"TTF\" true true false 8 Double 6 15 ,First,#,SOB$_Features,TTF,-1,-1;Northing \"Northing\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Northing,-1,-1;Easting \"Easting\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Easting,-1,-1;Zone \"Zone\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Zone,-1,-1;Altitude \"Altitude\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Altitude,-1,-1;Maxsnr \"Maxsnr\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Maxsnr,-1,-1;Hdop \"Hdop\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Hdop,-1,-1;Vdop \"Vdop\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Vdop,-1,-1;Satt \"Satt\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Satt,-1,-1;Fix \"Fix\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Fix,-1,-1;V1 \"V1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,V1,-1,-1;V2 \"V2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,V2,-1,-1;T1 \"T1\" true true false 8 Double 6 15 ,First,#,SOB$_Features,T1,-1,-1;T2 \"T2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,T2,-1,-1;H1 \"H1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,H1,-1,-1;H2 \"H2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,H2,-1,-1;S1 \"S1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S1,-1,-1;S2 \"S2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S2,-1,-1;S3 \"S3\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S3,-1,-1;S4 \"S4\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S4,-1,-1"
sMasterGDB = "C:\\Temp\\MuleDeer\\AllKLM5.gdb"
# 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 fileT in fileList:
        sFileName = os.path.splitext(fileT)[0]
        sDestXY = sFileName + "XY"
        sDestFC = sFileName + "FC"
        sSrc = fileT + "\\" + sWorksheetName
        print fileT + " : " + sFileName + " : " + sSrc

        # out with the old (if any)
        if arcpy.Exists(sDestXY):
            arcpy.Delete_management(sDestXY)

        # out with the old (if any)
        if arcpy.Exists(sDestFC):
            arcpy.Delete_management(sDestFC)

        if arcpy.Exists(sMasterGDB):
            print 'Reality check: ' + sMasterGDB + " is there"

        # Toolbox / Data mgt / Layers and Table views" 
        arcpy.MakeXYEventLayer_management(sSrc, sXColName, sYColName, sDestXY, sProjection, "")

        # Reality check
        if arcpy.Exists(sDestXY):
            print sDestXY + " is there "


        print "About to call FeatureClassToFeatureClass_conversion()"
        # Toolbox / Conversion / To Geodatabase
        arcpy.FeatureClassToFeatureClass_conversion(sDestXY, sMasterGDB, sDestFC, "", sXLSMapping, "")

        del sXColName, sYColName, sWorksheetName, sProjection, sXLSMapping, sMasterGDB
        del fileT, sFileName,  sDestXY, sDestFC, sSrc

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)

Answer 1

Based on the code you have copied, it looks like your error is arising from how you have entered the file path for your arcpy.env.workspace variable.

Here is what you entered

arcpy.env.workspace = r"C:\\Temp\\MuleDeer\\AllKLM5.gdb"

For comparison, here is what you entered for the local variable gdb location:

AllKLM5_gdb = "C:\\Temp\\MuleDeer\\AllKLM5.gdb"

In both situations, you have doubled the backslash \\ which escapes it to avoid errors in the script. This would normally be all you need to do, except in the first instance, you also added the r in front of the string, which treats it as a raw string, and performs no conversion of those escape characters.

This means that instead of reading the string as a valid file path,
"C:\Temp\MuleDeer\AllKLMS.gdb"

It is reading it exactly as you entered it above
"C:\\Temp\\MuleDeer\\AllKLMS.gdb"
which is not a valid file path.

Basically, you need to use either the double backslash \\ to escape the directory separators, or use the r to read it as a raw string, but not both at the same time.

-- Hat tip to @Roy for a very relevant link: Setting paths to data in Python

--EDIT - Based on additional information in Question

As it turns out, the information included above, was probably part of the problem, but is secondary to the larger issue, which is the handling and source of the X12XY Variable.

In the original script segment, you are filling that variable with a string of the same name. You are then passing it to the arcpy.FeatureClassToFeatureClass_conversion function. The reason it was throwing an error is because the input to this function is a FeatureLayer object, as opposed to a string.

Following is a code sample that shows how to correctly call the FeatureClassToFeatureClass_conversion function. It takes the Layer name that you designated as the output of the MakeXYEventLayer_management function, and passes that as the input FeatureLayer.

import arcpy, os, sys, traceback

arcpy.env.workspace = r"C:\Temp\CDM"
outPath = r"C:\TEMP\CDM"
arcpy.env.overwriteOutput = True

# Local variables
sWorksheetName = "SOB$"
sProjection = "PROJCS['NAD_1983_UTM_Zone_10N',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Transverse_Mercator'],PARAMETER['false_easting',500000.0],PARAMETER['false_northing',0.0],PARAMETER['central_meridian',-123.0],PARAMETER['scale_factor',0.9996],PARAMETER['latitude_of_origin',0.0],UNIT['Meter',1.0]];-5120900 -9998100 10000;-100000 10000;-100000 10000;0.001;0.001;0.001;IsHighPrecision"

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

#Set Output Geodatabase Variable
    AllKLM5_gdb = r"C:\Temp\MuleDeer\AllKLM5.gdb"


# Loop through each file
    for fileT in fileList:
        fileName = os.path.splitext(fileT)[0]
        sDest = fileName  + "XY"
        sSrc = fileT + "\\" + sWorksheetName
        print fileT + " : " + fileName + " : " + sSrc
        # Toolbox / Data mgt / Layers and Table vuews" 
        arcpy.MakeXYEventLayer_management(sSrc, "Easting", "Northing", sDest, sProjection, "")

        # Process: Feature Class to Feature Class
        arcpy.FeatureClassToFeatureClass_conversion(sDest, AllKLM5_gdb, "CDM_9c_FC", "", "Freq \"Freq\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Freq,-1,-1;Date \"Date\" true true false 8 Date 0 0 ,First,#,SOB$_Features,Date,-1,-1;Time \"Time\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Time,-1,-1;TTF \"TTF\" true true false 8 Double 6 15 ,First,#,SOB$_Features,TTF,-1,-1;Northing \"Northing\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Northing,-1,-1;Easting \"Easting\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Easting,-1,-1;Zone \"Zone\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Zone,-1,-1;Altitude \"Altitude\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Altitude,-1,-1;Maxsnr \"Maxsnr\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Maxsnr,-1,-1;Hdop \"Hdop\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Hdop,-1,-1;Vdop \"Vdop\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Vdop,-1,-1;Satt \"Satt\" true true false 8 Double 6 15 ,First,#,SOB$_Features,Satt,-1,-1;Fix \"Fix\" true true false 255 Text 0 0 ,First,#,SOB$_Features,Fix,-1,-1;V1 \"V1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,V1,-1,-1;V2 \"V2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,V2,-1,-1;T1 \"T1\" true true false 8 Double 6 15 ,First,#,SOB$_Features,T1,-1,-1;T2 \"T2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,T2,-1,-1;H1 \"H1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,H1,-1,-1;H2 \"H2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,H2,-1,-1;S1 \"S1\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S1,-1,-1;S2 \"S2\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S2,-1,-1;S3 \"S3\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S3,-1,-1;S4 \"S4\" true true false 255 Text 0 0 ,First,#,SOB$_Features,S4,-1,-1", "")

When you create the XYEvent Layer, the output is a FeatureLayer, which is exactly what the FeatureClass conversion function needs as the input. This layer is held in memory. It does not exist on disk anywhere, which is why looking for it in the "MuleDeer" directory was throwing the Not Found error.

By placing the FeatureClass conversion within the For loop, it will ensure that each XYEvent layer is created and then immediately converted to a FeatureClass.

One last note about your script. In the except section, the call to sys.exc_value was coming up as being an invalid parameter. I'm not sure if this is just my installation being old or something, but you might want to watch for it.