Python: Copy Data From Multiple Files to Master File | Read/Write Closed Excel Files Using Openpyxl
Vložit
- čas přidán 25. 07. 2024
- 👉 Explore All My Excel Solutions: 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]:
► pythonandvba.com/read_values_...
◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
GistHub: 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 (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): pythonandvba.com/mytoolbelt
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): pythonandvba.com/grafly
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): pythonandvba.com/cuteplots
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): pythonandvba.com/emojify
📑 Excel Templates: pythonandvba.com/go/excel-tem...
🎓 My Courses: pythonandvba.com/go/courses
📚 Books, Tools, and More: pythonandvba.com/resources
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn: / sven-bosau
📸 Instagram: / codingisfun_official
💻 GitHub: github.com/Sven-Bo
💬 Discord: pythonandvba.com/discord
📬 Contact: pythonandvba.com/contact
☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here: pythonandvba.com/coffee-donation
Thank you. Good job! :) Thanks for the content.
With love, from South Africa.
Thank you for watching the video & your comment. Greetings to South Africa :)
Nice video flow design. Useful stuff…!
Thank you!
Great video!
*I am glad you liked the video; thanks for watching and for the comment!*
u are amazing kudos to your effort❤👍 thanks
Thanks for watching the video & your comment! 👍
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
Great work
Thank you! 😀
Good Insights.
Glad you liked it. Thanks for watching. :)
Отличный пример! спасибо!
My pleasure! Appreciate you taking the time to watch and leave a comment.
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 = load_workbook(filename=excel_files)
wb.create_sheet("test")
Then,i get a TypeError : expected str... not list
Would love to have same sheet in all excel files so i can add a drop-down in my master sheet.
I am not entirely sure what you are trying to achieve, but 'excel_files' is a list of file paths. That is why you are getting an error.
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?
Thank you for watching the video. Your questions are great, but finding the time to answer them is difficult. Kindly understand that I receive many requests for individual solutions or modifications. That said, you might want to join our Discord server ( pythonandvba.com/discord ) and post your question there. Thanks for your understanding. Happy Coding!
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?
Thanks for watching. How about using 'xlwings' for this? Here is an example for you:
import xlwings as xw #pip install xlwings
source_wb = xw.Book('Source.xlsx')
target_wb = xw.Book('Target.xlsx')
sht_source = source_wb.sheets['Sheet1']
sht_source.range('A2:K99').copy()
sht_target = target_wb.sheets['Sheet1']
sht_target.range('A2').paste()
I hope this helps! :)
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?
Thanks for watching the video and your question. Yes, that is correct. In fact, pandas is also using openpyxl under the hood. Yet, when creating a pandas dataframe, pandas will remove the formatting. If you use openpyxl directly, you could keep the current format of the excel file. Hope this clarifies. Happy Coding!
@@CodingIsFun Thank you very much Sir for clarifying my doubt. This helps me a lot.
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...
I am unsure what you are trying to achieve, but you might want to use openpyxl.
Please find below an example:
from pathlib import Path
import openpyxl
excel_file = Path(r"C:\Users\UserName\Desktop\data.xlsx")
wb = openpyxl.load_workbook(excel_file, data_only=True)
Happy Coding!
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!
Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!
I totally understand, do not worry :-) I will dig into the net and try to find out something.
I love your channel!
How can we make the code work if the range within each workbook changes?
Thanks so much for watching the video and leaving a comment! Your request is definitely noted. However, I get a ton of requests for custom solutions and, as much as I'd love to help everyone out, I just don't have the time in my schedule to develop and test all of them. I hope you can understand. Happy Coding!
but if i want to do the opposite , dividing the data from the master workbook to multiple excel file how should i proceed ??
That is also possible. Check out the following videos:
czcams.com/video/sh8pErS21rE/video.html
czcams.com/video/NWD2LSSuyjE/video.html
Happy Coding!
@@CodingIsFun thank you so much
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
Thanks for watching the video.
*Here is the code snippet:*
import xlwings as xw #pip install xlwings
wb_source = xw.Book('paging.xlsx')
wb_master = xw.Book('master.xlsx')
sht_source = wb_source.sheets['raw']
sht_master = wb_master.sheets['CSSR']
source_values = sht_source.range('B2:B28').value
sht_master.range('AL2').options(transpose=True).value = source_values
I left out the column change for each day (AM, AN, AO, ...) as this might be a good learning project for you :)
Hope this helps & happy coding!
@@CodingIsFun Much appreciate sir. How can i support you sir in return
@@noeldoller2958 *Liking the videos is the best support.* ❤
@@CodingIsFunYes sir, all your videos are worth liking and I already liked them all as I already compared it with similar videos from other programming experts. In fact Im always looking for your new videos. The latest I watched and liked was the Pandas short cut
@@noeldoller2958 Thank you for your support! 🦾
What if the worksheets have different names?
Then you need to change the code ;)
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 ?
1. I am using a Jupyter Notebook. Set up guide can be found on my channel
2. Yes, it's possible, but I cannot explain all the steps in a single comment.
Happy Coding!
@@CodingIsFun 1. this video, right ? czcams.com/video/XCvgyvBFjyM/video.html
@@tanganhminh9138 yes
@@CodingIsFun 2. if you have time, hope you will do the above problem, ok?
@@tanganhminh9138 you can join our Discord and ask for help there. Link is in the description
Hi, I get the following error
SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape
Any idea how to get round this ?
Which line is causing the error and did you modify the code from the tutorial?
@@CodingIsFun I managed to fix the error. the code seems to work now however the new file "Masterfile_Filled" is empty any ideas why this could be ?
@@andrewwithers4971 Hard to tell from a distance
@@CodingIsFun I now have the code working how I need it however I would like use different file names. is there a way to take the file names in the directory and add them to column "B" ?
I tried this -
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 = "excel sheets" # 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 = {['Project Costs File 1'] : [1,2,3, ..],
# ['Project Costs File 2'] : [1,2,3, ..]}
values_excel_files = {}
for excel_file in excel_files:
report_date = excel_file.stem.replace("_Report", "") #** This was the replace file name
wb = load_workbook(filename=excel_file, read_only=True)
#print(report_date)
#######select cells from sheet
rng1 = wb["Sheet1"]["B2":"B2"]
rng2 = wb["Sheet1"]["B4":"B4"]
rng3= wb["Sheet1"]["B6":"B6"]
rng4=wb["Sheet1"]["B6":"B6"]
rng =rng1+rng2+rng3+rng4
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:
ws.write(B3, report_date)
@@andrewwithers4971 Your questions are great, but finding the time to answer them is difficult. Kindly understand that I receive many requests for individual solutions or modifications. That said, you might want to join our Discord server (pythonandvba.com/discord ) and post your question there. Thanks for your understanding. Happy Coding!
hi can u help me
Hi. With what?
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/'
Thank you for watching the video. Your questions are great, but finding the time to answer them is difficult. Kindly understand that I receive many requests for individual solutions or modifications. That said, you might want to join our Discord server ( pythonandvba.com/discord ) and post your question there. Thanks for your understanding. Happy Coding!
@@CodingIsFun Thank You! I figured it out. Your video got me 1/4th of the way there, your discord server got me 3/4 of the way there, google and stack got me the rest of the way. I just bought you coffee please keep up the good work, my Thank You is all the way from Atlanta Georgia USA.