Excel Power Trick – How to Combine a Folder of CSV Files
I previously published an article and video ‘Excel Power Trick – Save time without VBA’ which seems to have been very popular. In this video we looked at using Power Query to get data from Excel sheets, format that data and then save the query for future use. It’s amazing how much time you can save with this trick.
Following on from that article, I have another Power trick that will save you so so so much time.
When I was employed in finance and accounting I always worked with CVS files. The truth be known, no matter how expensive the software package, us accountants tend to dump data into Excel for further reporting. Once we imported the data and did some formatting, we can then prepare our reports or collect key figures.
The Scenario
You are group accountant for a retailer. You have a few stores and sell a number of products. On the first of every month, the store manager runs a very simple sales report. A report you need for preliminary monthly reporting. But the report comes in CSV format….( I bet you seen that one coming!) So you have to spend time formatting and combining the reports in Excel to get the metrics you need. Sounds familiar right!
In this week’s video we are going to look at the combine binary’s feature for CSV files. Combine binary’s is a fancy name for combining files in a folder. It’s a really really powerful tool that will increase your efficiency by saving you loads of time without the need to know VBA macros.
Taking the scenario above, we are going to combine all the data together from each CVS file into one report. Once you have done this and saved the file, next month you won’t need to carry out the steps again. All you have to do is add the CSV files to the folder and refresh the data in the Excel spreadsheet.
So if you want an Excel Power Trick that will really impress others and save you loads of time in the process then have a look at this video. And if you want to try it out yourself, you can download the data here (don’t forget to unzip the folders first).
By the way, if this feature is not available don’t worry. This is a new feature and it is free. All you have to do is ensure you have updated the Power Query add in. Check out the Microsoft website to find out more