How can we write new data to existing Excel spreadsheet

0 votes

I have a weekly procedure that generates a data frame with about 1,000 entries. In order to avoid having to read the spreadsheet again as the file gets larger, I would like to be able to add it to an existing sheet. I saw this answer here: Append existing excel sheet with new dataframe using python pandas. Sadly, it doesn't seem to be functioning well for me. I'm attempting to add the following dummy code to that already-existing file. Currently, it has two drawbacks: first, it overwrites the data rather than appending it. Second, even after the software has started, when I try to open the file, it will only let me do so in read-only mode. I can confirm that I'm also utilizing Pandas 1.4.

import pandas as pd


data = {'Name': ['Tom', 'Joseph', 'Krish', 'John'], 'Age': [20, 21, 19, 18]}
df = pd.DataFrame(data)


filename = "Testing Append Process.xlsx"
writer = pd.ExcelWriter(filename, engine="openpyxl", mode="a", if_sheet_exists="overlay")

df.to_excel(writer, index=False)
writer.save()
Oct 14 in Others by Kithuzzz
• 20,660 points
76 views

1 answer to this question.

0 votes

Please notes that the Testing Append Process.xlsx file has to be created before running this code.

from openpyxl import load_workbook
import pandas as pd

data = {'Name': ['Tom', 'Joseph', 'Krish', 'John'], 'Age': [20, 21, 19, 18]}
df = pd.DataFrame(data)

filename = "Testing Append Process.xlsx"
workbook = load_workbook(filename)
writer = pd.ExcelWriter(filename, engine='openpyxl')
writer.book = workbook
writer.sheets = {ws.title: ws for ws in workbook.worksheets}

df.to_excel(writer, startrow=writer.sheets['Sheet1'].max_row, index = False, header= False)

writer.close()

Returns the following if you will run the code twice.

enter image description here

answered Oct 14 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10 in Others by gaurav
• 22,040 points
296 views
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
6,119 views
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
66 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 3,131 views
0 votes
1 answer
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
43 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP