Import Multiple Csv Files Into One Excel Worksheet

WARNING!!! Mega POWER Ahead!

Note: When using the completed workbook, you will need to change the folder reference in the query to wherever you save the sample files. From the Query Editor, go to View > Advanced Editor and change the folder path.

Import pandas as pd writer = pd.ExcelWriter('yourfile.xlsx', engine='xlsxwriter') df = pd.readcsv('originalfile.csv') df.toexcel(writer, sheetname='sheetname') writer.save Since you have multiple csv files, you would probably want to read all csv files and store them as a df in a dict. Then write each df to Excel with a new sheet name. I recently needed to combined a large number of comma-separated values (CSV) files into a single XLSX file as part of a data analysis pipeline. To achieve this I employed PowerShell to combine the CSVs into separate worksheets within the one Microsoft Excel Workbook, which is saved as an XLSX file.

If you’ve ever come across a situation where you’ve had multiple files of data with each file having data spread across multiple sheets then you’ll want to read on. In this post we’re going to explore how to use the From Folder Power Query to import multiple files with multiple sheets in each file and aggregate the data into one table.

  • Merge multiple CSVs into one Excel file: folder option. If you want to combine a large number of CSV files into one Excel file, it’s more efficient to consolidate from a folder. Sheetgo lets you consolidate up to 80 files in one. It’s a time-saving option as there’s no need to select each source file individually.
  • Import or combine multiple xml/csv files into a sheet or a workbook with Kutools for Excel. If you are not familiar with VBA, do worry, here I introduce a handy tool – Kutools for Excel for you. With its powerful Combine utility, you can quickly combine multiple xml files or csv files into one workbook or one Excel sheet.
  • Now let me show you how you can import multiple CSV files into one Excel workbook, placing each.csv file on a separate sheet or consolidating all the files in a single spreadsheet. Import multiple CSV files into a single Excel workbook. To quickly import several.csv files into one workbook, we are going to use the Copy Sheets tool included.

In this example we have a series of sales files in a folder. Each file contains the sales for a given country and the files are named according to which country the sales data is from (i.e. Ireland.xlsx, England.xlsx, Luxembourg.xlsx, and Canada.xlsx). Each file has several sheets with different data in the same format. Each sheet contains the sales for a given sales person from the country and is named with the sales person’s name. As you could imagine, aggregating the data manually could be very time consuming as the number of files/sheets grows. This is where Power Query can shine.

Step 1: Create a From Folder query

Create a From Folder query.

  1. Go to the Power Query tab.
  2. Press the From File button.
  3. Select From Folder in the drop down menu.
  4. Select the folder path of the files you want to import.
  5. Press the OK button.

Check the preview data to ensure you it is the correct folder and files.

  1. A preview of the import data will appear. Check these are the correct files and folders.
  2. Press the Edit button.

Step 2: Remove data columns that aren’t needed

The From Folder query will include a lot of data such as file extension type, date modified, file location etc. that we do not need for our purposes. We can remove these to avoid clutter.

  1. Select the two columns we do need (Content and Name). Hold the Ctrl key and left click on the column headings to select them.
  2. Go to the Home tab.
  3. Press the Remove Columns button.
  4. Select Remove Other Columns from the drop down menu.
Excel

Step 3: Split the file name column

Import Multiple Csv Files Into One Excel Worksheet

To get the country into our data, we will need to parse the text in our file name. Since our file naming convention is pretty simple (Country Name.xlsx) we can use the split column function using a period as the delimiter.

  1. Highlight the file name column.
  2. Go to the Transform tab.
  3. Press the Split Column button.
  4. Choose By Delimiter in the drop down menu.
  5. Choose Custom.
  6. Enter a period for the delimiter.
  7. Choose At the right most delimiter. This will only split the file name text using the right most period found (ie just before the file extention xlsx).
  8. Press the OK button.

We can remove the resulting column containing the extension part of the split file name.

  1. Select the column.
  2. Go to the Home tab.
  3. Press the Remove Columns button.
  4. Select Remove Columns from the drop down menu.

Step 4: Add column for file content

Now we will need to add a column to bring our content into the query.

Import multiple csv files into one excel workbook
  1. Go to the Add Column tab.
  2. Press the Add Custom Column button.
  3. Name the new column something like GetFileData.
  4. Type this formula into the formula area.
  5. Press the OK button.

Expand the new column to show all the items in the Content.

  1. Press the small double arrow icon in the right hand side of the column heading.
  2. Select Expand.
  3. Check Use original column name as prefix.
  4. Press the OK button.

We also need to expand resulting Data column to show all its elements.

  1. Press the small double arrow icon in the right hand side of the column heading.
  2. Select Expand.
  3. Check Use original column name as prefix.
  4. Press the OK button.

We now have all the columns needed plus a few extra. Delete any columns you don’t need and rearrange the order of columns if desired by dragging and dropping columns.

Sheet

Power Query will guess the data type of each column, but you may need to correct these.

  1. Select the column you need to change the data type in.
  2. Go to the Home tab.
  3. Press Data type and select the data type from the drop down menu.

We were able to import all the data from multiple files and sheets into one table. We were also able to add in a country data column based on the file name and a sales person data column based on the sheet name. IF we add files to our folder or update data in a file, we can easily update our aggregated data by going into the Data tab and pressing the Refresh All button. Wow, Power Query can be very powerful!

You can do this too

Download 'The one essential add-in for all Excel users.'
“Working in Excel will be fun again with all these extra supersmart tools”
English (us) / Nederlands / Deutsch / Español / Français / Português do Brasil / Italiano / Русский / 中文(简体) / 日本語

Import » Merge and/or import multiple files...

This utility will merge multiple files from a folder together into one new file. You can also use it to import multiple files at once into your workbook.
Excel has no built-in method for merging files. It can therefore be time consuming and frustrating if you for example need to import or merge 200 Excel or CSV files. Fortunately you can use ASAP Utilities to do this quick and easy.
You can configure the following settings:

Import Multiple Csv Files Into One Excel Worksheet Vba


  • Start the import from a certain row, with the option to make an exception for the first file. This can be useful if you have files with fieldnames and only want these listed once.
  • Import the files into your current workbook or a new workbook.
  • Create a new worksheet for each file, or merge the data into one new sheet. With this second option the data from the files will be placed below each other.
    You can import/merge the following file formats:
  • CSV, comma delimited
  • Text, space delimited
  • Import multiple csv files into one excel worksheet free
  • Text, tab delimited
  • Text, pipe ( ) delimited
  • Text file, semicolon (;) delimited
  • Text file, delimited by specified character
  • dBASE
  • Excel*
  • Lotus 123*
  • Quattro Pro*
    * Only data from the active sheet is imported

    Import Multiple Csv Files Into One Excel Worksheet Pdf

    Your last used settings and chosen folder will be remembered.

    Screenshots

    Example screenshot: Import multiple files

    Starting this utility

    In the Excel menu choose: ASAP Utilities » Import » Merge and/or import multiple files...
    « Return to the list of “Import” tools.
    « Previous
    Insert text file (with options)...
    Next »
    Create a list of filenames and properties in a folder...

    You can do this too


    Download 'The one essential add-in for all Excel users.'
    Additional keywords for this tool:
    Concatenate, Combine, Directory, merge workbooks, combine workbooks, Merge files together in a new file, merge sheets, combines, consolidation