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!


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
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 · 28 days ago
    Thank you . Very handy
  • To post as a guest, your comment is unpublished.
    Amit · 1 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 · 2 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?