Import Data From a Folder with Multiple Files (Excel)

Posted on by Summa Lai

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010

Use Power Query to combine multiple files with the same schema stored in a single folder into one table. For example, each month you want to combine budget workbooks from multiple departments, where the columns are the same, but the number of rows and values differ in each workbook. Once you set it up, you can apply additional transformations as you would with any single imported data source and then refresh the data to see results for each month.  

A conceptual overview of Combining folder files

Note    This topic shows how to combine files from a folder. You can also combine files stored in SharePoint, Azure Blob Storage, and Azure Data Lake Storage. The process is similar.

Before you begin

Keep it simple:

  • Ensure that all the files you want to combine are contained in a dedicated folder without extraneous files. Otherwise, all files in the folder and any subfolders you select are included in the data to be combined.
  • Each file should have the same schema with consistent column headers, data types, and number of columns. The columns do not have to be in the same order as the matching is done by column names.
  • If possible, avoid unrelated data objects for data sources that can have more than one data object, such as a JSON file, an Excel workbook, or Access database.

Import from text, CSV, or XML files

Each of these files follow a simple pattern, only one table of data in each file.

  1. Select Data > Get Data > From File > From Folder. The Browse dialog box appears.
  2. Locate the folder containing the files you want to combine.
  3. A list of the files in the folder appears in the <Folder path> dialog box. Verify that all the files you want are listed.

    An example text import dialog box
  4. Select one of the commands at the bottom of the dialog box, for example  Combine > Combine & Load.
  5. If you select any Combine command, The Combine Files dialog box appears. To change file settings, select each file from the Sample File box, set the File Origin, Delimiter, and Data Type Detection as desired. You can also select or clear the Skip files with errors checkbox at the bottom of the dialog box.
  6. Select OK.

Result

Power Query automatically creates queries to consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose.

Import from JSON

  1. Select Data > Get Data > From File > From Folder. The Browse dialog box appears.
  2. Locate the folder containing the files you want to combine.
  3. A list of the files in the folder appears in the <Folder path> dialog box. Verify that all the files you want are listed.
  4. Select one of the commands at the bottom of the dialog box, for example  Combine > Combine & Transform.

    The Power Query Editor appears.
  5. The Value column is a structured List column. Select the Expand  Expand column icon icon, and then select Expand to New rows. 

    Expanding a JSON List
  6. The Value column is now a structured Record column. Select the Expand  Expand column icon icon. A drop-down dialog box appears.

    Expanding a JSON Record
  7. Keep all the columns selected. You may want to clear the Use original column name as a prefix check box. Select OK.
  8. Select all the columns that contain data values. Select Home, the arrow next to Remove Columns, and then select Remove Other Columns.
  9. Select Home > Close & Load.

Result

Power Query automatically creates queries to consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose. 

Import from Excel or Access

Each of these data sources can have more than one object to import. An Excel workbook can have multiple worksheets, Excel tables, or named ranges. An Access database can have multiple tables and queries. 

  1. Select Data > Get Data > From File > From Folder. The Browse dialog box appears.
  2. Locate the folder containing the files you want to combine.
  3. A list of the files in the folder appears in the <Folder path> dialog box. Verify that all the files you want are listed.
  4. Select one of the commands at the bottom of the dialog box, for example  Combine > Combine & Load.
  5. In the Combine Files dialog box:
    • In the Sample File box, select a file to use as sample data used to create the queries. You can either not select an object or select just one object. But, you can’t select more than one.
    • If you have many objects, use the Search box to locate an object or the Display Options along with the Refresh button to filter the list.
    • Select or clear the Skip files with errors checkbox at the bottom of the dialog box.
  6. Select OK.

Result

Power Query automatically creates a query to consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose. 

Use the Combine Files command

For more flexibility, you can explicitly combine files in the Power Query Editor by using the Combine Files command. Let’s say the source folder has a mixture of file types and subfolders, and you want to target specific files with the same file type and schema but not others. This can improve performance and help simplify your transformations.

  1. Select Data > Get Data > From File > From Folder. The Browse dialog box appears.
  2. Locate the folder containing the files you want to combine, and then select Open.
  3. A list of all the files in the folder and subfolders appears in the <Folder path> dialog box. Verify that all the files you want are listed.
  4. Select Transform Data at the bottom. The Power Query Editor opens and displays all the files in the folder and any subfolders.
  5. To select the files you want, filter columns, such as Extension or Folder Path.
  6. To combine the files into single table, select the Content column that contains each Binary (usually the first column), and then select Home > Combine Files. The Combine Files dialog box appears.
  7. Power Query analyzes an example file, by default the first file in the list, to use the correct connector and identify matching columns.

    To use a different file for the example file, select it from the Sample File drop-down list.
  8. Optionally, at the bottom, select Skip files with errors to exclude those files from the result.
  9. Select OK.

Result

Power Query automatically creates a queries to consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose.

Ref: https://support.microsoft.com/en-us/office/import-data-from-a-folder-with-multiple-files-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4