Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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


Combine / import multiple worksheets or csv files into one workbook:

In your daily work, to combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge and headachy work. But, if you have Kutools for Excel, with its powerful utility – Combine, you can quickly combine multiple worksheets, workbooks into one worksheet or workbook.

doc import csv to worksheet 6

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!



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 20151015
    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:

doc import csv to worksheet 1

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.


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 follows:

1. Please click Enterprise > Combine, see screenshot:

doc import csv to worksheet 5

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

doc import csv to worksheet 6

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

doc import csv to worksheet 7

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.

doc import csv to worksheet 8

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

doc import csv to worksheet 9

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.

doc import csv to worksheet 10

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

doc import csv to worksheet 11

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:

doc import csv to worksheet 12

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

doc import csv to worksheet 13

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

doc import csv to worksheet 14

Download and free trial Kutools for Excel Now !


Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

After installing Kutools for Excel, please do as follows:

1. Click Enterprise > Workbook > Split Workbook, see screenshot:

doc import csv to worksheet 2

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.

doc import csv to worksheet 3

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

doc import csv to worksheet 4

Download and free trial Kutools for Excel Now !


Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Rohit · 7 months ago
    Thank you . Very handy
  • To post as a guest, your comment is unpublished.
    Amit · 8 months ago
    Thank you! The code was very useful
  • To post as a guest, your comment is unpublished.
    Andrew · 2 years ago
    Thank you for sharing this code. It is exactly what I was looking for.

    Sadly it is not working as well as I hoped it would.

    I am receiving an error message every time the code is run.
    ERROR: "The name is already taken. Try a different one."

    Notes:
    - I am trying to combine four csv files
    - All of the csv files have the same name "file_name.csv, file_name(1).csv, etc..."


    The code combines the first two csv files into one workbook and the the third file into its own workbook. The fourth file never had a chance because of the error.

    If anyone has some thoughts as how to go about fixing this issue I would greatly appreciate your feedback.

    Thank you all in advanced for you time.

    Best regards,
    Andrew
  • To post as a guest, your comment is unpublished.
    Mary · 3 years ago
    Thank you for the code on combining files.

    I however want to have the multiple files in the workbook I am currently working with, not for the code to create a new workbook.

    How do I go about achieving that?