Unable to set a where clause using <> ' ' in a Python 3.x script

506
7
Jump to solution
09-15-2020 12:55 PM
AmyRoust
Occasional Contributor III

Totally stumped. I have a few records in a table that I imported into a file geodatabase from Excel that have three blank spaces in a text field called Specialty_Code instead of the required three character code. I want my script to skip those records in the analysis, so I'm creating a table view that excludes those records.

Using Python 3.x, why does this work (line 3)....

    allRecords = defaultGDB + '\\CleanTable_' + fileName
outLayer = 'CleanTable_' + fileName + '_View'
defQuery = "Specialty_Code IS NOT NULL"
print(defQuery)
readyToAppend = arcpy.MakeTableView_management(allRecords, outLayer, defQuery)‍‍‍‍‍‍‍‍‍‍

 

... but this doesn't (line 3)?

    allRecords = defaultGDB + '\\CleanTable_' + fileName
outLayer = 'CleanTable_' + fileName + '_View'
defQuery = "Specialty_Code IS NOT NULL and Specialty_Code <> \' \'"
print(defQuery)
readyToAppend = arcpy.MakeTableView_management(allRecords, outLayer, defQuery)‍‍‍‍‍‍‍‍‍‍

Error message is arcgisscripting.ExecuteError: ERROR 000358: Invalid expression

0 Kudos
1 Solution

Accepted Solutions
AmyRoust
Occasional Contributor III

No, that didn't work either. After further digging, the problem became more complicated. Despite the fact that there are records with 3 spaces in them, that column imported from Excel into my FGDB as a double by default (most of the records are three-digit numbers, many with leading zeros). That would explain why a text-based query failed to work -- it wasn't a text field. I ended up querying out those records earlier in the script, before they imported into the FGDB. Problem solved, but it does leave me wondering if there is any programmatic solution to remove blank spaces that are in a double field. I tried the steps in Model Builder (using the text string that failed in my script) and they worked fine.

View solution in original post

0 Kudos
7 Replies
JoshuaBixby
MVP Esteemed Contributor

Does this work?

defQuery = "Specialty_Code IS NOT NULL and Specialty_Code <> '   ' "
0 Kudos
AmyRoust
Occasional Contributor III

No, that didn't work either. After further digging, the problem became more complicated. Despite the fact that there are records with 3 spaces in them, that column imported from Excel into my FGDB as a double by default (most of the records are three-digit numbers, many with leading zeros). That would explain why a text-based query failed to work -- it wasn't a text field. I ended up querying out those records earlier in the script, before they imported into the FGDB. Problem solved, but it does leave me wondering if there is any programmatic solution to remove blank spaces that are in a double field. I tried the steps in Model Builder (using the text string that failed in my script) and they worked fine.

View solution in original post

0 Kudos
JoeBorgione
MVP Esteemed Contributor

An after the fact qestion; if the field is a double, how did it have three spaces and not a <Null> value?

can't wait to retire....
0 Kudos
AmyRoust
Occasional Contributor III

That is an excellent question, Joe, and one for which I do not have an answer. Definitely falls into the "that shouldn't happen, but it did anyway" category.

0 Kudos
JoeBorgione
MVP Esteemed Contributor

Very weird!  Glad you were able to overcome it none the less.  Excel always seems to bring new and exciting challenges when working with them in ArcGIS.   

can't wait to retire....
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

It is impossible to have text in a numeric data field in ArcGIS.  When you say, "imported from Excel into my FGDB as a double by default ," did you actually confirm the field is double?  If the field is in fact numeric, then you don't have spaces in it.

0 Kudos
AmyRoust
Occasional Contributor III

Yes, I did confirm that it came in as a double. I wouldn't have believed it if I hadn't seen it with my own eyes because you are 100% right that it's impossible. I wish I still had the table so that I could screenshot it and post, but it would take more time than I'm willing to spend to undo the corrections I've written into the script to recreate the issue. 

0 Kudos