Thank you. Good job! :) Thanks for the content. With love, from South Africa.
@CodingIsFun3 жыл бұрын
Thank you for watching the video & your comment. Greetings to South Africa :)
@asankacool13 жыл бұрын
Nice video flow design. Useful stuff…!
@CodingIsFun3 жыл бұрын
Thank you!
@sayantandatta29962 жыл бұрын
u are amazing kudos to your effort❤👍 thanks
@CodingIsFun2 жыл бұрын
Thanks for watching the video & your comment! 👍
@Abramov_Andrey11 ай бұрын
Отличный пример! спасибо!
@CodingIsFun11 ай бұрын
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. :)
@JUN1ORLYONN3 жыл бұрын
Great video!
@CodingIsFun3 жыл бұрын
*I am glad you liked the video; thanks for watching and for the comment!*
@ahmadmojeed55072 жыл бұрын
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
@ahmadmojeed55072 жыл бұрын
Would love to have same sheet in all excel files so i can add a drop-down in my master sheet.
@CodingIsFun2 жыл бұрын
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.
@tsiddiq233 жыл бұрын
Great work
@CodingIsFun3 жыл бұрын
Thank you! 😀
@mikekaspari13572 жыл бұрын
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
@royalwinautomatedsoftware29852 жыл бұрын
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...
@CodingIsFun2 жыл бұрын
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!
@aquabestindian2 жыл бұрын
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!
@marcelduplessis2 жыл бұрын
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?
@CodingIsFun2 жыл бұрын
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! :)
@sachinmahale60032 жыл бұрын
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?
@CodingIsFun2 жыл бұрын
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!
@sachinmahale60032 жыл бұрын
@@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_242 жыл бұрын
but if i want to do the opposite , dividing the data from the master workbook to multiple excel file how should i proceed ??
@CodingIsFun2 жыл бұрын
That is also possible. Check out the following videos: kzbin.info/www/bejne/qZmboXioiJdkqKc kzbin.info/www/bejne/hIinY3-JiNqsoKc Happy Coding!
@rosalyna_242 жыл бұрын
@@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!
@noeldoller29582 жыл бұрын
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
@CodingIsFun2 жыл бұрын
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!
@noeldoller29582 жыл бұрын
@@CodingIsFun Much appreciate sir. How can i support you sir in return
@CodingIsFun2 жыл бұрын
@@noeldoller2958 *Liking the videos is the best support.* ❤
@noeldoller29582 жыл бұрын
@@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
@CodingIsFun2 жыл бұрын
@@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.