I have saved several HTML files with data in table format on GitHub and would now like to read the data to one EXCEL workbook.
As pd.ExcelWriter('output.xlsx', mode='a') is apparently deprecated, I am now trying to use openpyxl.
This is my code:
# import packages
import pandas as pd
from urllib.parse import urljoin
from openpyxl import load_workbook
# define EXCEL file for output data
book = load_workbook("C:\\Users\\###\\Downloads\\VMA2013_raw-data.xlsx")
writer = pd.ExcelWriter("C:\\Users\\###\\Downloads\\VMA2013_raw-data.xlsx", engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
# files with WBRA data on Github
filenames= ("WebDataRA-2022-09-21T11_52_44.html",
"WebDataRA-2022-09-21T11_54_00.html",
"WebDataRA-2022-09-21T11_55_33.html",
"WebDataRA-2022-09-21T11_59_16.html",
"WebDataRA-2022-09-21T12_07_45.html",
"WebDataRA-2022-09-21T12_08_42.html",
"WebDataRA-2022-09-21T12_10_20.html",
"WebDataRA-2022-09-21T12_15_34.html",
"WebDataRA-2022-09-21T12_16_50.html",
"WebDataRA-2022-09-21T12_17_43.html",
"WebDataRA-2022-09-21T12_18_29.html",
"WebDataRA-2022-09-21T12_19_25.html",
"WebDataRA-2022-09-21T12_20_53.html",
"WebDataRA-2022-09-21T12_21_46.html",
"WebDataRA-2022-09-21T12_23_01.html",
"WebDataRA-2022-09-21T12_24_06.html",
"WebDataRA-2022-09-21T12_25_53.html",
"WebDataRA-2022-09-21T12_26_53.html",
"WebDataRA-2022-09-21T12_27_19.html",
"WebDataRA-2022-09-21T12_28_28.html",
"WebDataRA-2022-09-21T12_28_50.html",
"WebDataRA-2022-09-21T12_30_41.html",
"WebDataRA-2022-09-21T12_31_38.html",
"WebDataRA-2022-09-21T12_32_47.html",
"WebDataRA-2022-09-21T12_33_18.html",
"WebDataRA-2022-09-21T12_34_00.html",
"WebDataRA-2022-09-21T12_34_20.html",
"WebDataRA-2022-09-21T12_34_49.html",
"WebDataRA-2022-09-21T12_35_04.html",
"WebDataRA-2022-09-21T12_36_33.html",
"WebDataRA-2022-09-21T12_37_04.html",
"WebDataRA-2022-09-21T12_37_30.html",
"WebDataRA-2022-09-21T12_38_54.html",
"WebDataRA-2022-09-21T12_39_10.html",
"WebDataRA-2022-09-21T12_40_14.html",
"WebDataRA-2022-09-21T12_40_42.html",
"WebDataRA-2022-09-21T12_41_56.html",
"WebDataRA-2022-09-21T12_42_52.html",
"WebDataRA-2022-09-21T12_44_05.html",
"WebDataRA-2022-09-21T12_45_28.html",
"WebDataRA-backup-1000.html")
# Create URL for each file whose table we want to extract
path="https://raw.githubusercontent.com/####/*****/main/VMA2013_tweets/"
for f in filenames:
url=urljoin(path, f)
# Assign the table data to a Pandas dataframe
table = pd.read_html(url)[0]
# Store the dataframe in Excel file
startrow = writer.sheets['VMA2013'].max_row
table.to_excel(writer, startrow,index = False)
print("done")
The error notification I am getting is:
BadZipFile Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_3488\720253268.py in <module>
6 # define EXCEL file for output data
7
----> 8 book = load_workbook("C:\\Users\\###\\Downloads\\VMA2013_raw-data.xlsx")
9 writer = pandas.ExcelWriter("C:\\Users\\###\\Downloads\\VMA2013_raw-data.xlsx", engine='openpyxl')
10 writer.book = book
C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
314 """
315 reader = ExcelReader(filename, read_only, keep_vba,
--> 316 data_only, keep_links)
317 reader.read()
318 return reader.wb
C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in __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
C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in _validate_archive(filename)
94 raise InvalidFileException(msg)
95
---> 96 archive = ZipFile(filename, 'r')
97 return archive
98
C:\ProgramData\Anaconda3\lib\zipfile.py in __init__(self, file, mode, compression, allowZip64, compresslevel)
1256 try:
1257 if mode == 'r':
-> 1258 self._RealGetContents()
1259 elif mode in ('w', 'x'):
1260 # set the modified flag so central directory gets written
C:\ProgramData\Anaconda3\lib\zipfile.py in _RealGetContents(self)
1323 raise BadZipFile("File is not a zip file")
1324 if not endrec:
-> 1325 raise BadZipFile("File is not a zip file")
1326 if self.debug > 1:
1327 print(endrec)
BadZipFile: File is not a zip file
I cannot make sense of it. Why is a zip file required? The EXCEL workbook has been created in my Downloads folder as assigned, but when I try and open it, the file is broken.
When I create the required file beforehand, the error notification is this:
TypeError Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_3488\1843287704.py in <module>
70 startrow = writer.sheets['VMA2013'].max_row
71
---> 72 table.to_excel(writer, startrow,index = False)
73
74 print("done")
C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\generic.py in to_excel(self, excel_writer, sheet_name, na_rep, float_format, columns, header, index, index_label, startrow, startcol, engine, merge_cells, encoding, inf_rep, verbose, freeze_panes, storage_options)
2289 freeze_panes=freeze_panes,
2290 engine=engine,
-> 2291 storage_options=storage_options,
2292 )
2293
C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\formats\excel.py in write(self, writer, sheet_name, startrow, startcol, freeze_panes, engine, storage_options)
843 startrow=startrow,
844 startcol=startcol,
--> 845 freeze_panes=freeze_panes,
846 )
847 finally:
C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py in write_cells(self, cells, sheet_name, startrow, startcol, freeze_panes)
447 else:
448 wks = self.book.create_sheet()
--> 449 wks.title = sheet_name
450 self.sheets[sheet_name] = wks
451
C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\workbook\child.py in title(self, value)
88 raise ValueError("Worksheet titles must be str")
89
---> 90 m = INVALID_TITLE_REGEX.search(value)
91 if m:
92 msg = "Invalid character {0} found in sheet title".format(m.group(0))
TypeError: expected string or bytes-like object