Trouble to read an excel file with pandas

Question

I'm trying to read an excel file with pandas (+50000 rows), and it gives me the same error in all cases. the code:

strfile='C:\\Users\\...\\excel_files\\excelfile_01.xls'

Try 01:

import pandas as pd

data = pd.read_excel(strfile, low_memory=False)

Try 02:

import pandas as pd

data = pd.read_excel(strfile, encoding='utf-16-le',low_memory=False)

Try 03:

import pandas as pd

data = pd.read_excel(strfile, encoding='sys.getfilesystemencoding()',low_memory=False)

Try 04:

import pandas as pd

data = pd.read_excel(strfile, encoding='latin-1',low_memory=False)

The error in all cases:

UnicodeDecodeError: 'utf-16-le' codec can't decode bytes in position 146-147: unexpected end of data

Any help/tip will be greatly appreciated. Thanks in advance.

@coldspeed, thanks, already tested, but with the same error as result — user9910379, Aug 20 '18 at 18:14
As a test, have you tried saving as CSV and using `pd.read_csv`? — jpp, Aug 20 '18 at 18:19
@jpp I have tested with 1 file (I have a 400+ excel files I need to read) but gives me the following error: 'utf-8' codec can't decode byte 0xd0 in position 0: invalid continuation byte — user9910379, Aug 20 '18 at 18:29
Try saving your legacy `.xls` file in the modern `.xlsx` format and send it to `pd.read_excel()` — jeschwar, Aug 20 '18 at 18:35
Already solved by creating an excel macro to batch open all files and save them as .xlsx as @jeschwar suggested. — user9910379, Aug 24 '18 at 15:00

score 2 · Accepted Answer · answered Aug 24 '18 at 15:08

2

Posting my previous comment as an answer:

Try saving your legacy .xls file in the modern .xlsx format and send it to pd.read_excel()

answered Aug 24 '18 at 15:08

jeschwar

1 Answers1