0

I have a daily recurring script that pulls data from an API and exports it to a EXCEL file. Below is the code I am using for the dataframe to excel conversion.

df.to_excel("/Users/janedoe/Downloads/Covid-19/Covid_Export_Global.xlsx", sheet_name = 'Sheet_name_1')

  1. Will above df conversion add new rows of data to the same excel file everyday?

  2. Or will it deleted the previous day data and write a new rows of data to the same excel file everyday?

If it is option number 2 is there a way I can achieve it like option 1.

Piyush Patil
  • 14,512
  • 6
  • 35
  • 54
  • The second: it will delete the previous file and write a new one everyday – jjsantoso Mar 31 '20 at 16:02
  • Is there a way to avoid this? I wanted the first option to work – Piyush Patil Mar 31 '20 at 16:07
  • you can check if the file is present, and open the excel writer with append as described in the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html) to add it – modesitt Mar 31 '20 at 16:54

1 Answers1

0

I solved my problem using openpyxl for Excel provided by Pandas.

Below is sample code for doing something similar

enteimport pandas
from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') 
writer.book = book

## ExcelWriter for some reason uses writer.sheets to access the sheet.
## If you leave it empty it will not know that sheet Main is already there
## and will create a new sheet.

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()r code here

For more solutions refer this

How to write to an existing excel file without overwriting data (using pandas)?

Piyush Patil
  • 14,512
  • 6
  • 35
  • 54