Python: Copy Data From Multiple Files to Master File | Read/Write Closed Excel Files Using Openpyxl

𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
This tutorial will show you how to read & write closed Excel Files in Python using openpyxl. In particular, we will copy data from multiple Excel files in one Master workbook. After executing the Python script, openpyxl will create a new workbook that combines all the single Excel files.

✅ 𝗧𝗶𝗺𝗲𝘀𝘁𝗮𝗺𝗽𝘀:
00:00 | Problem Statement and Final Outcome
01:26 | Solution Approach
02:30 | Step 1
02:52 | Step 2
04:37 | Step 3
06:43 | Wrap Up

📝 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀:
Download the Python File & Excel Sample Files here [Google Drive]:

◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
👩‍💻 𝗣𝘆𝘁𝗵𝗼𝗻 𝗖𝗼𝗱𝗲:
from pathlib import Path # Standard Python Module
from openpyxl import load_workbook, Workbook # pip install openpyxl

# -- 1.STEP
# Get all excel file paths from given directory
SOURCE_DIR = "Daily_Reports" # e.g. r"C:\Users\Username\Desktop\Sample Files"
excel_files = list(Path(SOURCE_DIR).glob("*.xlsx"))

# -- 2.STEP:
# Iterate over all Excel files from step 1,
# access the worksheet and store the values in a dictionary
# values_excel_files = {['2021-01-01'] : [1,2,3, ..],
# ['2021-01-02'] : [1,2,3, ..]}
values_excel_files = {}
for excel_file in excel_files:
wb = load_workbook(filename=excel_file, read_only=True)
rng = wb["Sheet1"]["B2":"B19"]
rng_values = []
for cells in rng:
for cell in cells:
values_excel_files[report_date] = rng_values

# -- 3.STEP:
# a) Iterate over all worksheets in the master workbook
# b) For each worksheet, iterate over defined Excel range (dates)
# c) If date matches with the key of dictionary (values_excel_files) then insert values & save workbook
clm = "B"
first_row = 3
last_row = len(ws[clm])
rng = ws[f"{clm}{first_row}:{clm}{last_row}"]
for cells in rng:
for cell in cells:
# Iterate over values (list inside the dictionary) and write values to column

𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?

[compton]

Thank you. Good job! :) Thanks for the content.

With love, from South Africa.

[asankacool]

Nice video flow design. Useful stuff…!

[sayantandatta]

u are amazing kudos to your effort❤👍 thanks

[mikekaspari]

Hi Sven, danke fürs Video. Ist es möglich die Rows und Columns im Mastertemplate zu drehen, also das Datum entlang der Colums und die Produkte in A bis nach unten. Aber versucht den Code umzuschreiben, ging aber nicht

[ahmadmojeed]

Great tutorial again! 👌

Really helpful but in my case, i would like to create a new sheet for each excel files but sadly, it throws error.
Example,
wb =
wb.create_sheet("test")

Then, i get a TypeError : expected str... not list

[aquabestindian]

Thanks for the tutorial.
One problem, the data is not stored as values but are formulas, and the solution in the video is not fetching values !
How to modify the code to get values?

[royalwinautomatedsoftware]

Hai sir, ur video super. I learn so much ur channel. I have one doubt I'm using excel data python. Excel sheet cells I have created formula. Excel read data python program only copy formula not value. Please help me I need excel rows read values only. Please advise sir...

[marcelduplessis]

This may be a silly question, but I'm trying to add data from a excel file into a template without the date comparison. The idea is to grab everything from A2:K99. I can't seem to get this going :)

How can I modify this script to do this?

[sachinmahale]

This is really a great tutorial Sir.

I am new in this sector. I am trying to merge multiple excel files into one master excel file. The code runs well using pandas, but it removes all the formatting from master excel file. I saw this video and found that the formatting in the master excel file remains as it is. Is it because of the openpyxl package instead of pandas?

[gregorydubois]

Hi Sven, each of the files I need to combine into one workbook has a date in its title. I would like to get this title and copy it into a column I will create in each file so that at the end, I have all the rows with the titles from the files they have been copied from. How can I do it? Many thanks!

[akshayathalye]

How can we make the code work if the range within each workbook changes?

[rosalyna_]

but if i want to do the opposite, dividing the data from the master workbook to multiple excel file how should i proceed ??

[Yoitsryn]

What if the worksheets have different names?

[noeldoller]

Great tutorial Sir. I subscriber to your channel as there a lot of tutorial that would help me in my work.

Sir Im just started to learn python and I badly needed to learn because my work requires a lot of automation in excel. Appreciate sir if you can help me for one basic example. Need to automate copy and pasting range of cells from one multiple work book to Master excel file. Can you help me with to code copying range of data from column B2:B28 of sheet name "raw" of excel file name "paging" to paste in master excel file sheet name "CSSR" column AL2:AL28 and next day same source file to the next column AM2:MA28 and AN2:AN28 and soon. Thank you sir waiting for your help

[tanganhminh]

1. The interface to write python code, I see it seems to be localhost8888. but when i access it it is blocked, is there any way to solve this problem?
2. Extract all info of 1 video or 1 playlist or all videos of 1 youtube channel into excel file, use python can work ?

[andrewwithers]

Hi, I get the following error
SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated escape

Any idea how to get round this ?

[ohmandy]

Great video thanks for sharing, I just found this channel, do you happen to have a video on how to copy from multiple .xlsx source files just like yours but paste in multiple individual destination .xlsm files that are all in individual sub folders eg copy data from 20210101_Report.xlsx and paste in existing file 20210101_Sales.xlsm, copy data from 20210102_Report.xlsx and paste in existing file 20210102_Sales.xlsm where is 20210101_Sales.xslm is in a sub folder 'C:/folderpath/Jan1/' and 20210102_Sales.xlsm is in another sub folder 'C:/folderpath/Jan2/'