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 good idea3

Export each sheet as csv/text/pdf to a folder with Kutools for Excelgood idea3


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 Sub

3. 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:

doc import multiple csv text xml 1

4. And click OK, and each text file in the selected folder has been imported into one worksheet of the active workbook. See screenshot:

doc import multiple csv text xml 2doc import multiple csv text xml 3

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!
combine sheets
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

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 Sub

3. 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:

doc import multiple csv text xml 4

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:

doc import multiple csv text xml 5

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:

doc import multiple csv text xml 6doc import multiple csv text xml 7

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 

doc import multiple csv text xml 8


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 Sub

3. Click Run button or F5 key to run the VBA, and select a folder in the popping dialog, see screenshot:

doc import multiple csv text xml 9

4. Click OK, and all the XML files in the selected folder are imported into the active sheet.


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:
doc combine 1

2. And in the step 1 of Combine dialog, choose one separation option as you need. See screenshot:
doc combine 2

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:
doc kutools combine sheets 3

4. Click Next to the last one step of Combine, and you can specify the combine options.
doc kutools combine sheets 4

5. Click Finish, a dialog pops out to remind you select a location to save the new combined result. See screenshot:
doc combine 5

6. Click Save. All adding sheets have been combine into a new single sheet.
doc combine 6

Tip: With Combine, you also can combine multiple CSV files form multiple folders or one folder into one sheet or workbook.


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:

doc import multiple csv text xml 10

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:

doc import multiple csv text xml 11

3. Click Split and select a folder that to save the split files in the Browse for Folder dialog, see screenshot:

doc import multiple csv text xml 12

4. Click OK, now all the checked sheets are exported as new file format in the selected folder.


Relative Articles:


The Best Office Productivity Tools

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. 60-day money back guarantee.
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.