I have a question about appending a dataframe to existing sheet on existing file.
I tried to write the code by myself
writer = pd.ExcelWriter('existingFile.xlsx', engine='openpyxl', mode='a')
df.to_excel(writer, sheet_name="existingSheet", startrow=writer.sheets["existingSheet"].max_row, index=False, header=False)
and this would cause an error
ValueError: Sheet 'existingSheet' already exists and if_sheet_exists is set to 'error'.
and I googled and found this function in here;
Append existing excel sheet with new dataframe using python pandas
and even with this function, it still causes the same error, even though i think this function prevents this exact error from what i think.
Could you please help?
Thank you very much!
It seems this function is broken in pandas 1.3.0 Just look at this document , when trying to append to existing sheet it will either raise an error, create a new one or replace it
if_sheet_exists{‘error’, ‘new’, ‘replace’}, default ‘error’ How to behave when trying to write to a sheet that already exists (append mode only).
error: raise a ValueError.
new: Create a new sheet, with a name determined by the engine.
replace: Delete the contents of the sheet before writing to it.
New in version 1.3.0
The only solution is to downgrade pandas (1.2.3 is working for me)
pip install pandas==1.2.3
I guess I am late to the party but
Add keyword if_sheet_exists='replace' to ExcelWriter like this
pd.ExcelWriter('existingFile.xlsx', engine='openpyxl', mode='a', if_sheet_exists='replace' )
Then you can use the latest version of pandas
Since Pandas 1.4, there is also an 'overlay' option to if_sheet_exists.