CODEWITHSUNDEEP
python
doomdaam
doomdaam

Reputation: 783

"BadZipFile: File is not a zip file" - Error popped up all of a sudden

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

Answers (7)

kamal douma
kamal douma

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

Julien CRAMBES
Julien CRAMBES

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

Snehangsu
Snehangsu

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

Mathador
Mathador

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

Adonis Gaitatzis
Adonis Gaitatzis

Reputation: 3739

Excel XLSX files are zipped, XLS files are not.

I believe this bug is related to a combination of

  1. XLS is not zipped, and
  2. Since python-3.9, the openpyxl 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.

By file extension

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")

By file mimetype

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

Sharnam Khurana
Sharnam Khurana

Reputation: 101

As others have already pointed out, a corrupted file is the culprit.

Perform these quick sanity checks:

  1. Open the excel file. Is the data appearing correctly?
  2. Are you able to see the file size in the file's details in Windows Explorer?

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

Prototype
Prototype

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

Related Questions