Use Python To Combine Multiple Excel Files (only 13 lines of code!)

hello everyone this is jay from python in office welcome to another tutorial on using python to increase your work efficiency if you work with xl a lot like i do you must have had experience combining several worksheets into one file for example you might have some sales reports from different sources and your task is to combine all sales records into one place in this tutorial i'm combining only two files however technically you can combine as many files as you want a thousand or ten thousand is really up to you in these two sample files there are more than one tab that contain data and i want to get data from all the tabs from each file there are actually seven tabs in total and notice that in these sample files the sheets have the same structure or columns just different data inside our goal is to combine data from all these seven tabs into one excel table this is a super tedious job if we do it manually but we can use python to help out so we are going to need two python libraries os and pandas let's first store the folder path into a variable so that we can reuse it later the r string here is just to avoid any confusion that these backslashes might cause and we're going to start off by creating an empty data frame we can show all the files within this destination folder by using list dir which returns a list of file names in string now looking into this folder you'll notice that there are only two excel files and several other file types so all we care is these two excel files and we don't care about other files we can use a for loop to go through all the files and we'll perform a check if the file extension or the file type is an excel file so basically if the file name ends with dot excel sx then it means this is an excel file if yes then we're going to load the entire excel file into python can also find all the app names within this file and then loops through all tabs to load the data into appendix data free then we append the data frame this append operation basically just keeps adding new data to the bottom of existing data frame and the loop is done we can run it once the code is done running we'll save the aggregated data into a single excel file so now that's done let's take a look here we go this is the combined file essentially we should have all the items from those seven tabs that we were looking at let's check that quickly so how many different items do i have one two three four five six seven there you have it just a few lines of simple python code that can help you a lot if you need any clarification or have any questions feel free to leave a comment below and i'll respond as soon as i can if you find the video helpful and want to see more content like this please subscribe and click the like button it helps me a lot and i really appreciate it that's it for today i'll see you in the next tutorial you

Channel: Python In Office

Views: 13,782

Keywords:

Id: 9CuJlWDnTTg

Length: 4min 1sec (241 seconds)

Published: Thu Sep 09 2021