When working with Excel files, sometimes we come up with multiple CSV files. Now, it becomes a need for us frequently to merge those CSV files into a single workbook. In this article, I will show you all the steps to merge CSV files into multiple sheets in Excel.
Merge CSV Files into Multiple Excel Sheets: with Easy Steps
Say, you have three individual Excel workbooks for grade 6, grade 7, and grade 8 report cards for various subjects for multiple students. The files are in CSV format with the students’ names, subjects, and scores. Now, you want to merge all the workbooks in a single workbook in multiple sheets. Go through the step-by-step guidelines to merge CSV files into multiple Excel sheets.
📌 Step 1: Prepare a Module for VBA
First and foremost, you need to insert and prepare a module for the required VBA code application.
- To do this, go to the Developer tab >> Visual Basic tool.
- As a result, the VB Editor window will open.
- Afterward, go to the Insert tab here and choose the Module option.
As a result, you will have a module now to insert your VBA code.
Read More: How to Keep Leading Zeros in Excel CSV Programmatically
📌 Step 2: Write and Save the VBA Code
Following this, you will need to insert your VBA code now.
- In order to do this, first click on Module 1.
- Afterward, write the following code in the VB Editor of Module 1.
Sub MergeCSVFiles()
Dim openFiles As Variant
Dim I As Integer
Dim wb As Workbook
Dim mergedWb As Workbook
Dim delimiter As String
Dim screen As Boolean
On Error GoTo ErrHandler
screen = Application.ScreenUpdating
Application.ScreenUpdating = False
delimiter = ";"
openFiles = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Merge CSV Files", , True)
If TypeName(openFiles) = "Boolean" Then
MsgBox "No files were selected", , "Merge CSV Files"
GoTo ExitHandler
End If
I = 1
Set mergedWb = Workbooks.Open(openFiles(I))
mergedWb.Sheets(1).Copy
Set wb = Application.ActiveWorkbook
mergedWb.Close False
Do While I < UBound(openFiles)
I = I + 1
Set mergedWb = Workbooks.Open(openFiles(I))
mergedWb.Sheets(1).Move , wb.Sheets(wb.Sheets.Count)
Loop
ExitHandler:
Application.ScreenUpdating = screen
Set wb = Nothing
Set mergedWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Merge CSV Files"
Resume ExitHandler
End Sub
- Subsequently, press Ctrl + S on your keyboard to save the VBA code.
- As a result, a Microsoft Excel dialogue box will appear.
- Subsequently, click on the No button.
- Consequently, the Save As window will appear.
- Following, change the Save as type: option as .xlsm format and click on the Save button.
Thus, your VBA code is written and saved perfectly.
Read More: How to Fix CSV File in Excel
📌 Step 3: Run the Code to Merge CSV Files into Multiple Sheets
Last but not least, you need to run the VBA code now to merge CSV files into multiple sheets.
- To do this, go to the Developer tab >> Macros tool.
- As a result, the Macro window will appear.
- Subsequently, choose the MergeCSVFiles macro from the Macro name: pane and click on the Run button.
- Thus, all the workbooks will be merged into a new workbook as different worksheets.
- Now, to store the Excel file properly, go to the File tab.
- Following, click on the Save As option from the expanded File tab.
- As a result, the Excel Save As dialogue box will appear.
- Subsequently, click on the Browse option.
- Consequently, the Save As dialogue box will appear.
- Following, insert your desired file name in the File name: text box and click on the Save button.
Thus, the CSV files are merged into multiple sheets of a single workbook. And, the final outcome would look like this.
Read More: Paste Comma Separated Values into Excel
Use of Power Query to Merge CSV Files into a Single Sheet in Excel
Now, if you want to merge CSV files into a single sheet in Excel, you can use the Excel power query tool. Follow the steps below to do this.
📌 Steps:
- First and foremost, go to the Data tab >> Get Data tool >> From File option list >> From Folder option.
- As a result, the Browse window will open.
- Following, choose the folder where you have kept the CSV files.
- Subsequently, click on the Open button.
- As a result, the Power Query window will open and it will show you the CSV files to import.
- Subsequently, click on the Combine button >> Combine & Transform Data option.
- As a result, the Combine Files window will appear.
- You will see the preview here and choose the Delimiter option as Semicolon according to your data.
- Following, click on the OK button.
- Thus, you will get the merged file in the Power Query window.
- Last but not least, go to the File tab from the Power Query window >> click on the Close & Load button >> Close & Load option.
Consequently, you will get all the CSV files merged into a single sheet in Excel. And, the output should look like this.
Read More: How to Edit CSV File in Excel
💬 Things to Remember
- Maintain a specific delimiter for every file and every data.
- All CSV files should be oriented in the same way and from the same rows and columns.
Download Practice Workbook
You can download our source CSV files and final merged workbook from here for free!
CSV Source Files:
Final Merged File:
Conclusion
So, in this article, I have shown you all steps to merge CSV files into multiple sheets in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations. Thank you!
