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

👉 Explore All My Excel Solutions: https://pythonandvba.com/solutions

𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
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]:
► https://pythonandvba.com/read_values_...

◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
GistHub: https://gist.github.com/Sven-Bo/b22af...
👩‍💻 𝗣𝘆𝘁𝗵𝗼𝗻 𝗖𝗼𝗱𝗲:
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:
report_date = excel_file.stem.replace("_Report", "")
wb = load_workbook(filename=excel_file, read_only=True)
rng = wb["Sheet1"]["B2":"B19"]
rng_values = []
for cells in rng:
for cell in cells:
rng_values.append(cell.value)
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
wb = load_workbook(filename="Masterfile_Template.xlsx")
for ws in wb.worksheets:
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:
if cell.value in values_excel_files:
Iterate over values (list inside the dictionary) and write values to column
for i, value in enumerate(values_excel_files[cell.value]):
cell.offset(row=0, column=i + 1).value = value
wb.save("Masterfile_Filled.xlsx")


𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): https://pythonandvba.com/mytoolbelt
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): https://pythonandvba.com/grafly
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): https://pythonandvba.com/cuteplots
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): https://pythonandvba.com/emojify
📑 Excel Templates: https://pythonandvba.com/go/excel-tem...
🎓 My Courses: https://pythonandvba.com/go/courses
📚 Books, Tools, and More: https://pythonandvba.com/resources


𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn:   / sven-bosau  
📸 Instagram:   / codingisfun_official  
💻 GitHub: https://github.com/Sven-Bo
💬 Discord: https://pythonandvba.com/discord
📬 Contact: https://pythonandvba.com/contact


☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here: https://pythonandvba.com/coffee-donation