Reputation: 783
One minute my script works multiple days in a row, next minute I get this error.
File "<ipython-input-196-abdb28a77366>", line 1, in <module>
runfile('F:/-/-/-/cleaner_games_appstore_babil.py', wdir='F:/-/-/-')
File "C:\ProgramData\Anaconda3\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 827, in runfile
execfile(filename, namespace)
File "C:\ProgramData\Anaconda3\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 110, in execfile
exec(compile(f.read(), filename, 'exec'), namespace)
File "F:/-/-/-/cleaner_games_appstore_babil.py", line 112, in <module>
append_df_to_excel("stillfront.xlsx", dff, sheet_name='Apple_Babil', startrow=None, truncate_sheet=False, engine='openpyxl', header = False)
File "F:/-/-/-/cleaner_games_appstore_babil.py", line 84, in append_df_to_excel
writer.book = load_workbook(filename)
File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 311, in load_workbook
data_only, keep_links)
File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 126, in __init__
self.archive = _validate_archive(fn)
File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 98, in _validate_archive
archive = ZipFile(filename, 'r')
File "C:\ProgramData\Anaconda3\lib\zipfile.py", line 1222, in __init__
self._RealGetContents()
File "C:\ProgramData\Anaconda3\lib\zipfile.py", line 1289, in _RealGetContents
raise BadZipFile("File is not a zip file")
BadZipFile: File is not a zip file
To clarify I do not use any zip files. I found the code on here, StackOverflow, and there were not mentioning about the code not working, or error happening.
The script is supposed to write my pandas DataFrame to an excel sheet. Here's the part of the code that creates the error:
def append_df_to_excel(filename, df, sheet_name='Apple_Babil', startrow=None,
truncate_sheet=False,
**to_excel_kwargs):
# ignore [engine] parameter if it was passed
if 'engine' in to_excel_kwargs:
to_excel_kwargs.pop('engine')
writer = pd.ExcelWriter(filename, engine='openpyxl')
try:
# try to open an existing workbook
writer.book = load_workbook(filename)
# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
# index of [sheet_name] sheet
idx = writer.book.sheetnames.index(sheet_name)
# remove [sheet_name]
writer.book.remove(writer.book.worksheets[idx])
# create an empty sheet [sheet_name] using old index
writer.book.create_sheet(sheet_name, idx)
# copy existing sheets
writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
except FileNotFoundError:
# file does not exist yet, we will create it
pass
if startrow is None:
startrow = 0
# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
# save the workbook
writer.save()
append_df_to_excel("stillfront.xlsx", dff, sheet_name='Apple_Babil', startrow=None, truncate_sheet=False, engine='openpyxl', header = False)
Code was not edited or anything, just started not working.
Upvotes: 19
Views: 102013
Reputation: 522
this is how i fixed it:
1 - open the file with the issue in microsof excel
2 - a prompt will show up saying something like "the file is corupt will you want to recover all possible to recover"
3- click yes
4 - save the file with new name
5 - voila
Upvotes: 0
Reputation: 51
This error can also pop up when you have multiple pd.ExcelWriter() instances with the openpyxl engine trying to write onto the same excel file at the same time. To fix this you need to use pd.ExcelWriter.close() as soon as you finished writing onto the excel file. Here is an example of using two writers, one to create the file if iit does not exist and one to append more data to it.
writer = pd.ExcelWriter(filepath,
engine='openpyxl')
data1.to_excel(writer,
sheet_name=sheetname)
writer.close()
append_writer = pd.ExcelWriter(filepath,
mode='a',
if_sheet_exists='overlay',
engine='openpyxl')
data2.to_excel(append_writer,
sheet_name=sheetname,
startrow=start_row)
append_writer.close()
Upvotes: 1
Reputation: 511
If you have come here facing this issue in Google Colab while reading a .xlsx
file, please rename your file without any spaces (instead inlcude underscores _
).
df = pd.read_excel("/content/name_of_file.xlsx", engine='openpyxl')
UPDATE: It continues to be unstable if you follow the above method. The best way to avoid this is to upload the file in the drive and then read the file post mounting the drive.
Upvotes: 0
Reputation: 154
I got the same error. It turned out that the file was opened in another program, which caused the error. Closing the other program solved it.
Upvotes: 1
Reputation: 3739
Excel XLSX
files are zipped, XLS
files are not.
I believe this bug is related to a combination of
XLS
is not zipped, andopenpyxl
module must be used with XLSX
files.This problem is easy to solve by checking which type of Excel file is uploaded and using the appropriate engine to read into Pandas
.
from pathlib import Path
import pandas as pd
file_path = Path(filename)
file_extension = file_path.suffix.lower()[1:]
if file_extension == 'xlsx':
df = pd.read_excel(file.read(), engine='openpyxl')
elif file_extension == 'xls':
df = pd.read_excel(file.read())
elif file_extension == 'csv':
df = pd.read_csv(file.read())
else:
raise Exception("File not supported")
If you happen to have access to the file mimetype, you can perform the following test:
import pandas as pd
if file.content_type == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
df = pd.read_excel(file.read(), engine='openpyxl') # XLSX
elif file.content_type == 'application/vnd.ms-excel':
df = pd.read_excel(file.read()) # XLS
elif file.content_type == 'text/csv':
df = pd.read_csv(file.read()) # CSV
else:
raise Exception("File not supported")
Upvotes: 15
Reputation: 101
As others have already pointed out, a corrupted file is the culprit.
Perform these quick sanity checks:
In my case, I manually checked the excel file content and it turns out it was empty because I was not storing the file correctly. Once I fixed this, the "File is not a zip file" error got resolved.
Upvotes: 6
Reputation: 70
It is a very common issue and many people are trying to solve.It is related to excel file and openpyxl. Like @Barmar said in his comments xlsx, xlsm, etc are indeed zip. It was working fine until python 2.7 .
Try reading and writing to a csv instead, it won't be a problem.
Upvotes: 2