End-of-line (EOL) Problem

NickJacob · ‎02-15-2012

Came across a weird EOL Error with Arcpy's CalculateField_management() function today and I was wondering if anyone out there could point me towards a work around?

I previewed one of my tables in Catalog and at first glance the offending cells seemed fine. However, when I copy/pasted one over to a text file a lot more text became visible. My current theory is that the Enter key is being hit when my customers edit cells in MS Excel (my typical source data) and invisible newline characters are being carried into my geodatabase tables. Tricky thing is I can't see them in Excel or Arc, so I'm not sure how to strip or replace the backslashes via Python.

Any advice would be much appreciated,
- Nick

curtvprice · ‎07-29-2012

Or is it just easier to use VB instead of Python for field calculations?

I believe (and I think many would agree) that Python has far superior string manipulation capabilities.

Here are some pretty good Python methods for stripping non printables:

http://stackoverflow.com/questions/92438/stripping-non-printable-characters-from-a-string-in-python
http://stackoverflow.com/questions/1276764/stripping-everything-but-alphanumeric-chars-from-a-string...

Janvan_Linge · ‎11-07-2012

3) Nick's hexidecimal conversion seems to work for \n but, again, how can we apply it to other special characters? Or is it just easier to use VB instead of Python for field calculations?

I ran into the same problem when using python to add hyperlinks. As hyperlinks contain "\" characters it sometimes happened that a "\n" was in the hyperlink. I solved it by passing in the hyperlink as a raw string instead of a normal string:

hyperlink = "c:\somehyperlink\name_of_file"
arcpy.CalculateField_management(TableToEdit, "HYPERLINK_FIELD", r"r'" + hyperlink + r"'", "PYTHON")

curtvprice · ‎11-07-2012

I ran into the same problem when using python to add hyperlinks. As hyperlinks contain "\" characters it sometimes happened that a "\n" was in the hyperlink. I solved it by passing in the hyperlink as a raw string instead of a normal string:
hyperlink = "c:\somehyperlink\name_of_file"
arcpy.CalculateField_management(TableToEdit, "HYPERLINK_FIELD", r"r'" + hyperlink + r"'", "PYTHON")

The above code embeds a newline (\n) in the string:

>>> print "c:\somehyperlink\name_of_file"
c:\somehyperlink
ame_of_file

I think this may work better:

>>> print 'r"{0}"'.format(r"c:\somehyperlink\name_of_file")
r"c:\somehyperlink\name_of_file"

hyperlink = r"c:\somehyperlink\name_of_file"
arcpy.CalculateField_management(TableToEdit, "HYPERLINK_FIELD", '{0}"'.format(hyperlink), "PYTHON")

I thought I'd add one more thing to this post: how to specify newlines in the Calculate Field tool in ModelBuilder. The interactive tool dialog parser converts "\n" to real newlines in the code box, which doesn't work, so the workaround is to to use chr(10). I've used this as a quick and dirty way to have model builder print a message:

Expression: msg()

def msg():
  # text = "\n\nThis is\na message to you.\n"  # does not work
  text = "{0}{0}This is{0}a message to you.{0}".format(chr(10))
  return text

MarcNakleh · ‎12-06-2012

I do not believe that this problem has been sufficiently adressed.

Using the Field Calculator box, with text containing a CR-LF (Characters 10-13), I have tested all of the following:

Using a replace ('\r\n', '')

Using strip

Using filter

Using a comprehension that decompiles and checks individual characters

Even using a Codeblock, these tactics did not seem to work.

However, if I go from the console and set up something like:

rows = arcpy.UpdateCursor(fc)
for row in rows:
    if '\r\n' in row.TextString:
         row.setValue('TextString', row.TextString.replace('\r\n', ' '))
         rows.updateRow(row)
    del row
del rows

It works exactly as one would expect. But I would love to know more about why this doesn't seem to work from the Field Calculator window.

IB1 · ‎04-10-2013

Yeah, still not working.

curtvprice · ‎04-10-2013

However, if I go from the console and set up something like:
rows = arcpy.UpdateCursor(fc)
for row in rows:
    if '\r\n' in row.TextString:
         row.setValue('TextString', row.TextString.replace('\r\n', ' '))
         rows.updateRow(row)
del row, rows
It works exactly as one would expect. But I would love to know more about why this doesn't seem to work from the Field Calculator window.

The problem is that you cannot use Python escape codes like "\r" in the Field Calculator code block or the Calculate Value code block. I'm assuming this has something to do with the parsing of python arguments into string representation in the arcpy/gp messaging framework.

If you need to access escape characters, use the chr() function instead.

This will probably work fine:

rows = arcpy.UpdateCursor(fc)
for row in rows:
    newline = chr(13) + chr(10)
    if newline in row.TextString:
         row.setValue('TextString', row.TextString.replace(newline, ' '))
         rows.updateRow(row)
del row, rows

IanBroad2 · ‎04-10-2013

The problem is that you cannot use Python escape codes like "\r" in the Field Calculator code block or the Calculate Value code block. I'm assuming this has something to do with the parsing of python arguments into string representation in the arcpy/gp messaging framework.

If you need to access escape characters, use the chr() function instead.

This will probably work fine:
rows = arcpy.UpdateCursor(fc)
for row in rows:
    newline = chr(13) + chr(10)
    if newline in row.TextString:
         row.setValue('TextString', row.TextString.replace(newline, ' '))
         rows.updateRow(row)
    del row
del rows

Thanks Curtis, I'll give that a shot.

MarcNakleh · ‎07-18-2013

Hello Curtis,

I think I was too vague: the cursor example I provided works fine. However, NOTHING I have tried in the Field Calculator box worked. Even if I replace references of '\r\n' to (chr13) + chr(10), it still doesn't work.

One of the responses on GIS StackExchange describing the exact same problem recommends the same thing as you, and those who tried it seemed to have just as little luck as I did.

I set up a quick test just to make sure that I was isolating the issue:

In a new shapefile, I add 2 text fields (TEXTFIELD, NEWTEXTF)

I create a single feature

I type the following text in Notepad: "This is a[ENTER]test" (where [ENTER] represents pressing the Enter button)

I copy-paste this text (which is on two lines) into the feature's TEXTFIELD value

I then run the following in FieldCalculator: NEWTEXTF = !TEXTFIELD!.upper()

This generates the following error message:
Executing: CalculateField test NEWTEXTF !TEXTFIELD!.upper() PYTHON_9.3 #
Start Time: Thu Jul 18 12:35:16 2013
ERROR 000539: Error running expression: "This is a
test".upper() <type 'exceptions.SyntaxError'>: EOL while scanning string literal (<string>, line 1)
Failed to execute (CalculateField).
Failed at Thu Jul 18 12:35:16 2013 (Elapsed Time: 0,00 seconds)

Any attempts to replace the newlines, using either either escape sequences or chr() calls, result in the same error.
It looks as if the CalculateField is passing along the newlines unescaped, which breaks the interpreter.

So, a couple of questions come to mind:

Do you get the same behaviour as me for the basic case of !TEXTFIELD!.upper()?

If yes, does this mean that ALL CalculateField calls that use the Python interpreter need to have their input sanitized to remove newlines? Or that we should just switch to Cursors in all cases to avoid any errors or difficulties?

Could you paste the actual working code you used to get the example working properly?

If you'd prefer, we can correspond directly by e-mail too, so I can send you the samples I have.

Thanks so much!

KarstenSedmera · ‎08-05-2013

Dang it!

Why does the field calculator for Python reject standard strings with \t , \n, etc. characters? The only one it seems to accept is \r.

e.g. f.write(!textfield1!+', '+!textfield2!+'\r') works
but f.write(!textfield1!+',\t'+!textfield2!+'\t') doesn't work

This bug severely limits the possibilities for writing output from a table to a file or an email.

What is the point of castrating Python's string operators?

curtvprice · ‎08-05-2013

Could you paste the actual working code you used to get the example working properly?

import os
import arcpy

tbl = arcpy.CreateScratchName("","","table","in_memory")
arcpy.CreateTable_management("in_memory",os.path.basename(tbl))
arcpy.AddField_management(tbl,"TESTFIELD","TEXT")
Rows = arcpy.InsertCursor(tbl)
Row = Rows.newRow()
Rows.insertRow(Row)
del Row, Rows
arcpy.CalculateField_management(tbl,"TESTFIELD","chr(10) + chr(13)","PYTHON_9.3")
print arcpy.GetMessages()
Rows = arcpy.SearchCursor(tbl)
Row = Rows.next()
print "Field value: ",repr(Row.TESTFIELD)
del Row, Rows

Results:

Executing: CalculateField in_memory\xx0 TESTFIELD "chr(10) + chr(13)" PYTHON_9.3 #
Start Time: Mon Aug 05 10:49:33 2013
Succeeded at Mon Aug 05 10:49:33 2013 (Elapsed Time: 0.00 seconds)
Field value:  u'\n\r'

Also this worked fine for me:

[ATTACH=CONFIG]26465[/ATTACH]

I think the problem you're running into is using the value of the field !TESTFIELD! if the field contains newlines - the tool will substitute in the value of the field into the expression - the geoprocessing messaging and Python interpreter can't deal with this.

I think the method you found is a good approach, that is, using Python with cursors (using the Calculate Value tool if in ModelBuilder) instead of using Calculate Field. I don't see another way around this, which is a design issue with the way Calculate Field accesses field values, and its connection with the geoprocessing message environment in how things are passed to Python.

Seems to me this is a good enhancement request for Calculate Field, i.e. have non printables converted to escape codes as part of the !FIELDNAME! -> value substitution process.