0

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)
jakecp
  • 1
  • 1
  • @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 Mar 29 '22 at 15:55

1 Answers1

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)
pyaj
  • 545
  • 5
  • 15
  • 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: https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas – Stephan Mar 29 '22 at 00:25