[FIXED] Read Excel from standard input using pandas Python 2 vs 3

Issue

I'm trying to write a tool for parsing Excel on the command line; it's working with old versions of Python and pandas, but not with the new version.

It looks like a difference in sys.stdin between Python 2 and Python 3, but I'm at a loss to continue.

$ conda create -n py2 python=2.7 pandas=0.17.1 xlrd
$ source activate py2
(py2) $ cat data.xlsx | python -c "import pandas as pd; import sys; df = pd.read_excel(sys.stdin); print(df.head())"
    x    y
0   1    2
1  10  100
(py2) $ source deactivate

$ conda create -n py3 python=3.6 pandas=0.23.3 xlrd
$ source activate py3
(py3) $ cat data.xlsx | python -c "import pandas as pd; import sys; df = pd.read_excel(sys.stdin); print(df.head())"
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/Users/bilow/anaconda3/envs/py3/lib/python3.6/site-packages/pandas/util/_decorators.py", line 178, in wrapper
    return func(*args, **kwargs)
  File "/Users/bilow/anaconda3/envs/py3/lib/python3.6/site-packages/pandas/util/_decorators.py", line 178, in wrapper
    return func(*args, **kwargs)
  File "/Users/bilow/anaconda3/envs/py3/lib/python3.6/site-packages/pandas/io/excel.py", line 307, in read_excel
    io = ExcelFile(io, engine=engine)
  File "/Users/bilow/anaconda3/envs/py3/lib/python3.6/site-packages/pandas/io/excel.py", line 391, in __init__
    data = io.read()
  File "/Users/bilow/anaconda3/envs/py3/lib/python3.6/codecs.py", line 321, in decode
    (result, consumed) = self._buffer_decode(data, self.errors, final)
UnicodeDecodeError: 'utf-8' codec can't decode bytes in position 15-16: invalid continuation byte
(py3) $ source deactivate

Solution

As you point out, there's definitely a difference between the sys.stdin object in Python 2 vs 3. You can verify this by:

cat data.xlsx | python -c "import sys; print(sys.stdin)"

which in Python 2 yields,

open file '<stdin>', mode 'r' at 0x104a7e0c0>

and in Python 3 yields,

<_io.TextIOWrapper name='<stdin>' mode='r' encoding='US-ASCII'>

I don't know enough about Python I/O to say why that is a meaningful difference. Nevertheless, if it's important that you use sys.stdin then you'll need to check for the Python version and handle each situation respectively, like so:

# Note, you'll need to specify the `xlrd` engine for the buffer to work
$ cat data.xlsx | python -c "import pandas as pd, sys; data = sys.stdin if sys.version_info[0] == 2 else sys.stdin.buffer; df = pd.read_excel(data, engine='xlrd'); print(df.head())"

Now, this seems cumbersome to me. Alternatively, why don't you just skip the possible pitfalls of stdin and pass the file name:

python -c "import pandas as pd, sys; df = pd.read_excel(sys.argv[1]); print(df.head())" data.xlsx

It simplifies and guarantees Python 2 and 3 compatibility.

Answered By - T. Ray