Adding data to an existing excel table

Question

What I'm trying to accomplish:

Having a user drop an excel file/files into a network folder.
They then must run a python script that will take any files within the network folder (all of which are formatted the same) and append them to the bottom of a master excel file.
The script will then move the newly appended files to a separate folder.

What I've accomplished thus far:

I have wrote a script that when executed will take the files from within the folder and append them to the existing master excel file.

My issue:

Upon appending the files to the master excel, the script gets rid of my table inside of the master file.
I am wanting to append the new data to the bottom of the table and have it be incorporated into the table.

I have not yet coded anything to move the new files to a new folder but I don't foresee that being an issue. I am a beginner and am just learning python, so go easy. Any help would be very much appreciated. Thank you!

import glob
import pandas as pd
# File locations
location = "T:\\Example\\Test\\*.xlsx"
excel_files = glob.glob(location)
master = "T:\\File\\file.xlsx"
# Create data frames and concatenate files to master
df1 = pd.DataFrame()
df3 = pd.read_excel(master)
for excel_file in excel_files:
df2 = pd.read_excel(excel_file) 
df1 = pd.concat([df1, df2])
df4 = pd.concat([df3, df1])
df4.to_excel("T:\\File\\file.xlsx", index=False)

@pyaj when running your code it clears out any existing data in the master file and when adding in the new data it inserts the new arrays side-by-side rather than under the previous one like I am looking for. Any other suggestions? Thanks for your help! — jakecp
– jakecp, Commented Mar 29, 2022 at 15:55

pyaj · Accepted Answer · 2022-03-28 23:51:50Z

1

import os
import glob
import pandas as pd

# define relative path to folder containing excel data
location = "T:\\Example\\Test\\"

# load all excel files in one list
df_list = []
for file in glob.glob(os.path.join(location, "*.xlsx")):
    df = pd.read_excel(file)
    df_list.append(df)

# concatenate df_list to one big excel file
big_df = pd.concat(df_list, axis=1)

#save dataframe
big_df.to_excel("T:\\File\\file.xlsx", index=False)

edited Mar 28, 2022 at 23:51

answered Mar 28, 2022 at 23:46

pyaj

6407 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephan Over a year ago

OP requested appending to an existing Excel file. I believe should use function append_df_to_excel instead of to_excel. For reference, see similar request here: stackoverflow.com/questions/38074678/…

Collectives™ on Stack Overflow

Adding data to an existing excel table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related