Extract Data from Text File into Excel

Question

I am new to VBA so getting my task done is quite a struggle. Been reading and trying codes from different threads for a few days now to no success. So I am hoping someone could assist me.

I have multiple text files that I need to extract data from. But I only need certain data such as DATE-TIME to be placed in the 1st column and CARD NUMBER in the 2nd column. Got codes from this thread >> Extract a single line of data from numerous text files and import into Excel but my output only shows the first data from the file. Please see the attached files below.

sample text

Output

Desired Output

Here's what I have:

Sub ExtractData()
Dim filename As String, nextrow As Long, MyFolder As String
Dim MyFile As String, text As String, textline As String, filedate As String
Dim filenum As Integer

MyFolder = "C:\directory\"
MyFile = Dir(MyFolder & "*.txt")

Do While MyFile <> ""
    Open (MyFolder & MyFile) For Input As #1
    Do Until EOF(1)
        Line Input #1, textline
        text = text & textline

    Loop
    Close #1
    MyFile = Dir()
    Debug.Print text
    filedate = InStr(text, "DATE-TIME")
    nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    ActiveSheet.Cells(nextrow, "A").value = Mid(text, filedate + 16, 17)

    filenum = InStr(text, "CARD NUMBER")
    nextrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
    ActiveSheet.Cells(nextrow, "B").value = Mid(text, filenum + 16, 10)
    text = ""  
Loop
End Sub

Have a look at How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops to extract data by any pattern. Regular Expressions is a mighty tool for such things. — Pᴇʜ
– Pᴇʜ, Commented Aug 1, 2018 at 14:15
Why write VBA at all? You are asking to import a file into Excel. You can do that using the commands in the Data tab and use PowerQuery to clean up, transform the data — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 1, 2018 at 14:44
BTW Line Input, Close etc aren't used in VBA. They are leftovers from VB5 or even earlier. Since the VB6 days people are using FileSystemObject and the other classes in the Scripting Runtime. Check this question. Excel's own data input functionality though is far more powerful — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 1, 2018 at 14:46

Vinh Can Code · Accepted Answer · 2018-08-01 14:37:37Z

1

I modify the code for you, it can work:

Sub ExtractData()
Dim filename As String, nextrow As Long, MyFolder As String
Dim MyFile As String, text As String, textline As String, filedate As String
Dim filenum As Integer
dim idx%

MyFolder = "C:\directory\"
MyFile = Dir(MyFolder & "*.txt")

nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

Do While MyFile <> ""

    Open (MyFolder & MyFile) For Input As #1

    'nextrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

    Do Until EOF(1)
        Line Input #1, textline 'read a line

        idx = InStr(textline, "DATE-TIME") ' if has date, set it but not move to the next ROW
        if idx > 0 then 
            ActiveSheet.Cells(nextrow, "A").value = Mid(textline, idx + 16)
        end if

        idx = InStr(textline, "CARD NUMBER")
        if idx > 0 then
            ActiveSheet.Cells(nextrow, "B").value = Mid(textline, filenum + 16)

            nextrow = nextrow + 1 'now move to next row

        end if

    Loop
    Close #1
    MyFile = Dir()

Loop
End Sub

answered Aug 1, 2018 at 14:37

Vinh Can Code

4373 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gis Over a year ago

Hi! That's awesome! Worked like a charm! Thanks a lot. You're the best! :)

Gis Over a year ago

Yup, just did. Thanks again. :)

Collectives™ on Stack Overflow

Extract Data from Text File into Excel

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related