ArcGIS 10.5.1 Python 2.7, enterprise geodatabse, SQL server back end Using an arcpy.da.SearchCursor() how might I set up a where statement that looks for '\n' newline special character? Here is one of my iterations that does not work: fields = [ 'NOTES' ] where = '{} like %\n%' . format ( fields [ 0 ] ) with arcpy . da . SearchCursor ( "pweng.SLCOEN.BacteriaBatch" , fields , where ) as cursor : . . . for row in cursor : . . . print row [ 0 ] . . . Runtime error Traceback ( most recent call last ) : File " " , line 2 , in module > RuntimeError : Underlying DBMS error [ [ Microsoft ] [ ODBC Driver 13 for SQL Server ] [ SQL Server ] Incorrect syntax near ')' . ] [ pweng . SLCOEN . BacteriaBatch ] I've tried a few different iterations in the 'Select by Attributes' dialog in ArcMap, but haven't come across on that will return any records. I had one record selected that I know has '\n' in the field, and it shows up nicely: with arcpy.da.SearchCursor("pweng.SLCOEN.BacteriaBatch",fields) as cursor: ... for row in cursor: ... print row ... (u'Intensive\nFB-7:18',) '''and''' with arcpy.da.SearchCursor("pweng.SLCOEN.BacteriaBatch",fields) as cursor: ... for row in cursor: ... print row[0] ... Intensive FB-7:18 This stems from a request associated with this post: https://community.esri.com/message/829382-re-when-a-return-is-used-in-a-text-field-in-survey123-the-subsequent-multi-lin…

I think I cracked this nut: instead of worrying about a 'where' clause per se, I just plow through the records with an update cursor and look for the problems with that: import arcpy arcpy . env . workspace = r 'J:\WaterQuality\test_tables.gdb' table = "BacteriaBatch" fields = [ 'OBJECTID' , 'NOTES' ] with arcpy . da . UpdateCursor ( table , fields ) as updateRows : for updateRow in updateRows : if updateRow [ 1 ] == None : pass elif "\n" in updateRow [ 1 ] : updateRow [ 1 ] = updateRow [ 1 ] . replace ( "\n" , "-" ) updateRows . updateRow ( updateRow )

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; } } }

Where clause for '\n'

JoeBorgione

ArcGIS 10.5.1 Python 2.7, enterprise geodatabse, SQL server back end

Using an arcpy.da.SearchCursor() how might I set up a where statement that looks for '\n' newline special character? Here is one of my iterations that does not work:

fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'NOTES'</SPAN><SPAN class="punctuation token">]</SPAN>
where <SPAN class="operator token">=</SPAN> <SPAN class="string token">'{} like %\n%'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>fields<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"pweng.SLCOEN.BacteriaBatch"</SPAN><SPAN class="punctuation token">,</SPAN>fields<SPAN class="punctuation token">,</SPAN>where<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>     <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>         <SPAN class="keyword token">print</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>         
Runtime error 
Traceback <SPAN class="punctuation token">(</SPAN>most recent call last<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
  File <SPAN class="string token">"<string>"</SPAN><SPAN class="punctuation token">,</SPAN> line <SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="keyword token">in</SPAN> <SPAN class="operator token"><</SPAN>module<SPAN class="operator token">></SPAN>
RuntimeError<SPAN class="punctuation token">:</SPAN> Underlying DBMS error <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">[</SPAN>Microsoft<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">[</SPAN>ODBC Driver <SPAN class="number token">13</SPAN> <SPAN class="keyword token">for</SPAN> SQL Server<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">[</SPAN>SQL Server<SPAN class="punctuation token">]</SPAN>Incorrect syntax near <SPAN class="string token">')'</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">[</SPAN>pweng<SPAN class="punctuation token">.</SPAN>SLCOEN<SPAN class="punctuation token">.</SPAN>BacteriaBatch<SPAN class="punctuation token">]</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

I've tried a few different iterations in the 'Select by Attributes' dialog in ArcMap, but haven't come across on that will return any records. I had one record selected that I know has '\n' in the field, and it shows up nicely:

with arcpy.da.SearchCursor("pweng.SLCOEN.BacteriaBatch",fields) as cursor:
...     for row in cursor:
...         print row
...         
(u'Intensive\nFB-7:18',)

'''and'''

with arcpy.da.SearchCursor("pweng.SLCOEN.BacteriaBatch",fields) as cursor:
...     for row in cursor:
...         print row[0]
...         
Intensive
FB-7:18
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

This stems from a request associated with this post: https://community.esri.com/message/829382-re-when-a-return-is-used-in-a-text-field-in-survey123-the-subsequent-multi-lin…

Encontre mais publicações marcadas com

Accepted answers

All comments

JoeBorgione

In the years of my mis-spent youth, I worked as a diesel truck mechanic. That experience taught me to approach problem solving pragmatically. It also taught me that sometimes you just gotta use a bigger hammer....

DanPatterson_Retired

better solution... step-wise... it has 'wise' in it

JoeBorgione

I think I cracked this nut: instead of worrying about a 'where' clause per se, I just plow through the records with an update cursor and look for the problems with that:

<SPAN class="keyword token">import</SPAN> arcpy
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'J:\WaterQuality\test_tables.gdb'</SPAN>
table <SPAN class="operator token">=</SPAN> <SPAN class="string token">"BacteriaBatch"</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'OBJECTID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'NOTES'</SPAN><SPAN class="punctuation token">]</SPAN>           
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN>fields<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> updateRows<SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">for</SPAN> updateRow <SPAN class="keyword token">in</SPAN> updateRows<SPAN class="punctuation token">:</SPAN>
        <SPAN class="keyword token">if</SPAN> updateRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="operator token">==</SPAN> None<SPAN class="punctuation token">:</SPAN>
            <SPAN class="keyword token">pass</SPAN>
        <SPAN class="keyword token">elif</SPAN> <SPAN class="string token">"\n"</SPAN> <SPAN class="keyword token">in</SPAN> updateRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN>
            updateRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> updateRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>replace<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"\n"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"-"</SPAN><SPAN class="punctuation token">)</SPAN>
            updateRows<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>updateRow<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

JoeBorgione

LOL...

I've got a feeling this is going to be a challenge; the data is an enterprise geodatabase service edited in Survey 123 on an Apple iPad.... hahahahaha!

DanPatterson_Retired

Newline - Wikipedia

some on languages etc and some conversions

thankfully there is only one None

JoeBorgione

Cool. Thanks. I'll give it a go in the morning!

Joshua Bixby‌

JoshuaBixby

Different languages use different characters for new lines and carriage returns. How to check my data in SQL Server have carriage return and line feed? - Stack Overflow

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Todas as Comunidades
Publicações Recentes
Conteúdo Seguido
Ajuda da Comunidade

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