10

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!

Improve this question

3 Answers 3

Reset to default
14

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

For me this overwrites '(replace) content in the sheet. It doesn't append.
11

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

Improve this answer

7 Comments

not working overlay option for me
Which Pandas version do you use?
tried with pandas==1.4.0
No error. but it overwrites everytime
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)
|
6

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.