Python appending dataframe to exsiting excel file and sheet

Question

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!

score 11 · Answer 1 · answered Oct 24 '21 at 12:47

11

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

answered Oct 24 '21 at 12:47

Mikk

283
3
7

3

For me this overwrites '(replace) content in the sheet. It doesn't append. – Captain Caveman Aug 10 '22 at 22:42

score 10 · Answer 2 · answered Feb 02 '22 at 08:15

10

Since Pandas 1.4, there is also an 'overlay' option to if_sheet_exists.

answered Feb 02 '22 at 08:15

Matthieu Boileau

111
1
3

not working `overlay` option for me – Harsha Biyani Jun 22 '22 at 09:52
Which Pandas version do you use? – Matthieu Boileau Jun 27 '22 at 08:01
tried with pandas==1.4.0 – Harsha Biyani Jun 27 '22 at 08:35
Which error do you get? – Matthieu Boileau Jun 27 '22 at 08:51
1

No error. but it overwrites everytime – Harsha Biyani Jun 27 '22 at 08:56
Added new question: https://stackoverflow.com/questions/72868000/python-pandas-excelwriter-is-not-working-as-expected if you know, please answer – Harsha Biyani Jul 05 '22 at 10:41
This approach worked for me with pandas `1.4.2`: `with pd.ExcelWriter(excelPath, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer: timeseries.to_excel(writer, timeseriesSheetName)` – johnDanger Jul 21 '22 at 00:42

KuhakuPixel · Accepted Answer · 2021-08-16T17:07:20.807

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

Python appending dataframe to exsiting excel file and sheet

3 Answers3