CODEWITHSUNDEEP
pythonexcelpandasopenpyxl
dkcloud9
dkcloud9

Reputation: 159

Python appending dataframe to exsiting excel file and sheet

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!

Upvotes: 10

Views: 33580

Answers (3)

Matthieu Boileau
Matthieu Boileau

Reputation: 121

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

Upvotes: 11

Mikk
Mikk

Reputation: 333

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

Upvotes: 14

KuhakuPixel
KuhakuPixel

Reputation: 223

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

Upvotes: 6

Related Questions