0

I have an excel file with 6 sheets, in one of sheets I would like to a append data some data but keep the other sheets intact.

I have the following code

import pandas as pd
xls = pd.ExcelFile('myfile.xlsx')
df1 = pd.read_excel(xls, 'log')
d = {'Date': [2023], 'Time': [180855],'Q': ['2']}
df2 = pd.DataFrame(data=d)
df_final = pd.concat([df1,df2])
df_final.to_excel(xls, sheet_name='log')

So I specify the sheet and only interact with the sheet log , however my new saved sheet only has the log sheet saved and all other sheets deleted.

How do I prevent this?

scotmanDavid
  • 139
  • 1
  • 9
  • 1
    Does this answer your question? [How to write to an existing excel file without overwriting data (using pandas)?](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas) – BigBen Mar 24 '23 at 14:10
  • @BigBen Im trying to get it to work but it dosen't seem to work in my case. when I run it, it deletes my notebook content and then crashes. I've tried tinkering with it, but no luck :/ – scotmanDavid Mar 24 '23 at 14:37

1 Answers1

0

Ok so it seems that the answer is pretty easy actually

Instead of

df_final.to_excel(xls, sheet_name='log')

I needed

with pd.ExcelWriter('myfile.xlsx', mode="a", engine="openpyxl", if_sheet_exists='replace') as writer:
    df_final.to_excel(writer, sheet_name="log")

So final code looks like this:

import pandas as pd
xls = pd.ExcelFile('myfile.xlsx')
df1 = pd.read_excel(xls, 'log')
d = {'Date': [2023], 'Time': [180855],'Q': ['2']}
df2 = pd.DataFrame(data=d)
df_final = pd.concat([df1,df2])
with pd.ExcelWriter('myfile.xlsx', mode="a", engine="openpyxl", if_sheet_exists='replace') as writer:
    df_final.to_excel(writer, sheet_name="log")
scotmanDavid
  • 139
  • 1
  • 9