CODEWITHSUNDEEP
vbaexcel
Sharath
Sharath

Reputation: 57

Read only one record from Multiple text files into Excel using VBA

I have multiple txt files in a folder, which are tab delimited. Each of these files have a column called EngagementId, which is the same value, irrespective of number of records. However, it changes for every txt file, which is what I want to capture.

  1. I am trying to get the file name in the first row. The GetFileNames() works for that (as pointed out in the comments) 
Sub GetFileNames()
    Dim sPath As String
    Dim sFile As String
    Dim iRow As Integer
    Dim iCol As Integer
    Dim splitFile As Variant

    'specify directory to use - must end in "\"
    sPath = ActiveWorkbook.Path
    iRow = 0
    sFile = Dir(sPath & "\Individual Reports\")
    Do While sFile <> ""
        iRow = iRow + 1
        splitFile = Split(sFile, ".txt")
        For iCol = 0 To UBound(splitFile)
            Sheet1.Cells(iRow, iCol + 1) = splitFile(iCol)
        Next iCol
        sFile = Dir     ' Get next filename
    Loop
End Sub

Each of these txt files have one column (which is in the 13th position in each of the text files), called "EngagementId". I want to pull only the first "Engagement Id", which is from the 2nd row(since the first row contains headers).

Sub Extractrec()
    Dim filename As String, nextrow As Long, MyFolder As String
    Dim MyFile As String, text As String, textline As String

    MyFolder = ActiveWorkbook.Path
    MyFile = Dir(MyFolder & "\Individual Reports\*.txt")

    Do While MyFile <> ""
        Open (MyFolder & MyFile) For Input As #1
        Do Until EOF(1)
            Line Input #1, LineFromFile
            LineItems = Split(LineFromFile, "\t") 'second loop text is already stored 
                                                  '-> see reset text
            Sheet1.Cells(iRow, iCol + 2).Value = LineItems(13, 2)
        Loop
        Close #1

    Loop

Upvotes: 0

Views: 407

Answers (2)

Vincent G
Vincent G

Reputation: 3188

Since you only need the second line of each file, you don't need to loop, just read and discard the fist line, then read and split the second one:

    Open (MyFolder & MyFile) For Input As #1 'MyFolder & MyFile won't be the correct name (probably should be MyFolder & "\Individual Reports\" & MyFile)
    Line Input #1, LineFromFile 'line to discard
    Line Input #1, LineFromFile 'line to use
    LineItems = Split(LineFromFile, vbTab)
    Sheet1.Cells(someplace).Value = LineItems(13) ' replace some place with the correct value that we don't know
    Close #1

Upvotes: 0

user6432984
user6432984

Reputation:

Using an ADODB.Recordset to query would be more versatile.

Sub Example()
    On Error Resume Next
    Dim rs As Object, f As Object, conn  As Object
    Dim FolderPath As String, FileName As String, FilterString As String
    FolderPath = "C:\Users\best buy\Downloads\stackoverfow\Sample Data File\"
    FileName = "example.csv"
    FilterString = "WHERE EngagementId = 20"

    Set rs = getDataset(FolderPath, FileName, FilterString)

    Do While Not rs.BOF And Not rs.EOF
        Debug.Print rs.Fields("EngagementId")
        Debug.Print rs.Fields("Company")
        Debug.Print rs.Fields("City")
        Debug.Print rs.Fields("State")

        rs.MoveNext
    Loop

    Set conn = rs.ActiveConnection
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

Function getDataset(FolderPath As String, FileName As String, FilterString As String) As Object
    Dim conn As Object, rs As Object
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FolderPath & ";" & _
             "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
    rs.ActiveConnection = conn
    rs.Source = "SELECT * FROM " & FileName & " " & FilterString
    rs.Open
    Set getDataset = rs
End Function

Upvotes: 1

Related Questions