QST: read_excel - "odf engine" remove "\n" when reading multi-line cell from “ods” file

[msameen99]

• I have searched the [pandas] tag on StackOverflow for similar questions.

• I have asked my usage related question on StackOverflow.

---

I've a simple "ods" file (Test01.ods) with the below data in "sheet1" :-

also I saved it as "xlsx" (Test01.xlsx) so I've two files contains exactly the same data.

Now when I try to read them using Pandas "read_excel" with the below code

the "xslx" file is showing the line break char "\n" while the "ods" file does not.

Any idea why? and how to force "odf engine" to output the "\n" to the dataframe?

Thanks in advance

[asishm]

Can you post output of pd.show_versions() and a reproducible example (preferably post the file as well)

[msameen99]

Hi asishm,

Kindly find below the requested data, also I attached both the *.xlsx and *.ods Data files.

1- The output of the pd.show_versions()

INSTALLED VERSIONS
------------------
commit           : 2cb96529396d93b46abab7bbc73a208e708c642e
python           : 3.8.6.final.0
python-bits      : 64
OS               : Linux
OS-release       : 5.10.15-arch1-1
Version          : #1 SMP PREEMPT Wed, 10 Feb 2021 18:32:40 +0000
machine          : x86_64
processor        : 
byteorder        : little
LC_ALL           : None
LANG             : en_GB.UTF-8
LOCALE           : en_GB.UTF-8

pandas           : 1.2.4
numpy            : 1.20.1
pytz             : 2021.1
dateutil         : 2.8.1
pip              : 21.0.1
setuptools       : 52.0.0.post20210125
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : 1.3.8
lxml.etree       : 4.6.2
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : 2.11.3
IPython          : 7.22.0
pandas_datareader: None
bs4              : 4.9.3
bottleneck       : 1.3.2
fsspec           : None
fastparquet      : None
gcsfs            : None
matplotlib       : 3.3.4
numexpr          : None
odfpy            : None
openpyxl         : 3.0.7
pandas_gbq       : None
pyarrow          : None
pyxlsb           : 1.0.8
s3fs             : None
scipy            : 1.6.2
sqlalchemy       : None
tables           : None
tabulate         : None
xarray           : None
xlrd             : 2.0.1
xlwt             : None
numba            : 0.53.1

2- Below is a very basic JupyterLab Notebook:-

Cell 1

import pandas as pd
ods_df = pd.read_excel('MyData.ods', engine="odf", sheet_name="Sheet1")  
xlsx_df = pd.read_excel('MyData.xlsx', sheet_name="Sheet1")  

Cell 2
ods_df.loc[0,"Data"]

Cell 3
xlsx_df.loc[0,"Data"]

The output of the above cells is below, where the "ods" output did not return the newline char "\n" ("xslx" is OK) :

Data files Attachmentment:

MyData.ods
MyData.xlsx

[asishm]

This has to do with how odfpy is parsing it (which at this point seems to be somewhat inactive).

Pandas' does try to do some formatting for string cells here instead of directly calling str(cell) as I showed in the screenshot, but that is only for spaces -

pandas/pandas/io/excel/_odfreader.py

Line 199 in 1f96b6b

def _get_cell_string_value(self, cell) -> str:

but ultimately this is an upstream issue.

[msameen99]

Thanks asishm for your reply.
Is there a workaround here? as my data sources are ods files, and I'm afraid that conversation to xslx may affect the date

[asishm]

there's only 2 options

1. fix upstream (ideal) in odfpy, or
2. modify the _get_cell_string_value method linked above. This is not ideal as pandas can't and should not cover all possible cases.

That said this patch does seem to fix this for this file (and passes the other tests), but I don't have the knowledge to confirm whether this is generic enough

[msameen99]

Thanks a lot asishm for your support.

I think I'll close this issue now.

Regards

[yan12125]

The issue still persists with pandas 1.5.2 and odfpy 1.4.2. Could you reopen this issue until it's fixed either in odfpy or pandas?

[domdfcoding]

For posterity (and anyone like me who found this through google) pandas 2.2 and later support calamine for reading ods and xls files. As far as I can tell it correctly handles newlines. Just add the engine="calamine" argument to your pandas.read_excel call.