Importing 100 text files into Excel at once

Question

I have this macro to bulk import in a excel spreadsheet 100+ .txt files contained in the same folder :

Sub QueryImportText()
    Dim sPath As String, sName As String
    Dim i As Long, qt As QueryTable
    With ThisWorkbook
        .Worksheets.Add After:= _
            .Worksheets(.Worksheets.Count)
    End With
    ActiveSheet.Name = Format(Now, "yyyymmdd_hhmmss")
    sPath = "C:\Users\TxtFiles\"
    sName = Dir(sPath & "*.txt")
    i = 0
    Do While sName <> ""
        i = i + 1
        Cells(1, i).Value = sName
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & sPath & sName, Destination:=Cells(2, i))
            .Name = Left(sName, Len(sName) - 4)
            .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 = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        sName = Dir()
        For Each qt In ActiveSheet.QueryTables
            qt.Delete
        Next
    Loop
End Sub

Each .txt file has the same structure: title, ID, date, createdBy, text.

The macro is working but :

• I want each file to be in a row (this macro display them in column)

This excel will them by export as .csv to be imported in my joomla website with MySql

Thanks a lot for your help!

Answer 1

Instead of using Excel to do the dirty work, I would recommend using Arrays to perform the entire operation. The below code took 1 sec to process 300 files

LOGIC:

1. Loop through the directory which has text files
2. Open the file and read it in one go into an array and then close the file.
3. Store the results in a temp array
4. When all data is read, simply output the array to Excel Sheet

CODE: (Tried and tested)

'~~> Change path here
Const sPath As String = "C:\Users\Siddharth Rout\Desktop\DeleteMelater\"

Sub Sample()
    Dim wb As Workbook
    Dim ws As Worksheet

    Dim MyData As String, tmpData() As String, strData() As String
    Dim strFileName As String

    '~~> Your requirement is of 267 files of 1 line each but I created 
    '~~> an array big enough to to handle 1000 files
    Dim ResultArray(1000, 3) As String

    Dim i As Long, n As Long

    Debug.Print "Process Started At : " & Now

    n = 1

    Set wb = ThisWorkbook

    '~~> Change this to the relevant sheet
    Set ws = wb.Sheets("Sheet1")

    strFileName = Dir(sPath & "\*.txt")

    '~~> Loop through folder to get the text files
    Do While Len(strFileName) > 0

        '~~> open the file in one go and read it into an array
        Open sPath & "\" & strFileName For Binary As #1
        MyData = Space$(LOF(1))
        Get #1, , MyData
        Close #1
        strData() = Split(MyData, vbCrLf)

        '~~> Collect the info in result array
        For i = LBound(strData) To UBound(strData)
            If Len(Trim(strData(i))) <> 0 Then
                tmpData = Split(strData(i), ",")

                ResultArray(n, 0) = Replace(tmpData(0), Chr(34), "")
                ResultArray(n, 1) = Replace(tmpData(1), Chr(34), "")
                ResultArray(n, 2) = Replace(tmpData(2), Chr(34), "")
                ResultArray(n, 3) = Replace(tmpData(3), Chr(34), "")

                n = n + 1
            End If
        Next i

        '~~> Get next file
        strFileName = Dir
    Loop

    '~~> Write the array to the Excel Sheet
    ws.Range("A1").Resize(UBound(ResultArray), _
    UBound(Application.Transpose(ResultArray))) = ResultArray

    Debug.Print "Process ended At : " & Now
End Sub

Answer 2

Thanks a lot for this information. I wanted to import only 4th column of my data file for that I had to put bit modification as follows

 Sub QueryImportText()
    Dim sPath As String, sName As String
    Dim i As Long, qt As QueryTable
    With ThisWorkbook
        .Worksheets.Add After:= _
            .Worksheets(.Worksheets.Count)
    End With
    ActiveSheet.Name = Format(Now, "yyyymmdd_hhmmss")
    sPath = "C:\Users\TxtFiles\"
    sName = Dir(sPath & "*.txt")
    i = 0
    Do While sName <> ""
        i = i + 1
        Cells(1, i).Value = sName
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & sPath & sName, Destination:=Cells(2, i))
            .Name = Left(sName, Len(sName) - 4)
            .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 = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(9,9,9,1) <---------(here)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        sName = Dir()
        For Each qt In ActiveSheet.QueryTables
            qt.Delete
        Next
    Loop
End Sub