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

[AmyRoust]

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

[JoshuaBixby]

Does this work?

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

[AmyRoust · Accepted solution]

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.

[JoeBorgione]

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

[AmyRoust]

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.

[JoeBorgione]

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.   

[JoshuaBixby]

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.

[AmyRoust]

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.