How to quickly batch import multiple csv/text/xml files in Excel?
In Excel, you may have tied to save a workbook as csv file, text file or xml file, but have you ever tried to import multiple csv/text/xml files from a folder into a workbook or a worksheet? In this article, I introduce some methods to quickly batch import them.
Import multiple text files from a folder into each worksheets of a workbook with VBA
Import multiple csv files from a folder into a single sheet with VBA
Import multiple xml files from a folder into a single sheet with VBA
Import or combine multiple xml/csv files into a sheet or a workbook with Kutools for Excel 
Export each sheet as csv/text/pdf to a folder with Kutools for Excel
Import multiple text files from a folder into each worksheets of a workbook with VBA
To import text files from a folder to a workbook, you can use below VBA to quickly handle it.
1. Enable a blank workbook, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the VBA to the Module window.
VBA: Import all text files from a folder into a workbook
Sub LoadPipeDelimitedFiles()
'UpdatebyKutoolsforExcel20151214
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xCount As Long
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
Application.ScreenUpdating = False
xFile = Dir(xStrPath & "\*.txt")
Do While xFile <> ""
xCount = xCount + 1
Sheets(xCount).Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& xStrPath & "\" & xFile, Destination:=Range("A1"))
.Name = "a" & xCount
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
xFile = Dir
End With
Loop
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "no files txt", , "Kutools for Excel"
End Sub3. Press F5 key or Run button to run the VBA, and select a folder that you want to import the text files from in the popping dialog. See screenshot:
4. And click OK, and each text file in the selected folder has been imported into one worksheet of the active workbook. See screenshot:
Easily Combine multiple sheets/Workbook into one Single sheet or Workbook |
| To combinne multiples sheets or workbooks into one sheet or workbook may be edious in Excel, but with the Combine function in Kutools for Excel, you can combine merge dozens of sheets/workbooks into one sheet or workbook, also, you can consolidate the sheets into one by several clicks only. Click for full-featured 30 days free trial! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Import multiple csv files from a folder into a single sheet with VBA
To import all the csv files form a folder into a single sheet, you can use below VBA code.
1. Enable a blank worksheet, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste below VBA into the new Module window.
VBA: Import csv files from a folder into one worksheet
Sub ImportCSVsWithReference()
'UpdatebyKutoolsforExcel20151214
Dim xSht As Worksheet
Dim xWb As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
Set xSht = ThisWorkbook.ActiveSheet
If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear
Application.ScreenUpdating = False
xFile = Dir(xStrPath & "\" & "*.csv")
Do While xFile <> ""
Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
Columns(1).Insert xlShiftToRight
Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
ActiveSheet.UsedRange.Copy xSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
xWb.Close False
xFile = Dir
Loop
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "no files csv", , "Kutools for Excel"
End Sub3. Press F5 key or click Run button to execute the VBA, and a dialog pops out to select a folder that you want to import all csv files from. See screenshot:
4. Click OK, and a dialog pops out to remind you if clear the contents of active worksheet before importing, here I click Yes. See screenshot:
After clicking Yes, all csv files in the selected folder are imported into the current sheet, and place data from Column A to right. See screenshot:
Tip: If you want to place csv files horizontally in a worksheet, you can use below VBA.
Sub ImportCSVsWithReferenceI()
'UpdatebyKutoolsforExcel20151214
Dim xSht As Worksheet
Dim xWb As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xCount As Long
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
Set xSht = ThisWorkbook.ActiveSheet
If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then
xSht.UsedRange.Clear
xCount = 1
Else
xCount = xSht.Cells(3, Columns.Count).End(xlToLeft).Column + 1
End If
Application.ScreenUpdating = False
xFile = Dir(xStrPath & "\" & "*.csv")
Do While xFile <> ""
Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
Rows(1).Insert xlShiftDown
Range("A1") = ActiveSheet.Name
ActiveSheet.UsedRange.Copy xSht.Cells(1, xCount)
xWb.Close False
xFile = Dir
xCount = xSht.Cells(3, Columns.Count).End(xlToLeft).Column + 1
Loop
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "no files csv", , "Kutools for Excel"
End Sub 
Import multiple xml files from a folder into a single sheet with VBA
If you want to import all XML files from a folder into a single sheet, you can use below VBA code.
1. Select a blank sheet you want to place the imported data, and press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.
2. Click Insert > Module, paste VBA code into the Module window.
VBA: Import XML files from a folder into a worksheet.
Sub From_XML_To_XL()
'UpdatebyKutoolsforExcel20151214
Dim xWb As Workbook
Dim xSWb As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xCount As Long
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
Application.ScreenUpdating = False
Set xSWb = ThisWorkbook
xCount = 1
xFile = Dir(xStrPath & "\*.xml")
Do While xFile <> ""
Set xWb = Workbooks.OpenXML(xStrPath & "\" & xFile)
xWb.Sheets(1).UsedRange.Copy xSWb.Sheets(1).Cells(xCount, 1)
xWb.Close False
xCount = xSWb.Sheets(1).UsedRange.Rows.Count + 2
xFile = Dir()
Loop
Application.ScreenUpdating = True
xSWb.Save
Exit Sub
ErrHandler:
MsgBox "no files xml", , "Kutools for Excel"
End Sub3. Click Run button or F5 key to run the VBA, and select a folder in the popping dialog, see screenshot:
4. Click OK, and all the XML files in the selected folder are imported into the active sheet.
Import or combine multiple xml/csv files into a sheet or a workbook with Kutools for Excel
If you are not familiar with VBA, do worry, here I introduce a handy tool – Kutools for Excel for you. With its powerful Combine utility, you can quickly combine multiple xml files or csv files into one workbook or one Excel sheet.
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. | ||
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Active Excel, and click Kutools Plus > Combine. See screenshot：
2. And in the step 1 of Combine dialog, choose one separation option as you need. See screenshot:
3. Click Next to go to step 2 of the Combine, click Add to add files from various folders or files from one folder to Workbook list, and also you can specify the sheets you want to combine from Worksheet list of right section. See screenshot:
4. Click Next to the last one step of Combine, and you can specify the combine options.
5. Click Finish, a dialog pops out to remind you select a location to save the new combined result. See screenshot:
6. Click Save. All adding sheets have been combine into a new single sheet.
Tip: With Combine, you also can combine multiple CSV files form multiple folders or one folder into one sheet or workbook.
Combine Multiple XML/CVS Files into One Sheet/Workbook
Export each sheet as csv/text/pdf to a folder with Kutools for Excel
If you want to export each sheet as csv/text/pdf file to a folder, Kutools for Excel’s Split Workbook utility can do a favor for you.
After free installing Kutools for Excel, please do as below:
1. Enable the workbook you want to export its worksheets, and click Kutools Plus > Workbook > Split Workbook. See screenshot:
2. In the Split Workbook dialog, you can check the sheet names you need to export, in default all the sheets are checked, and check Specify save format and select the file format you want to save as from below drop down list. See screenshot:
3. Click Split and select a folder that to save the split files in the Browse for Folder dialog, see screenshot:
4. Click OK, now all the checked sheets are exported as new file format in the selected folder.
Export Each Sheet as Single XML/CSV/TXT/PDF File
Relative Articles:
- How to convert hyperlink to plain text in Excel?
- How to convert decimal number to binary/octal/hex number or vice versa in Excel?
- How to insert live exchange rate in Excel?
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!
