Copy & automate data from multiple workbook into an existing Master workbook without losing formatting using python

Question

I have multiple excel workbooks with the same format but different monthly data. I want to copy these data into an existing worksheet under an existing Master wkbook (same data format with the other workbooks)& without losing the formatting in the Master file using python

I have tried using xlwings and pywin libraries. The xlwings code below was able to copy the contents of a source wkbk into the Result wkbook but however into a separate sheet. I want the data to be copied into a specified sheet of the Master wkbook!(Both libraries generated the same result)

#Using xlwings
import xlwings as wx
path1='C:\\Users\\G852589\\data transfer\\data1.xlsx'
#path0 = 'C:\\Users\\G852589\\data transfer\\data2.xlsx'
path2='C:\\Users\\G852589\\data transfer\\Result.xlsx'
wb1 = xw.Book(path1)
wb2 = xw.Book(path2)

ws1 = wb1.sheets(1)
ws1.api.Copy(Before=wb2.sheets(1).api)
wb2.save()
wb2.app.quit()

#Using pywin32
import os
import win32com.client as win32
from win32com.client import Dispatch
path1='C:\\Users\\G852589\\data transfer\\data1.xlsx'
#path0 = 'C:\\Users\\G852589\\data transfer\\data2.xlsx'
path2='C:\\Users\\G852589\\data transfer\\Result.xlsx'

xl=Dispatch('Excel.Application')
xl.Visible = True
wb1= xl.Workbooks.Open(Filename=path1)
wb2= xl.Workbooks.Open(Filename=path2)
ws1 =wb1.Worksheets(1)

ws1.Copy(Before=wb2.Worksheets(1))
wb2.Close(SaveChanges=True)
xl.Quit()

I need to be able to copy multiple data from several workbook sheets into a specified existing sheets in the Result workbook

I have attached screenshot to show the visual representation of what I am trying to achieve. data 1&2 are the original data files, the result sheet is how I want my Master workbook to look like after the files have been copied.

https://i.stack.imgur.com/0G4lM.png

Zaraki Kenpachi Zaraki Kenpachi · Accepted Answer · 2019-08-02T07:02:18

Use pandas library for this:

import pandas as pd
import os

# collect files names
files_list = os.listdir('files_folder')

# collect data frames from each file
data_list = []
for file in files_list:
    df = pd.read_excel('files_folder/'+file)
    data_list.append(df)

# concat all data frames into one
result = pd.concat(data_list, sort=True)
result.to_excel('final_data.xlsx')

Copy & automate data from multiple workbook into an existing Master workbook without losing formatting using python

1 Answers