1

I am using ArcMap 10.2.2 and trying to make separate feature classes from a larger feature class using a field delimiter and SQL statement but am getting an error that the SQL statement is invalid. Here is the snippet of code I'm having a problem with:

# This script looks at a featureclass(FC)/service location(SL)
# and splits it by day for web service data preparation.
# In this manner, end users can toggle off and on days of service.


#import modules
import arcpy
import os

#create input FC/SL -- the mega FC/SL
inputFC = raw_input("Paste Route FC from ArcCatalog: ")

#set default geodatabase
arcpy.env.workspace = raw_input("Paste default file GDB from ArcCatalog: ")

#designate output location as a variable so it can be used in system path
#output filename
outLocation = arcpy.env.workspace

# Designate the type of fleet, service, commodity etc
fleetType = raw_input("Name of output (e.g. Residential_Garbage, NO SPACES!!!): ")

#make a copy of the input FC so nothing gets corrupted
arcpy.MakeFeatureLayer_management(inputFC, "Copy_inputFC")

dayList = []
while True:
    day = raw_input("Type field delimiter and press enter. Type and enter 'Done' when finished: ")
    if day.lower() == 'done':
        break
    dayList.append(day)

#Make multiple FCs based on route number
for day in dayList:
    #Add field delimitters
    delimFld = arcpy.AddFieldDelimiters("Copy_inputFC", "Exist_Partition")
    print "step1"
    if day == '1':
        dayName = 'Monday'
    elif day == '2':
        dayName = 'Tuesday'
    elif day == '3':
        dayName = 'Wednesday'
    elif day == '4':
        dayName = 'Thursday'
    else:
        dayName = 'Friday'

    #Get output name
    outName = "{0}{1}{2}".format(fleetType,"_",dayName)
    outFC = os.path.join(outLocation, outName)
    print "step2", outFC

    #If routes are numbers:
    try:
        sql = "{0}={1}".format(delimFld, day)
        arcpy.Select_analysis("Copy_inputFC", outFC, sql)

    #If routes are strings
    except:
        sql = "{0} = '{1}'".format(delimFld, day)
        arcpy.Select_analysis("Copy_inputFC", outFC, sql)

    print outName


print "Finished"

This is what the error is saying:

ExecuteError: ERROR 000358: Invalid expression "Exist_Partition" = '1'
Failed to execute (Select).

However, if I do the SQL statement in ArcMap it appears to be fine:

enter image description here

  • Is your Exist_Partition field numeric or text? – Michael Stimson Mar 27 '17 at 23:17
  • 1
    Have you reviewed questions tagged error-000358? – PolyGeo Mar 27 '17 at 23:26
  • 1
    "Exist_Partition" = '1' should be "Exist_Partition" = 'Monday' based on your code, perhaps it should be dayList.append(str(day)). I am assuming "Copy_inputFC" is a layer and Exist_Partition is a field (text) that exists in the layer... Can you include the line where the layer is made please. – Michael Stimson Mar 27 '17 at 23:30
  • @MichaelMiles-Stimson I don't think that's quite right. The code clearly says to use 'day' not 'dayName'. The latter is used for the output name. Not the query, which prints out in the error message: ExecuteError: ERROR 000358: Invalid expression "Exist_Partition" = '1'. I'm making the Feature layer with this: arcpy.MakeFeatureLayer_management(inputFC, "Copy_inputFC") – geoJshaun Mar 28 '17 at 0:24
  • 1
    @ShaunO Please edit your question to include additional information/clarification – Midavalo Mar 28 '17 at 0:26
1

My guess is that Exist_Partition doesn't actually exist in your layer.

From your screenshot it appears that your layer datasource is a Shapefile (I'm basing this on the presence of a FID field here). A Shapefile has a maximum field length of 10 characters, however Exist_Partition has 15 characters. When I tried to create that field in my Shapefile I was told it would be shortened to Exist_Part - 10 characters long. The arcpy.AddFieldDelimiters() is also putting double quotes " " around the field name which it usually does on Shapefiles.

With my shapefile and Exist_Part field, I recreated your error message by using a trimmed down version of your code:

>>> delimFld = arcpy.AddFieldDelimiters("testpoints", "Exist_Partition")
>>> print delimFld
"Exist_Partition"
>>> day = "1"
>>> sql = "{0} = '{1}'".format(delimFld, day)
>>> print sql
"Exist_Partition" = '1'
>>> arcpy.env.workspace = 'in_memory'
>>> outLocation = arcpy.env.workspace
>>> outName = "{0}{1}{2}".format("tester","_","Monday")
>>> outFC = os.path.join(outLocation, outName)
>>> arcpy.Select_analysis("testpoints", outFC, sql)

Start Time: Mon Mar 27 18:10:15 2017
ERROR 000358: Invalid expression "Exist_Partition" = '1'
Failed to execute (Select).
Failed at Mon Mar 27 18:10:15 2017 (Elapsed Time: 0.01 seconds) 

>>> 

The expression is Invalid because the field Exist_Partition does not and can not exist in a shapefile.

This doesn't explain why it works when you enter it into the Select by Attributes window though - this does seem odd to me. The screenshot doesn't show double-quotes " " around the field names in the Select by Attributes window though, which mine does on my shapefile.

  • I suspect that a shorter field name (like your Exist_Part) in the shapefile has an alias of Exist_Partition in the layer and map. That way the Select_analysis on the shapefile is using a long field name that does not exist while the Select By Attributes in the map is using it as an alias instead, and so works. – PolyGeo Mar 28 '17 at 1:32
  • @PolyGeo I had thought that, however the select by attributes didn't accept it in longer alias form when I tried it – Midavalo Mar 28 '17 at 1:38
  • That's curious. I think the best way forward may be for the asker to try and reduce the size of his code snippet while keeping the error, and then see what extra step removes the error. – PolyGeo Mar 28 '17 at 1:46
  • @Midavalo It's a featureclass, not a shapefile, however is it possible that the field name is truncated when converted to a feature layer? That is what the select analysis is being performed on. I'll add a print statement to print field names. – geoJshaun Mar 28 '17 at 16:06
  • @ShaunO what type of feature class? Is it in a geodatabase of some sort? What kind? Usually the AddFieldDelimiters only adds the quote marks onto fields in shapefiles, not geodatabases. Try removing the field delimiters and force the sql to be sql = "Exist_Partition = '1' " with no quotes around the field name. – Midavalo Mar 28 '17 at 16:10
1

I ran the script again this morning and it worked perfectly. I added arcpy.env.overwriteOutput = Trueafter setting the workspace with the user prompt. I took it out for testing and was receiving the error with it included so I'm confident it's unrelated to the problem. Otherwise it's exactly the same as my original post.

What was causing it was field mapping issues on my citrix VDI. The program either reads the directory where the data is stored as C:\RS_Data\Workspace or N:\Workspace. They are actually the same location depending on if you navigate through ArcMap or Windows Explorer, respectively.

This drive lives on a server with ArcMap and all of it's associated programs installed on it. Everything else I use is on a citrix server which I access through VDI. The latter acts as my main desktop and I access ArcMap desktop through it, reaching a desktop through another desktop. A virtual one at that. In certain transactions Python can't find certain files because the path names get mixed up.

In short, the problem was solved by terminating my session and logging back on which remapped the drives and allowed the program to find the feature layer.

I apologize for the long schpeil but I hope this will help others that encounter issues using ArcMap on VDI. I also added a virtual machine tag to the post. Thanks to everyone who helped me troubleshoot this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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