Combine Excel Files with Python | Beginner Friendly | Excel Python Automate with Pandas

[@truecolorvibe]

Thank You, Laura, for this great tutorial ! No fluff and to the point - keep it up

[@thefriendlycoder_]

This makes me happy! Thank you :)

[@MrJohn2brown]

Amazing , this saves me a ton of repetitive copy pasting every Monday. Cant wait for more vids. Many thanks

[@thefriendlycoder_]

Thank you so much!

[@Oostenwald-s5q]

You made this look very easy. Please, please, please make more of these Python videos. I extract information from 18 different Excel files and consolidate the relevant data from each file to create a report on a single Excel sheet. There must be an easier way to accomplish this, as my laptop struggles to execute all the commands and formulas I use.

[@thefriendlycoder_]

Thank you! Great idea

[@cdisla98]

Really needed to see this tutorial!! thank you so much for this!!

[@michaelcrain8926]

I am just now learning python and this is just what I needed to start with. Thank you for making this.

[@thefriendlycoder_]

Thank you!

[@SalehGoodarzian]

I appreciate your helpful, concise, and very friendly video. It helped me a lot. I had spent hours to find a very short way to import all excel/csv files. Your video showed me the way. I really appreciate and I wish you all the best...I share what I did in only 5 lines, I can't thank you more (please note my code is saved in the folder with csv files...). files = glob.glob('*.csv') data = pd.DataFrame() for file in files: current = pd.read_csv(file) data = pd.concat([data, current], axis=0)

[@akanobody5622]

Perfect, thanx I have to do many repetitive excel sheet cleaning and this tutorial is very helpful for me, I just hope this can handle the amounth of data I work with.

[@thefriendlycoder_]

This makes me so happy! I hope it works for you

[@HartsoBig]

This was great! However, going to your channel only to see one upload was not! More videos! I would love to see you continue this sleep data project and automate turning the data into visuals. Thanks!

[@bleharris4702]

You're a star, very simple and to the point tutorial. Thank you.

[@thefriendlycoder_]

Thank you ✨✨✨

[@deepakhaldar2983]

keep upload new videos, you have just saved my hours by this. Thankyou. Can you please make a video on sharing Auto Mail to different stakeholders with attachement of their mentioned data.

[@thefriendlycoder_]

Thank you! A great suggestion :)

[@binarystar4947]

Welcome to you tube :) and wanted to see more of the videos related to pandas tips and tricks

[@patrickwheeler7107]

Thanks for putting this together! Would love to see you add some logic to look for last row of an empty data set and omit the rest of the rows, like foot notes from some reporting systems. EIther way this was very helpful!

[@thefriendlycoder_]

Thanks Patrick, good idea!

[@patrickwheeler7107]

@@thefriendlycoder_ Thanks for replying. Here IMO would be a fantastic project to run through. You have several files. All the same column length. a couple of real world examples I run into. As mentioned looking for the last record, maybe blank, or a specific word or length of word. Delete a column or columns Delete a row Rename missing values to something to group them correct in a column. I am mainly an SQL developer and use some VBA when unable to use our ETL tool so learning Python is really helpful for me. Thanks!

[@robertspalding5635]

I am only a beginner but this was incredibly cool and so are you. thank you.

[@thefriendlycoder_]

Thank you!

[@noahinaus]

Laura, you seem like a night owl! XD Thanks for the great tutorial and I really like your energy all along 😁

[@sirajmultani5724]

Thank You Laura, it really helped me and saved hours of work. I really appreciate! please keep up the good work !!!

[@thefriendlycoder_]

Thank you Siraj!

[@MuhammadAdeelAhmed]

Thank you Laura, something I learnt today, very well explained, excellent work

[@thefriendlycoder_]

Thank you!!

[@rikardoledal2270]

Thank you!! I love excel and are now learning to code Python. It was a great video. I'm subscribing :)

[@thefriendlycoder_]

I love to hear that! thank you :)

[@jenniferdios]

pandas is so awesome. Need to get more familiar with it. Thanks for this tutorial!!

[@johnobiomah22]

Thank you. This worked perfectly for what I needed it for

[@call-me-potato.]

thanks madam for such a nice tutorial , I just started learning programming. no previous back ground . kindly please make more easy videos and please increase size of your screen its very small. and wall paper on your screen is amazing. kindly any one who is reading my comments , advice me on python , what things should i learn initially.

[@thefriendlycoder_]

Thank you so much! Will take all of this into consideration.

[@user-lp1iu4eo5s]

Explained it very clearly.... Thank u 😊

[@LanceToledo]

So proud of you Laura!!!

[@thefriendlycoder_]

Thanks Lance :')

[@zeinplayy]

Best tutorial ever, thanks🎉

[@SuperNunera]

Gonna use that today ! Ty for sharing !

[@anujsaxena8268]

Really Appreciable, Much helpful 💕 Please do make more videos on Numpy and Pandas facing much errors in this

[@thefriendlycoder_]

Thanks for the encouragement Anuj!

[@anujsaxena8268]

@@thefriendlycoder_ Most Welcome

[@shubhamgade7180]

Thanks for the content!! If possible can you make a tutorial on joining 2 excel sheets on basis of one common column between the two files? So as the values in second excel with join the values in first excel.

[@noeldoller2958]

great work Laura. Please do more excel automation specially showing how to copy range of cells from multiple excel files to a single master file, please

[@theshuffle0409]

Great explanation Laura...

[@miladghalehnoei8300]

it was very helpfull for me. thank you so mach

[@ngrunoz]

Thank You Laura for the great tutorial. I have a scenario were I would like to combine only sheet with same name from various workbooks and either write each sheet for similar sheet names or a concatenated csv file for similar sheet names.

[@michaelventarola7100]

Liked your tutorial. Would like to do lookups from one sheet to another. That would be a great tutorial.

[@lordprocessor7253]

You leave me no choice….”subscribed”

[@thefriendlycoder_]

😂😂😂 thank you!

[@temukaify]

Really nice. I wish I also had a video how to delete all the rows that I don't need and combine files in a column fashion - join by columns.

[@temukaify]

+1 request - Please zoom in on codes, because it's difficult when they're so tiny.

[@thefriendlycoder_]

Thanks for the feedback :)

[@terrytolbert3098]

i am a heavy VBA (Excel/ Access/PowerPoint) user and always wondered how Python could also do the same things. can you also please show me how Python can do these. - auto find the last row/ column of data, if the data has gaps. - find a sheet in a excel workbook find (multi sheets, and you only want data from 1).

[@miltondiaz2531]

Hola Laura, tu video hizo que resuma mi codigo a de mas de 10 filas a 4. Saludos desde Perú. Gracias

[@thefriendlycoder_]

Gracias! ☺️

[@rmscrisostomo]

Thank you so much, this is a very good tutorial! Congratulations!

[@thefriendlycoder_]

Thank you! :)

[@gmagcaling]

Very helpful!

[@thefriendlycoder_]

Thank you!

[@shivkay]

Can you do a video on how to update one worksheet with another one…. And also to refresh all pivots that are linked to the first sheet

[@deepanshusingh6057]

Beautiful Content

[@thefriendlycoder_]

Thank you! :)

[@dalixolombuyane5119]

Love it. Thank you

[@utkarshtripathi9118]

U Healed me alot Thank U Thank u so much

[@sureshn3951]

Hey Laura, This tutorial is very Helpful. Can you please do another video on how to combine cols by cols. thanks

[@iceiceisaac]

Nice, please keep posting

[@thefriendlycoder_]

Thank you!

[@fafa5841]

thank you so much. can you make a video on how to read specific cell value/column/ raw from multiple workbook or sheets and combine it into a new excel file?

[@thegracegong]

thanks queen

[@thefriendlycoder_]

💓

[@jessesmith2250]

when running the script at 6:31 I get the following error message: AttributeError(f"module 'pandas' has no attribute '{name}'")

[@mhmoudkhadija3839]

Thanks! This tutorial is really useful..

[@thefriendlycoder_]

Thank you!

[@cdlowe3]

This was very helpful - Thanks for putting this together. How would you format the cells?

[@sravankumar1767]

THANKS 😊 NICE EXPLANATION 👌 👍

[@PONCEJE]

You are fantastic thank you for video.

[@thefriendlycoder_]

Thank you!!

[@PONCEJE]

Waiting for more videos.

[@Frank-si2jd]

Hi Laura, thanks for your very well done tutorial and using this helpful example! Question though; I understand having Python and adding/including libraries like Pandas and Glob and the necessity to have them installed on your computer to be able to program. But what if I hand this compiled executable program over to a friend, does he needs to have Python, Glob and Pandas installed on his computer? Or is running the executable enough for him? I do understand I also need to fix the location (path) of his Excel file on his computer in the script.

[@aleksandrekacharava2318]

Thank you for the great info! One more question: can we keep cells' format while combining the files? For example, if there is some color in certain files' cells and we need to keep them in those colors. Thank you.

[@gloriachica986]

Thank you Laura

[@sravankumar1767]

How can we convert each xlsx sheet as single csv to load to destination. Can you please explain this scenario

[@torque6389]

Great video, thank you!

[@thefriendlycoder_]

Thank you!

[@arielmoraes9960]

Wow great video!!!

[@anselmeouohi7914]

Really good tutorial ! How about if we want to compare data in dataframe before concatenate?

[@TajMikel]

Why no more tutorials? This one was really helpful!

[@thefriendlycoder_]

Thank you for this comment! I posted another video

[@marceloestrada1934]

thanks Laura! amazing tutorial! I have many excel files but with different headers. How can I match or concatenate headers with similar names or a condition that include one word for each column in each file?

[@siyakholwakhiphi1556]

That's great staff, I emulated the code and it worked only on your spreadsheets but on the ones I want to use, kind to explain what could be the problem? Thanks in advance

[@hirengajjar4159]

i want to marge multiple excel, all excel have common 3 sheets. please help to get code for the same, i want all data merge in new blank excel sheet wise

[@blankseventydrei]

nice work and I learned alot, and will put it to use, but why do this in python when it can be done with VBA? what would be the advantage?

[@ahmedabdelaziem4497]

Many thanks

[@neeljain5169]

Thanks for the great tutorial ! I am trying to combine two excel files using your code. But its not reading the hyperlink values and I am getting N/A in that column. Could u pls suggest what changes can be done to have the hyperlink values as well.

[@bluchips2055]

That was good, thanks.

[@tranngocquynh94]

Hi Laura, I have just following your tutorial. However, i have duplicate headlines that need to be removed, can you share how can we delete it? Thank you so much

[@cuneytozkurt4867]

Hey, it's really great!!!

[@kudakwashejena1667]

Thank you for this. Can I combine sheets into one if I’m dealing with more than 3million rows of data?

[@nasserabdal523]

Thank you very much on this explan But i ask about copy of format excel file as color and bolt text for each file when i combine in one

[@thefriendlycoder_]

Hi! For formatting (e.g, adding color, making text bold) I suggest using the library openpyxl instead of pandas.

[@mariachica6913]

Thanks, I will try ❤️

[@kabirgive9356]

Really helpfull could you please tell me could I append each excel file and then save all the data generated into a single excel file please it would be really helpfull

[@mattviverette]

Can you think of a way to reorder the sheets so that the month names are in the correct chronological order without numbering the month names?

[@ryanrumsey2830]

Hello Laura .. loved your video. Can you do a video on combining data from multiple excel files using a "Vlookup" to search for certain data?

[@thefriendlycoder_]

Good idea thank you!

[@akhgh07]

really helpful thank you

[@thefriendlycoder_]

Thank you!

[@beginho2454]

Nice and pretty teacher.

[@d515144d]

Thank You

[@kamrank680]

Thanks!! It helped me

[@thefriendlycoder_]

That's awesome! Thanks for the feedback :)

[@samahmedalone6573]

Thanks, 👍👍👍

[@Karimahmedsaleh]

I`d like to thank you for your great video, but I also tried and didn`t work with me, I don`t know what`s the reason

[@sainbayarzorig]

thans you very much

[@williamstanes1381]

Hello Laura , i have a doubt about combining different files to single files but in a different sheet

[@williamstanes1381]

Is there any platform so i can post my query and you can help me with it

[@aseemtripathi3555]

How Laura can you please guide me how to fetch only current date excel files from specific time frame

[@gloriachica986]

Wow ,Thanks 😘🕊🙏🏻

[@allanrasmussen5789]

it's fine what you're doing, but you need to set the font size up

[@evelic]

Hum, interesting, Python is blazing fast to process all this stuff.

[@fernandoortiz6069]

great job

[@thefriendlycoder_]

Thank you!

[@bluchips2055]

If you could, please be encouraged to put in some SUMIFS and VLOOKUP tutorials on python.

[@Saurabhdeepsinghdeopuri]

i cannot be able to run at 6.45, error show (warning: Workbook contains no default style, apply openpyxl's default) help[ me to resolve issue

[@thefriendlycoder_]

Hey Saurabhdeep, does the code run before 6.45 for you?

[@mohamadhabibhomeidi5634]

Great....

[@FahmiFashaApridiansyah]

I always get ValueError: Invalid file path or buffer object type:

[@Smolenishev]

Thanks!

[@thefriendlycoder_]

Thank you!

[@heythemhamlaoui2392]

thank you

[@jonelatendido9836]

Could I use Visual Studio, Instead of IDE?

[@thefriendlycoder_]

Yes! Visual studio is a type of IDE

[@pankajgaurr]

How to do it for csv files??????

[@thefriendlycoder_]

1) change the extension from .xls or .xlsx to .csv 2) change pd.read_excel to pd.read_csv 3) change .to_excel to .to_csv

[@karthikrajamony6044]

getting error as "ValueError: Excel file format cannot be determined, you must specify an engine manually."

[@thefriendlycoder_]

Hey! Are all your excels closed?

[@karthikrajamony6044]

@@thefriendlycoder_ yes, all are closer

[@nick-oi1xf]

It might not be an xls file, it could be an xlm file, even though the extension is xls. Open it as a text file and check.

[@komangsengku133]

thanks

[@alikhanak123]

Hi can you make a video on this scenario Multiple files with multple sheets, merged to one file, with all sheets in thoes multple files. If sheet name in all files are same data should concat.

[@ngrunoz]

Hi Randoms. Did you manage to find solution on this one. I am also trying to find solution on it.

[@alikhanak123]

@@ngrunoz half way... I wanted, if File 1 2 & 3 all have sheets A B C.. And output file should have Sheets A B C with all data from files 1 2 &3 under respective sheets name

[@ngrunoz]

@@alikhanak123 that is great. It looks like the exact same solution I am looking for. If you comfortable sharing the solution you have.

[@alikhanak123]

@@ngrunoz I will share the work I have done half way, in a while. It is not successful, may be you can make it successful.

[@shreya4676]

How to convert csv files into one excel sheet in different tabs ? Please help

[@umara941]

Do you still need help with your csv files?

[@shreya4676]

@@umara941 Yes, please If you can.

[@umara941]

@@shreya4676 okay, let's discuss it. Do you have email, discord or telegram that we can use to discuss it and share files?

[@robertreed4337]

excellent +++++++

[@thefriendlycoder_]

Thank you!!

[@atangbingana283]

Hey did you give up ? keep going ✊❤

[@thefriendlycoder_]

Thank you for the encouragement! I just posted another video!

[@atangbingana283]

Thanks for coming out of retirement - time to build 👊🏽 @@thefriendlycoder_