Pandas "read_excel" : How to read multi-line cell from "ods" file?

Question

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

Answer 1

As per this issue in Pandas's Github, this is an issue with the upstream "odfpy" package, our options are one of the following:

1. fix upstream (ideal) in odfpy
2. modify the _get_cell_string_value method.

My workaround: save the "odf" file as "xslx" then work with it in pandas instead.