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]:
► https://pythonandvba.com/read_values_closed_excel_file

◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
GistHub: https://gist.github.com/Sven-Bo/b22afa37bb87ced28786caca50022428
?? ?????? ????:
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")
◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
??????? ???? ??:
? Website: https://pythonandvba.com
? GitHub: https://github.com/Sven-Bo
⭐ Discord: https://pythonandvba.com/discord
▶️ Subscribe: https://youtube.com/c/CodingIsFun?sub_confirmation=1

? ???? ????? ???-?? ?? ????? ???? ????????????
Get it here: https://pythonandvba.com/mytoolbelt

? ????????? ??? ???????? ??? & ??????
Check out my recommendations: https://pythonandvba.com/resources

☕ ??? ?? ? ??????❓
If you want to support this channel, you can buy me a coffee here:
https://pythonandvba.com/coffee-donation