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

[@compton8301]

Thank you. Good job! :) Thanks for the content. With love, from South Africa.

[@CodingIsFun]

Thank you for watching the video & your comment. Greetings to South Africa :)

[@asankacool1]

Nice video flow design. Useful stuff…!

[@CodingIsFun]

Thank you!

[@sayantandatta2996]

u are amazing kudos to your effort❤👍 thanks

[@CodingIsFun]

Thanks for watching the video & your comment! 👍

[@Abramov_Andrey]

Отличный пример! спасибо!

[@CodingIsFun]

My pleasure! Appreciate you taking the time to watch and leave a comment.

[@K-mk6pc]

Good Insights.

[@CodingIsFun]

Glad you liked it. Thanks for watching. :)

[@JUN1ORLYONN]

Great video!

[@CodingIsFun]

*I am glad you liked the video; thanks for watching and for the comment!*

[@ahmadmojeed5507]

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

[@ahmadmojeed5507]

Would love to have same sheet in all excel files so i can add a drop-down in my master sheet.

[@CodingIsFun]

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.

[@tsiddiq23]

Great work

[@CodingIsFun]

Thank you! 😀

[@mikekaspari1357]

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

[@royalwinautomatedsoftware2985]

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...

[@CodingIsFun]

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!

[@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?

[@CodingIsFun]

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!

[@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?

[@CodingIsFun]

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! :)

[@sachinmahale6003]

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?

[@CodingIsFun]

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!

[@sachinmahale6003]

@@CodingIsFun Thank you very much Sir for clarifying my doubt. This helps me a lot.

[@gregorydubois8468]

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!

[@CodingIsFun]

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!

[@gregorydubois8468]

I totally understand, do not worry :-) I will dig into the net and try to find out something. I love your channel!

[@rosalyna_24]

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

[@CodingIsFun]

That is also possible. Check out the following videos: kzbin.info/www/bejne/qZmboXioiJdkqKc kzbin.info/www/bejne/hIinY3-JiNqsoKc Happy Coding!

[@rosalyna_24]

@@CodingIsFun thank you so much

[@Yoitsryn]

What if the worksheets have different names?

[@CodingIsFun]

Then you need to change the code ;)

[@akshayathalye264]

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

[@CodingIsFun]

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!

[@noeldoller2958]

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

[@CodingIsFun]

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!

[@noeldoller2958]

@@CodingIsFun Much appreciate sir. How can i support you sir in return

[@CodingIsFun]

​@@noeldoller2958 *Liking the videos is the best support.* ❤

[@noeldoller2958]

@@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

[@CodingIsFun]

@@noeldoller2958 Thank you for your support! 🦾

[@tanganhminh9138]

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 ?

[@CodingIsFun]

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!

[@tanganhminh9138]

@@CodingIsFun 1. this video, right ? kzbin.info/www/bejne/jnTZmKysd6udr68

[@CodingIsFun]

@@tanganhminh9138 yes

[@tanganhminh9138]

@@CodingIsFun 2. if you have time, hope you will do the above problem, ok?

[@CodingIsFun]

@@tanganhminh9138 you can join our Discord and ask for help there. Link is in the description

[@andrewwithers4971]

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 ?

[@CodingIsFun]

Which line is causing the error and did you modify the code from the tutorial?

[@andrewwithers4971]

@@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 ?

[@CodingIsFun]

@@andrewwithers4971 Hard to tell from a distance

[@andrewwithers4971]

@@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)

[@CodingIsFun]

@@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!

[@youngking7849]

hi can u help me

[@CodingIsFun]

Hi. With what?

[@ohmandy09]

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/'

[@CodingIsFun]

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!

[@ohmandy09]

@@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.