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.
Step2: Press Alt+F11 to open the Visual Basic Editor.
Step3: In the Visual Basic Editor, select Insert > Module to create a new module.
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.
Step7: You need to select one folder that containing the CSV files.
Step8: The macro will automatically import or merge the CSV files into separate worksheets in the same workbook.
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.
Step2: Navigate to the folder where your CSV files are located using the “cd” command.
Step3: Type “copy *.csv mergedfiles.csv” command and press Enter.
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“.
Step6: In the Text Import Wizard, select “Delimited” as the file type and click “Next“.
Step7: Select “Comma” as the delimiter and click “Next“.
Step8: click on “Finish” command, the csv file would be imported into the current worksheet.
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.
Step2: Click on “From File” in the “Get & Transform Data” section. And Select “From Folder” in the drop-down menu.
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.
Step4: select “Combine & Transform Data” from the Power Query window. And the Combine Files window will appear.
Step5: In the “Combine Files” dialog box, choose the Delimiter option as Comma based on your csv data and click “OK“.
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.
Step7: Click “Close & Load” to import the merged data into Excel.
Step8: This will merge the data from all the CSV files in the selected folder into one table in Excel using Power Query.
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.