I currently have this code. It works perfectly.
It loops through excel files in a folder,
removes the first 2 rows,
Then saves them out as individual excel files,
and it also saves the the files in the loop as an appended file.
Currently the appended file overwrites the existing file each time I run the code.
I need to append the new data to the bottom of the already existing excel sheet ('master_data.xlsx)
dfList = []
path = 'C:\\Test\\TestRawFile'
newpath = 'C:\\Path\\To\\New\\Folder'
for fn in os.listdir(path):
# Absolute file path
file = os.path.join(path, fn)
if os.path.isfile(file):
# Import the excel file and call it xlsx_file
xlsx_file = pd.ExcelFile(file)
# View the excel files sheet names
xlsx_file.sheet_names
# Load the xlsx files Data sheet as a dataframe
df = xlsx_file.parse('Sheet1',header= None)
df_NoHeader = df[2:]
data = df_NoHeader
# Save individual dataframe
data.to_excel(os.path.join(newpath, fn))
dfList.append(data)
appended_data = pd.concat(dfList)
appended_data.to_excel(os.path.join(newpath, 'master_data.xlsx'))
I thought this would be a simple task, but I guess not.
I think I need to bring in the master_data.xlsx file as a dataframe, then match the index up with the new appended data, and save it back out. Or maybe there is an easier way. Any Help is appreciated.
解决方案
You can use openpyxl engine in conjunction with startrow parameter:
In [48]: writer = pd.ExcelWriter('c:/temp/test.xlsx', engine='openpyxl')
In [49]: df.to_excel(writer, index=False)
In [50]: df.to_excel(writer, startrow=len(df)+2, index=False)
In [51]: writer.save()
c:/temp/test.xlsx:
PS you may also want to specify header=None if you don't want to duplicate column names...