Getting errors saving a large excel file using pandas

Question

Im testing how pandas works saving excel files and came along and error when i tried making it read and save a big excel file.

So i tried it with a small sample from my big excel file to test, around 150 rows, and it works. So i used the full sheet, around 200,000 rows, and got a couple errors

This is what Im using to test saving

import pandas as pd

xls = pd.ExcelFile('File\\Path\\File.xlsx')
df = pd.read_excel(xls, 'Sheet_Name')

df.to_excel('TEST.xlsx')

This is all the code so Im wondering why Im getting errors.

This is what the console reports

File "C:/Users/CSR001/PycharmProjects/CLeaner/Base.py", line 30, in <module>
    df.to_excel('TEST.xlsx')
  File "C:\Users\CSR001\PycharmProjects\CLeaner\venv\lib\site-packages\pandas\core\generic.py", line 2257, in to_excel
    engine=engine,
  File "C:\Users\CSR001\PycharmProjects\CLeaner\venv\lib\site-packages\pandas\io\formats\excel.py", line 739, in write
    freeze_panes=freeze_panes,
  File "C:\Users\CSR001\PycharmProjects\CLeaner\venv\lib\site-packages\pandas\io\excel\_openpyxl.py", line 416, in write_cells
    xcell.value, fmt = self._value_with_fmt(cell.val)
  File "C:\Users\CSR001\PycharmProjects\CLeaner\venv\lib\site-packages\openpyxl\cell\cell.py", line 252, in value
    self._bind_value(value)
  File "C:\Users\CSR001\PycharmProjects\CLeaner\venv\lib\site-packages\openpyxl\cell\cell.py", line 205, in _bind_value
    value = self.check_string(value)
  File "C:\Users\CSR001\PycharmProjects\CLeaner\venv\lib\site-packages\openpyxl\cell\cell.py", line 169, in check_string
    raise IllegalCharacterError
openpyxl.utils.exceptions.IllegalCharacterError

Note: The main excel file has some symbols characters in the cells so it could be that causing the errors.

There are cell that have data like this

Ex.

____
&#xC3;&#x192;znur
-
H√•kon

Are these affecting how pandas can save?

check [How to remove illegal characters so a dataframe can write to Excel](https://stackoverflow.com/questions/42306755/how-to-remove-illegal-characters-so-a-dataframe-can-write-to-excel) — anky, Aug 06 '19 at 04:59

score 0 · Answer 1 · answered Aug 06 '19 at 05:02

0

The problem is not pandas but with the excel parsing lib. It is 100% an encoding error. Try adding the following parameter:

encoding='utf8'

answered Aug 06 '19 at 05:02

Abdur-Rahmaan Janhangeer

570
4
20

1

where should i add the parameter to. – John Zapanta Aug 06 '19 at 05:18
df = pd.read_excel(xls, 'Sheet_Name', encoding) like this? sorry im new to this. – John Zapanta Aug 06 '19 at 05:21
sorry it is in to_excel, ```df.to_excel('TEST.xlsx', encoding="utf8")``` – Abdur-Rahmaan Janhangeer Aug 06 '19 at 10:16

ImMJ · Answer 2 · 2019-08-06T11:27:02.883

0

Instead of using read_excel use Parse this will handle the IllegalCharacterError Exception.

import pandas as pd
path = r"C:\Users\uib05928\Desktop\prac1.xlsx"
xls = pd.ExcelFile(path)
df = xls.parse("Sheet1").head()
df.to_excel('C:\Users\uib05928\Desktop\TEST.xlsx')

Output-

edited Aug 06 '19 at 11:27

answered Aug 06 '19 at 11:06

ImMJ

206
2
5

score 0 · Answer 3 · answered Aug 07 '19 at 03:07

0

So i just changed my to_excel into to_csv and it works. I dont know why it works only saving as a csv but it does, any ways, its fine i was planning to convert it into a csv file later on.

df.to_excel('TEST.xlsx')
df.to_csv('TEST.csv')

answered Aug 07 '19 at 03:07

John Zapanta

179
1
3
14

Getting errors saving a large excel file using pandas

3 Answers3