How to combine / import multiple csv files into multiple worksheets?

If you want to import multiple csv files from a folder as separate worksheets within a workbook, how could you deal with this job in Excel?

Import multiple csv files into separate worksheets with VBA code

Import multiple csv files into separate worksheets with Kutools for Excel

Split a workbook into multiple separate csv / pdf / txt / excel files

Import multiple csv files into separate worksheets with VBA code

To quickly import multiple csv files to a workbook, the following VBA code may do you a favor, please do as follows:

1. Hold down the ALT + F11 keys in Excel to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Import multiple text files to separate worksheets:

Sub CombineCsvFiles()
'updateby Extendoffice
    Dim xFilesToOpen As Variant
    Dim I As Integer
    Dim xWb As Workbook
    Dim xTempWb As Workbook
    Dim xDelimiter As String
    Dim xScreen As Boolean
    On Error GoTo ErrHandler
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    xDelimiter = "|"
    xFilesToOpen = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "Kutools for Excel", , True)
    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files were selected", , "Kutools for Excel"
        GoTo ExitHandler
    End If
    I = 1
    Set xTempWb = Workbooks.Open(xFilesToOpen(I))
    xTempWb.Sheets(1).Copy
    Set xWb = Application.ActiveWorkbook
    xTempWb.Close False
    Do While I < UBound(xFilesToOpen)
        I = I + 1
        Set xTempWb = Workbooks.Open(xFilesToOpen(I))
        xTempWb.Sheets(1).Move , xWb.Sheets(xWb.Sheets.Count)
    Loop
ExitHandler:
    Application.ScreenUpdating = xScreen
    Set xWb = Nothing
    Set xTempWb = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, , "Kutools for Excel"
    Resume ExitHandler
End Sub

3. Then press F5 key to execute this code, and a window will popped out, go to the specific folder and select the csv files that you want to import to Excel, see screenshot:

4. And then click Open button, the selected csv files have been imported as separate worksheets within a new workbook.

5. Finally, you can save the new workbook as you need.

Import multiple csv files into separate worksheets with Kutools for Excel

If you have Kutools for Excel, with its powerful tool-Combine, you can quickly combine or import multiple csv files into one single worksheet or multiple separated worksheets as you need.

After installing Kutools for Excel, please do as this:

1. Please click Kutools Plus > Combine, see screenshot:

2. In the Combine Worksheets dialog box, please select Combine multiple worksheets from workbooks into one workbook, see screenshot:

3. In the Step 2 of the wizard, click Add > File to choose the csv files that you want to combine, see screenshot:

4. In the popped out Open dialog, select (*.csv) from the drop down list, and then specify the csv files that you want to merge.

5. Then click Open button, and your selected csv files have been imported into the dialog box, see screenshot:

6. In the Step 3, please do the following operations:

(1.) Checking First row of each worksheet (comments) from the Insert worksheet information section, all of the csv files information will be inserted into the combined sheets as comments;

(2.) You can rename the csv files as you need;

(3.) you can skip the blank csv files when you encounting a blank csv file.

7. Then click Finish button, and after finishing the combining, you can specify a file name and location for your new combined workbook. See screenshot:

8. Then click Save button, in the popped out promt box, you can open the new file click Yes, and close it by clicking No, see screenshot:

9. And then you can save this scenario or not as you like. See screenshot:

10. At last, your selected csv files have been imported into a new workbook with separated worksheet, see screenshot:

Download and free trial Kutools for Excel Now !

Split a workbook into multiple separate csv / pdf / txt / excel files

In certain case, you may want to split a workbook into separate csv files, Kutools for Excel’s Split Workbook utility can finish this job quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools Plus > Workbook > Split Workbook, see screenshot:

2. In the Split Workbook dialog box:

(1.) Check the worksheets that you want to split;

(2.) And check Specify saveformat, then choose the file format you want to save;

(3.) Then click Split button, and specify a folder to put the separated files.

3. And then click OK to start splitting, the workbook will be split into separate csv files you need.

Download and free trial Kutools for Excel Now !