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 filw 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 !
Demo: Import multiple csv files into separate worksheets with Kutools for Excel
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 !
Split a workbook into multiple separate csv / pdf / txt / excel files
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
