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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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 <SPAN class="operator token">=</SPAN> defaultGDB <SPAN class="operator token">+</SPAN> <SPAN class="string token">'\\CleanTable_'</SPAN> <SPAN class="operator token">+</SPAN> fileName
    outLayer <SPAN class="operator token">=</SPAN> <SPAN class="string token">'CleanTable_'</SPAN> <SPAN class="operator token">+</SPAN> fileName <SPAN class="operator token">+</SPAN> <SPAN class="string token">'_View'</SPAN>
    defQuery <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Specialty_Code IS NOT NULL"</SPAN>
    <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>defQuery<SPAN class="punctuation token">)</SPAN>
    readyToAppend <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>MakeTableView_management<SPAN class="punctuation token">(</SPAN>allRecords<SPAN class="punctuation token">,</SPAN> outLayer<SPAN class="punctuation token">,</SPAN> defQuery<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

    allRecords <SPAN class="operator token">=</SPAN> defaultGDB <SPAN class="operator token">+</SPAN> <SPAN class="string token">'\\CleanTable_'</SPAN> <SPAN class="operator token">+</SPAN> fileName
    outLayer <SPAN class="operator token">=</SPAN> <SPAN class="string token">'CleanTable_'</SPAN> <SPAN class="operator token">+</SPAN> fileName <SPAN class="operator token">+</SPAN> <SPAN class="string token">'_View'</SPAN>
    defQuery <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Specialty_Code IS NOT NULL and Specialty_Code <> \'   \'"</SPAN>
    <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>defQuery<SPAN class="punctuation token">)</SPAN>
    readyToAppend <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>MakeTableView_management<SPAN class="punctuation token">(</SPAN>allRecords<SPAN class="punctuation token">,</SPAN> outLayer<SPAN class="punctuation token">,</SPAN> defQuery<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

Find more posts tagged with

Accepted answers

AmyRoust

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.

All comments

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.

bixb0012

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.

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.

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

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

AmyRoust

bixb0012

Does this work?

defQuery <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Specialty_Code IS NOT NULL and Specialty_Code <> '   ' "</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.