BUG: Was trying to read an ods file and ran into UnboundLocalError in odfreader.py

[taksuyu]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

for file in os.listdir('data'): pandas.read_excel(pathlib.Path('data', file), engine='odf')

Sorry I don't have a minimal data example at this time.

Problem description

Was trying to test pandas reading a collection of ods files and ran into this error.

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/michael/.local/share/virtualenvs/merge-csv-NFbvYFrS/lib/python3.8/site-packages/pandas/util/_decorators.py", line 296, in wrapper
    return func(*args, **kwargs)
  File "/home/michael/.local/share/virtualenvs/merge-csv-NFbvYFrS/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 311, in read_excel
    return io.parse(
  File "/home/michael/.local/share/virtualenvs/merge-csv-NFbvYFrS/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 906, in parse
    return self._reader.parse(
  File "/home/michael/.local/share/virtualenvs/merge-csv-NFbvYFrS/lib/python3.8/site-packages/pandas/io/excel/_base.py", line 443, in parse
    data = self.get_sheet_data(sheet, convert_float)
  File "/home/michael/.local/share/virtualenvs/merge-csv-NFbvYFrS/lib/python3.8/site-packages/pandas/io/excel/_odfreader.py", line 91, in get_sheet_data
    value = self._get_cell_value(sheet_cell, convert_float)
  File "/home/michael/.local/share/virtualenvs/merge-csv-NFbvYFrS/lib/python3.8/site-packages/pandas/io/excel/_odfreader.py", line 175, in _get_cell_value
    return self._get_cell_string_value(cell)
  File "/home/michael/.local/share/virtualenvs/merge-csv-NFbvYFrS/lib/python3.8/site-packages/pandas/io/excel/_odfreader.py", line 211, in _get_cell_string_value
    value.append(" " * spaces)
UnboundLocalError: local variable 'spaces' referenced before assignment

I took a look at the code in question and it seems like the line may be on the wrong indent level?

Expected Output

The usual dataframes 👍

Output of pd.show_versions()

INSTALLED VERSIONS

commit : d9fff27
python : 3.8.2.final.0
python-bits : 64
OS : Linux
OS-release : 5.4.0-42-generic
Version : #46-Ubuntu SMP Fri Jul 10 00:24:02 UTC 2020
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.1.0
numpy : 1.19.1
pytz : 2020.1
dateutil : 2.8.1
pip : 20.0.2
setuptools : 44.0.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : 1.0.1
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[WillAyd]

Your proposed fix seems reasonable - want to push a PR with a test case?

[taksuyu]

I believe I can do that for you. Assuming I can sleuth out what exactly the file is crashing on would a test case similar to this test work?

[simonjayhawkins]

I believe I can do that for you. Assuming I can sleuth out what exactly the file is crashing on would a test case similar to this test work?

hard to say what the best test would be without a reproducible failing code sample. It maybe that a simple roundtrip test could suffice.

[openwebcc]

find a minimal .ods file here that has one column with a header and one data-cell with content Test (1) that causes the fail. Put the .ods in the same directory where you run the following python3 snippet:

python3

import pandas as pd
print(pd.show_versions())
df = pd.read_excel("bug_odfreader_l211_spaces.ods", sheet_name="Test123")

I think, the fail is related to the type of the data-cell where fragment.qname is reported as ('urn:oasis:names:tc:opendocument:xmlns:text:1.0', 'span'). As it is not handled by the if-clause, spaces is not set and value.append(" " * spaces) throws the error. Fixing the indent would solve the problem, but leave the span-clause unimplemented.

I could not reproduce creating a cell of type span in LibreOffice.

Sorry for linking to the .ods file but I was not able to upload it here as .ods is not a supported file type

[taksuyu]

I was working with some bad csv data and using libreoffice to deal with it so that could be how a span, or other element cause I think it might be a line-break, got in there. I got around the issue by returning the data to a csv file and continuing as normal. I'll be taking a look into implementing this test in the coming days now that my work is done.

Judging by the spec there is quite a few elements that aren't being checked for. I'm not all that sure what sort of guarantees pandas makes for reading data so could I get some feedback on whether we should handle all these cases or just set something of a default for spaces so that it doesn't throw?