A common requirement is to import all files in a folder into Excel. These could be text files like .csv’s or even Excel files.
In this case a new option available is to use Excel’s PowerQuery tool (if you are not sure if you have it, visit the Where to find it page). This tool has an option to append a number of files within a folder onto each other to create a single data source.
As shown below (depending on the version this may look different- this is Excel 2016), you can:
- Click on the Data Ribbon
- Choose New Query
- Choose From File, and
- Choose From Folder
You then choose the folder that contains the files.
When you click OK, it will show you the list of files in the folder. You can click Load or Edit
When you enter the PowerQuery window, you will again see all the files stored in that folder. Note the icon on the first column and the button called Combine Binaries.
If you click either of these it will combine all the files into a single file. It will first give you the normal CSV options though so that you can specify how it is split.
On clicking OK it will perform the append and you will see a screen similar to the one below. Note the extra column for the Source name. This will be very useful if you ever want to break the file back into its component parts.
Done! You have imported all files in the folder into Excel
What’s great about this is, if more files are added to the folder, you just need to refresh and they will be pulled in automatically!
Related
How to export file names from a folder to excel
Easy fix for promoting headers when name can change issue in PowerQuery
Merge multiple csv files into a single file for use in Excel
Export folder and subfolder list to excel
Pin your regularly used Excel files
Recommended Course for importing all sorts of data into MS Excel