1

I have a list of aprox 150 unique road names in Excel. I copy and paste (as text option) that list into Word (2013 version). I then "replace" all the end paragraphs (^p) with quotes and commas (','). This gets the list to go from:

Cambie
Blanchard
Cook

to looking like

'Cambie','Blanchard','Cook'

Then within Arc's Select by Attribute I use this text in the query

Road_Name IN ('Cambie','Blanchard','Cook')

which gives me an SQL error. Any idea why I would be getting the error?

From trial and error I have come to realize that when I paste the text the quotes are a different length than if I had just typed them. See screen shot - the first road name's quotes (which I pasted) are shorter than the second road name's quotes (which I typed out in the query box).

enter image description here

When I query the one with the longer quotes by itself it works. Why would my pasted quotes be shorter? Any suggestions as to how I can query the 150 road names without having to re-type every quote?

  • 1
    Can you add a screenshot of the completed Select By Attributes window? – papadoo May 15 '15 at 20:07
  • Please see above – GIS-questions May 15 '15 at 20:59
1

The problem you have apparently run into here is character encoding. Particularly when moving between programs and fonts as your Excel > Word > Arc pasting does, and single quotes are one of the worst characters for this issue. ` (upper left standard keyboard) and ' (next to Enter standard keyboard) are two different characters. Likely your 'shorter' quotes map to a different character that isn't recognized by Arc as being the single quote. This could be a Unicode vs non Unicode font issue between the programs. In some contexts start quote/end quote are the same character, as are Grave Accent Acute Accent and Apostrophe; in others, they're all distinct. One possible workaround is to paste your text into Notepad and save it as a .txt file, then copy from that rather than the Word document. The .txt format usually strips out weird formatting and character issues, though not always. Even if it doesn't fix them, they can be more apparent in Notepad than other software with fonts applied.

Another potential issue is the characters needed to enclose a field name in a query vary depending on the format of the data you're querying (and your question doesn't specify, so I'm leaving this paragraph in for completeness/others). In a shapefile, it would be double quotes "Road_Name", while a feature class in a personal geodatabase would use brackets [Road_Name] and a feature class in a file geodatabase wouldn't use any enclosing characters Road_Name. This can also vary depending on where you're working with the field name (for example with Field Calculator and a Python syntax, it would be exclamation points/bangs !Road_Name!). If you add the field by double-clicking in the list rather than typing it, it should use the correct enclosing marks regardless of where you are though you may not have a choice to do so (as in the case of using a nested table for the query.

Using the Verify Expression button will sometimes indicate the specific problem, and if it isn't a generic error (invalid syntax), including the text of the error you get would be helpful in your question. Also, there is a limit to the number of items in a list you can have like that depending on the underlying database the info is stored in, but that shouldn't be happening here.


Alternative workflow without Word

If your features are in a feature class within a geodatabase (as opposed to a shapefile) you also don't need to do the whole Excel to Word and find/replace thing. Import your Excel spreadsheet to the geodatabase as a table (make sure the spreadsheet has a column heading, and for simplicity sake you may as well make it Road_Name - datatypes need to match too but in this case a string should be a string). If both the table and feature class are in the same geodatabase, you can use nested queries. So: "Road_Name" IN (SELECT "Road_Name" FROM excel_table_in_geodatabase).

  • When I double click to add "Road_Name" it adds it in the query box without the double quotes. Even when I type the double quotes around it it still gives me a query error. – GIS-questions May 15 '15 at 21:13
  • @GIS updated answer with more info. Forgot to include the character encoding paragraph when I originally answered for some reason, must have gotten distracted while typing. – Chris W May 15 '15 at 22:02

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.