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

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

AmyRoust — Sat, 11 Dec 2021 16:46:13 GMT

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

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

JoshuaBixby — Tue, 15 Sep 2020 21:16:54 GMT

Does this work?

defQuery = "Specialty_Code IS NOT NULL and Specialty_Code <> '   ' "‍

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

AmyRoust — Wed, 16 Sep 2020 13:01:21 GMT

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.

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

JoeBorgione — Wed, 16 Sep 2020 13:28:12 GMT

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

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

AmyRoust — Wed, 16 Sep 2020 14:11:00 GMT

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.

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

JoeBorgione — Wed, 16 Sep 2020 14:23:54 GMT

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.

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

JoshuaBixby — Wed, 16 Sep 2020 14:27:08 GMT

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.

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

AmyRoust — Wed, 16 Sep 2020 14:54:04 GMT

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.