14 - Combine CSV Files (or Text Files) in a Folder Using Power Query

hello and welcome to the video by Trump Excel I'm so mad pencil and in this video I'm going to show you how to combine multiple CSV files or text files from a folder using power query so here I have a folder with the name sales data and I have different files here I have the CSV files I have the Excel files I have the text files and what I want to do is I want to combine all these CSV files into one consolidated report so to do that what I'm going to do is I'm going to connect power query to this folder and get these CSV files and combine these files so to do that I would go back to the workbook here I would go to the data tab click on get data from file from folder now I would browse the folder and give the path the folder path here so I have to click on browse drag down click sales data because that is the folder that has my files click OK click OK here and now it opens the preview and it shows me all the file names now I have the option to combine these files for using this button but because I have multiple extensions and I only want to combine CSV files I am not going to reuse this rather I would click on edit this would open the query editor and put these files in the query editor now I have these columns I have the content name the extension and I have the metadata which is date accessed and date modified and created and so on what I need to do first is I need to filter by extension so that I only have the CSV file so I would come here click on select all and then check the CSV option and click OK now if you have a folder that only has CSV files it still a good practice to filter these file types and select CSV because in future if you add an excel file to it or text file to it power query would know that it needs to ignore those files and only select the CSV files so in case you only have CSV files you click on this go to text filter equals and then you can type dot CSV here now oops remove this step now that I have CSV files filter what I need to do is I'll go to the content column here and see what happens this is binary and binary would mean that this single cell holds an entire file so whenever you see binary it would mean that it's holding a file and these four cells together hold four finds the first one if I click on the space to the right of binary here you can see it says East data of CSV if I click on the white space here it says not greater dot CSV so these four files are these work cells are holding four files and if I hover my mouse over this icon which is two arrows pointing downwards it says combine files and if I click on it it would combine all these files in this column and it will also get rid of all these other columns except the name column so see what happens when I click on this it will show me the combined files preview pane here I can see the data from one of the files which is the first file and now when I click OK it opens these files it combined these files and give me the data here you can see there are the number of steps have been applied here and these are quite complicated but you don't need to worry about it because power query takes care of everything what you need to do is make sure that the data is right so you can see and you can check whether the data types are correct or not so in this case it's date if it's correct or not if you want to change this you can change this into using locale you can see that these are text data type if you go to transform you can see the text these are whole numbers the whole number and this is a commission this is percentage also in source name it says East data dot CSV but I don't want this to be this long name I just want this to be east or west or north or south so the first thing I would do is rename this column to a more meaningful name which is region and then I would right click on it go to replace values and Here I am type space data dot CSV and I would replace it with nothing so when I click OK it will remove data dot CSV part and it will only give me the region which is east west north or south now my data is all set I can go to Home close and load and this would insert the new worksheet here and place all the data in an Excel table here in the workbook and you can see it says 1403 rows loaded so what it has done here is we connected power query to a folder that folder had for CSV files so it selected those for CSV files out of the entire set of files and it combined all those files and at the same time it also gave us the region part here which is the file name now if I add a new file to it let's say I add Midwest data here so now I have five CSV files and I go back to my data set all I need to do is simply refresh this query and the power and power query would go back to the folder it will combine the five files and give me the combined data here see it says 1403 rows loaded when I come here and I click on refresh or you can go to the query and you can refresh from here as well see what happens it says 1844 rows loaded because now those additional rows have been added here so this is how you can quickly combine CSV files in a folder the same process is also followed for the text file so you just need to follow the same steps instead of CSV files you can filter the text file and you can combine it in the same fashion that's it in this video I hope you found this useful thank you and have a nice day

Channel: TrumpExcel

Views: 47,650

Keywords: combine csv files power query, power query combine csv files, power query combine csv files in a folder, power query combine text files in a folder, combine text files, combine text files into one excel, combine multiple csv files into one excel workbook, advanced excel, excel power query 2016, excel power query training, free power query course, free power query training, get and tranform, power query excel, sumit bansal, trumpexcel, power query

Id: K-nal4mlVq8

Length: 6min 11sec (371 seconds)

Published: Thu Apr 02 2020