How To Import Multiple CSV Files into One Excel Workbook

This post will introduce three methods for importing multiple CSV files into one Excel workbook.

  • The first method involves using VBA code to automate the process of importing CSV files.
  • The second method involves using Power Query, a data connection technology that allows you to connect to and import data from various sources.
  • This third method can be used to easily combine multiple CSV files into one Excel worksheet using the command prompt.

Those methods can save time and effort when working with multiple CSV files in Excel.

Import or Merge Multiple CSV Files into Separate Worksheet Using VBA Code

If you try to import or merge multiple CSV files manually into separate worksheets, it should be time-consuming and tedious. You can use VBA code to automate the process to save your time and effort.

To import or merge multiple CSV files into separate worksheets using VBA code, just follow the steps below:

Step1: Open the Excel workbook where you want to import or merge the CSV files.

How to Import Multiple CSV Files into One Excel Workbook vba1.png

Step2: Press Alt+F11 to open the Visual Basic Editor.

How to Import Multiple CSV Files into One Excel Workbook vba2

Step3: In the Visual Basic Editor, select Insert > Module to create a new module.

How to Import Multiple CSV Files into One Excel Workbook vba3.png

Step4: Copy and paste the below VBA code into the new module.

Sub ImportCSVFiles_excelgeek ()
    Dim FolderPath As String, Filename As String
    Dim Sheet As Worksheet, NextRow As Long
    Dim FilePicker As FileDialog

    ' Show the File Picker dialog box to select the folder containing the CSV files
    Set FilePicker = Application.FileDialog(msoFileDialogFolderPicker)
    FilePicker.AllowMultiSelect = False
    FilePicker.Title = "Select the folder containing the CSV files"
    If FilePicker.Show <> -1 Then Exit Sub
        FolderPath = FilePicker.SelectedItems(1)
        ' Loop through all CSV files in the folder
        Filename = Dir(FolderPath & "\*.csv")
        Do While Filename <> ""
            ' Open the CSV file
            Workbooks.Open Filename:=FolderPath & "\" & Filename, ReadOnly:=True
            ' Copy the data to a new worksheet
            Set Sheet = ThisWorkbook.Worksheets.Add(After:= _
                  ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            Sheet.Name = Left(Filename, Len(Filename) - 4)
            Range("A1").Select
            Selection.CurrentRegion.Select
            Selection.Copy Sheet.Range("A1")
            Workbooks(Filename).Close
            ' Move to the next row on the new worksheet
            NextRow = Sheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
            ' Set the filename to the next file in the folder
            Filename = Dir
        Loop
End Sub

Step5: Save the module and return to the Excel workbook.

Step6: Press Alt+F8 to open the Macro dialog box, or click on the Macros command under Code group and select the macro you just created. Click Run to execute the macro.

How to Import Multiple CSV Files into One Excel Workbook vba5.png

Step7: You need to select one folder that containing the CSV files.

How to Import Multiple CSV Files into One Excel Workbook vba6.png

Step8: The macro will automatically import or merge the CSV files into separate worksheets in the same workbook.

How to Import Multiple CSV Files into One Excel Workbook vba7.png

This code will display the File Picker dialog box to select the folder containing the CSV files. It will then loop through all CSV files in the folder, open each file, and copy the data into a new worksheet in the same workbook. The name of each worksheet will match the name of the CSV file, without the “.csv” extension.

Import or Merge Multiple CSV Files into one Excel Worksheet using Command Prompt

If you want to import or merge multiple CSV files into one Excel worksheet, and you can achieve it by using the command prompt in Windows command line, just do the following steps:

Step1: Open the Command Prompt. On Windows, you can press the Windows key + R, type “cmd“, and press Enter.

How to Import Multiple CSV Files into One worksheet 1.png

Step2: Navigate to the folder where your CSV files are located using the “cd” command.

How to Import Multiple CSV Files into One worksheet 2.png

Step3: Type “copy *.csv mergedfiles.csv” command and press Enter.

How to Import Multiple CSV Files into One worksheet 3.png

This will create a new file named “ mergedfiles.csv ” that contains the data from all the CSV files in the folder.

Step4: Open Microsoft Excel and select “Open” from the “File” menu.

Step5: In the “Open” dialog box, navigate to the folder where your CSV files are located. And Change the file type to “All Files“. Select the “ mergedfiles.csv ” file and click “Open“.

How to Import Multiple CSV Files into One worksheet 4.png

Step6: In the Text Import Wizard, select “Delimited” as the file type and click “Next“.

How to Import Multiple CSV Files into One worksheet 5.png

Step7: Select “Comma” as the delimiter and click “Next“.

How to Import Multiple CSV Files into One worksheet 6.png

Step8: click on “Finish” command, the csv file would be imported into the current worksheet.

How to Import Multiple CSV Files into One worksheet 7.png

Import or Merge Multiple CSV Files into one Excel Worksheet using Power Query

You can import or merge multiple CSV files into one Excel worksheet using Power Query, just do the following steps:

Step1: Open Microsoft Excel and select “Data” Tab.

import multiple csv files into one worksheet using power query1.png

Step2: Click on “From File” in the “Get & Transform Data” section. And Select “From Folder” in the drop-down menu.

import multiple csv files into one worksheet using power query2.png

Step3: In the “From Folder” dialog box, navigate to the folder where your CSV files are located and click “Open“. The Power Query window will open.

import multiple csv files into one worksheet using power query3.png

Step4: select “Combine & Transform Data” from the Power Query window. And the Combine Files window will appear.

import multiple csv files into one worksheet using power query4.png

Step5: In the “Combine Files” dialog box, choose the Delimiter option as Comma based on your csv data and click “OK“.

import multiple csv files into one worksheet using power query5.png

Step6: In the Power Query Editor dialog box, select the columns you want to include in the merged table. You can now edit, filter, and transform the merged data as needed.

import multiple csv files into one worksheet using power query6.png

Step7: Click “Close & Load” to import the merged data into Excel.

import multiple csv files into one worksheet using power query7.png

Step8: This will merge the data from all the CSV files in the selected folder into one table in Excel using Power Query.

import multiple csv files into one worksheet using power query7.png

Conclusion

There are several ways to import or merge multiple CSV files into one Excel workbook. VBA code can be used to merge the files into one workbook, while using command prompt can be a quick and simple solution for those who prefer a command-line interface. On the other hand, using Power Query is a more user-friendly solution that allows for editing, filtering, and transforming the data, and can be particularly useful for large datasets.

Leave a Reply

Your email address will not be published. Required fields are marked *