[Python] Read Excel file from OneDrive / Sharepoint - error code: File is not a zip file

I am trying to read an excel file from SharePoint to python and my first error message was that I should define an engine manually, so I defined the engine = 'openpyxl' and now the following error message comes up: File is not a ZIP File

From the previous Q&As it was often talked about if the Excel - file is a real Excel file or some text file with a fake xlsx extension.

The excel file was created using Microsoft Excel and its stored in a shared OneDrive folder (Team - Sharepoint). Does it affect the error message?

How can i solve this?

Many thanks in advance!

My Code:

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File

url_sp = 'https://company.sharepoint.com/teams/TeamE'
username_sp = 'MyUsername'
password_sp = 'MyPassword'
folder_url_sp = '/Shared%20Documents/02%20Team%20IAP/06_Da-An/Data/E/Edate.xlsx'

#Authentication
ctx_auth = AuthenticationContext(url_sp)
if ctx_auth.acquire_token_for_user(username_sp, password_sp):
ctx = ClientContext(url_sp, ctx_auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
print('Authentication sucessfull')

else:
print(ctx_auth.get_last_error())

import io

response = File.open_binary(ctx,folder_url_sp)

bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0)

data = pd.read_excel(bytes_file_obj,sheet_name = None, engine = 'openpyxl')


The Error:

BadZipFile Traceback (most recent call last)
Cell In[29], line 32
29 bytes_file_obj.write(response.content)
30 bytes_file_obj.seek(0)
---> 32 data = pd.read_excel(bytes_file_obj, sheet_name= None, engine = 'openpyxl')

File ~\Anaconda3\lib\site-packages\pandas\util\_decorators.py:311, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
305 if len(args) > num_allow_args:
306 warnings.warn(
307 msg.format(arguments=arguments),
308 FutureWarning,
309 stacklevel=stacklevel,
310 )
--> 311 return func(*args, **kwargs)

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py:457, in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, decimal, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
455 if not isinstance(io, ExcelFile):
456 should_close = True
--> 457 io = ExcelFile(io, storage_options=storage_options, engine=engine)
458 elif engine and engine != io.engine:
459 raise ValueError(
460 "Engine should not be specified when passing "
461 "an ExcelFile - ExcelFile already has the engine set"
462 )

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py:1419, in ExcelFile.__init__(self, path_or_buffer, engine, storage_options)
1416 self.engine = engine
1417 self.storage_options = storage_options
-> 1419 self._reader = self._engines[engine](self._io, storage_options=storage_options)

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py:525, in OpenpyxlReader.__init__(self, filepath_or_buffer, storage_options)
514 """
515 Reader using openpyxl engine.
516
(...)
522 passed to fsspec for appropriate URLs (see ``_get_filepath_or_buffer``)
523 """
524 import_optional_dependency("openpyxl")
--> 525 super().__init__(filepath_or_buffer, storage_options=storage_options)

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py:518, in BaseExcelReader.__init__(self, filepath_or_buffer, storage_options)
516 self.handles.handle.seek(0)
517 try:
--> 518 self.book = self.load_workbook(self.handles.handle)
519 except Exception:
520 self.close()

File ~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py:536, in OpenpyxlReader.load_workbook(self, filepath_or_buffer)
533 def load_workbook(self, filepath_or_buffer: FilePath | ReadBuffer[bytes]):
534 from openpyxl import load_workbook
--> 536 return load_workbook(
537 filepath_or_buffer, read_only=True, data_only=True, keep_links=False
538 )

File ~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py:315, in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
288 def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA,
289 data_only=False, keep_links=True):
290 """Open the given filename and return the workbook
291
292 aram filename: the path to open or a file-like object
(...)
313
314 """
--> 315 reader = ExcelReader(filename, read_only, keep_vba,
316 data_only, keep_links)
317 reader.read()
318 return reader.wb

File ~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py:124, in ExcelReader.__init__(self, fn, read_only, keep_vba, data_only, keep_links)
122 def __init__(self, fn, read_only=False, keep_vba=KEEP_VBA,
123 data_only=False, keep_links=True):
--> 124 self.archive = _validate_archive(fn)
125 self.valid_files = self.archive.namelist()
126 self.read_only = read_only

File ~\Anaconda3\lib\site-packages\openpyxl\reader\excel.py:96, in _validate_archive(filename)
89 msg = ('openpyxl does not support %s file format, '
90 'please check you can open '
91 'it with Excel first. '
92 'Supported formats are: %s') % (file_format,
93 ','.join(SUPPORTED_FORMATS))
94 raise InvalidFileException(msg)
---> 96 archive = ZipFile(filename, 'r')
97 return archive

File ~\Anaconda3\lib\zipfile.py:1266, in ZipFile.__init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps)
1264 try:
1265 if mode == 'r':
-> 1266 self._RealGetContents()
1267 elif mode in ('w', 'x'):
1268 # set the modified flag so central directory gets written
1269 # even if no files are added to the archive
1270 self._didModify = True

File ~\Anaconda3\lib\zipfile.py:1333, in ZipFile._RealGetContents(self)
1331 raise BadZipFile("File is not a zip file")
1332 if not endrec:
-> 1333 raise BadZipFile("File is not a zip file")
1334 if self.debug > 1:
1335 print(endrec)

BadZipFile: File is not a zip file

```

Continue reading...