Openpyxl Unicode Values

Question

I am using openpyxl to read in cell values from a Excel Spreadsheet. One of the cells has values that are separated by a newline. I want to split the string using the newline character as the delimiter. However it seems that openpyxl is serializing the carriage return into a non-standard format. Look at the example below.

Code

import openpyxl

# Open the worksheet
wb = openpyxl.load_workbook(wb_path)
ws = wb.get_sheet_by_name("testing")

# Get the string value
tests_str = ws.cell(row = row, column = column).value

# Split text on newlines and add them to the list
tests = []
for test in tests_str.splitlines():
    tests.append(test)

Output

>>> tests_str
u'Test1_x000D_\nTest2_x000D_\nTest3_x000D_'
>>> tests
[u'Test1_x000D_', u'Test2_x000D_', u'Test3_x000D_']

openpyxl seems to be serializing the \r character into _x000D_ which is why splitlines() is not removing it as a newline character. Is there a reason why openpyxl behaves like this? Am I doing something wrong?

Answer 1

As stated in some support issue from 2015 (see Google cache entry to avoid login), which has been posted in the official Bitbucket project of openpyxl, this is done by Excel and seems to be out of control for openpyxl.

To resolve this, there are some utilty functions for encoding/decoding.

>> openpyxl.utils.escape.unescape(tests_str))
u'Test1\r\nTest2\r\nTest3\r'

Link to documentation: https://openpyxl.readthedocs.io/en/stable/api/openpyxl.utils.escape.html

Answer 2

It looks like either openpyxl or Excel is encoding carriage returns (\r, ASCII 0Dh) in that manner. You can convert them back or split on them as well:

>>> s=u'Test1_x000D_\nTest2_x000D_\nTest3_x000D_'
>>> s.split('_x000D_\n')
[u'Test1', u'Test2', u'Test3_x000D_']     # This misses the final one.
>>> s.replace('_x000D_','').splitlines()  # Better...
[u'Test1', u'Test2', u'Test3']