How to Merge Multiple CSV Files into One Workbook in Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. This article will show you how to merge multiple CSV files into one Excel workbook.


How to Merge Multiple CSV Files into One Workbook in Excel: 3 Easy Ways

CSV files stand for Comma Separated Values files. Excel preserves data in tabular form in CSV files. These files consume less space on your computer.
I have a folder that contains 2 CSV files. The folder is on my Desktop.

merge multiple csv files using command prompt

I will use them and explain 3 easy ways to merge multiple CSV files into one Excel workbook.


1. Apply CMD Command to Merge Multiple CSV Files into One Workbook

First, I will show how to use the Command prompt. Let’s see the process step by step.

Steps:

  • First of all, select the folder that contains the CSV
  • Then, hold the SHIFT key and right-click your mouse.
  • After that, select Copy as path from the context menu.

  • Then, go to the Windows search box.
  • After that, type cmd.
  • Then, select the command prompt.

using command prompt to merge multiple csv files

  • The Command Prompt box will arrive.
  • Type cd followed by a space.
  • Then, paste the folder path. Your command prompt window will look like this.

The first part C:\Users\Me> comes by default and it may vary on your PC. Don’t worry about it.
  • Then, press ENTER. A new directory will appear.
  • After that, type copy *.csv merged.csv
  • “merged” is the name of the new file with the combined dataset.

using command prompt to merge multiple csv files

  • Then, press ENTER.
  • Excel will create a new file.

Read More: Merge CSV Files into Multiple Sheets in Excel


2. Use PowerQuery Feature to Merge Multiple CSV Files

The next method is the use of the PoweQuery feature.

Steps:

  • First of all, open a new workbook.
  • Then, go to the Data
  • After that, select Get Data.
  • Then, choose From File.
  • After that, select From Folder.

using PowerQuery to merge multiple csv files

  • Then, select the folder that contains the CSV
  • After that, click Open.

  • Excel will open a PowerQuery
  • Select Combine & Load.

using PowerQuery to merge multiple csv files

  • A Combine Files box will appear.
  • Then, click OK.

  • Excel will merge all the data.

using PowerQuery to merge multiple csv files

Read More: How to Merge CSV Files in Excel


3. Apply VBA Macro to Merge Multiple CSV Files into One Workbook

Now, I will use a VBA macro to merge multiple CSV files.

Steps:

  • Press ALT + F11 to open the VBA window.
  • Then go to Insert >> select Module.

  • A new module will open. Write down the following code in the module
Sub MergeCsvs()
Dim folderpath As String
  folderpath = "C:\Users\Me\Desktop\CSV Files"
  If folderpath Like "*[!\/]" Then
    folderpath = folderpath & "/"
  End If

  Dim File As String
  File = Dir(folderpath & "*.csv")
  Dim workbook_result As WorkBook
  Set workbook_result = Workbooks.Add
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False

  Dim WorkBook As WorkBook
  While File <> vbNullString
    Set WorkBook = Workbooks.Open(folderpath & File)
    WorkBook.ActiveSheet.UsedRange.Copy workbook_result. _
    ActiveSheet.UsedRange.Rows(workbook_result.ActiveSheet. _
    UsedRange.Rows.Count).Offset(1).Resize(1)
    WorkBook.Close False
    File = Dir()
  Wend

  workbook_result.ActiveSheet.Rows(1).EntireRow.Delete
End Sub

using VBA Macro to merge multiple csv files

Code Explanation

  • MergeCsvs is the new sub-procedure
  • folderpath, File, workbook_result, and WorkBook are the variables.
  • “C:\Users\Me\Desktop\CSV Files” is the folder path.
  • I have used the while loop here.
  • Press F5 to run the code.
  • Excel will create a new file and merge all the data.

  • Delete the column headings that appear more than once.

using VBA Macro to merge multiple csv files

Read More: How to Sort CSV File in Excel


Things to Remember

  • For the Command Prompt method, keep the folder on your C
  • The datasets should have the same headings for the PowerQuery

Conclusion

In this article, I have explained 3 easy ways to merge multiple CSV files into one Excel workbook. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo