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?
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.
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:
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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoThank you . Very handy
- To post as a guest, your comment is unpublished.· 1 years agoThank you! The code was very useful
- To post as a guest, your comment is unpublished.· 3 years agoThank 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."
- 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.
- To post as a guest, your comment is unpublished.· 4 years agoThank 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?