I've written a script that pulls and summarizes data from 2 databases regularly. I'm using write.xlsx
to write 3 data frames to 3 different tabs:
- Tab1 is data from one database,
- Tab2 is data from another, and
- Tab3 is data summarized from both.
It is fine if Tabs 1&2 are overwritten with each run, but I'd like Tab3 to add a new row (with sys.date
in col1) each run, thus giving me a history of the data summaries. Is this possible and how?
append=TRUE
is not getting me what I'm looking for. My next option (less desirable) is to just write the summarized data to a separate file with sys.date in the filename.
# Write to spreadsheet w/ 3 tabs
write.xlsx(df1, file = "file_location\\filename.xlsx", sheetName = "dataset1", row.names = FALSE, append = FALSE) #write to tab1
write.xlsx(df2, file = "file_location\\filename.xlsx", sheetName = "dataset2", row.names = FALSE, append = TRUE) #write to tab2
write.xlsx(summary_df, file = "file_location\\filename.xlsx", sheetName = "summarized_data", row.names = FALSE, append = TRUE) #write to next blank row in tab3
The script is writing over the summary data with each run. I would like it to append it to the next blank row.
I would check out the openxlsx package - it allows you to write data starting at a given row in an Excel file. The only catch is that you will need to read the current Excel file in order to determine which line is the last one containing data. You can do that with the readxl package and using nrow() of the dataframe you create from the Excel file to determine the number of rows.