How to extract data from a non-structured text file?

Question

Hello, I am very new to VBA (Excel). I was wondering if someone could start me off on the right track on extracting data from a text file. Well, actually, it is a PRF file that I open in Monarch Explorer and export it as a text file.

An example is below:

DPR# BC800-00     ASSN 04 CORP  099999 INST                   BANKCARD DAILY ACTIVITY JOURNAL BY PLAN                        AS OF    09/17/12         PAGE   404
BIN 123456789     EXAMPLE FEDERAL CREDIT UNION                                                                               RUN DATE 09/17/12

                               TOTAL OUTSTANDING BALANCES - BEGINNING                                  $74,786.02
                               RECOVERY OUTSTANDING BALANCES - BEGINNING                                    $0.00

       NON-SYSTEM GENERATED TRANSACTIONS - POSTED
                              TRAN CODE       REASON CODE     DESCRIPTION          COUNT            $ AMOUNT
                                  05               00     SALES DRAFT                   13              $1,040.49
                                  65               00     PAYMENT - THANK YOU            6                $713.55-
                                  65               35     ON-LINE PAYMENT-THANK YO       3                $743.00-

                                TOTAL NON-SYSTEM GENERATED TRANSACTIONS - POSTED        22                $416.06-

                                TOTAL NON-SYSTEM GENERATED TRANSACTIONS                 22                $416.06-

                                            PLAN TOTAL TRANSACTIONS - POSTED            22                $416.06-
                                            PLAN TOTAL TRANSACTIONS - UNPOSTED           0                  $0.00
                                            PLAN TOTAL TRANSACTIONS - RECOVERY           0                  $0.00
                                            PLAN TOTAL TRANSACTIONS                     22                $416.06-

                               TOTAL OUTSTANDING BALANCES - ENDING                                     $74,369.96
                               RECOVERY OUTSTANDING BALANCES - ENDING                                       $0.00

I would like to be able to extract BIN 123456789. I would also like to extract the $416.06- from the row that contains PLAN TOTAL TRANSACTIONS (the 4th one). The info isn't always in the same area. I would be doing this for multiple BINs, so if someone could start me in the right direction, that would be great.

Answer 1

Use "open file for" method and grab line with "BIN" using function instr()

and last line with "PLAN TOTAL TRANSACTIONS". To cut words you can use Split() function.

How to do that you can read in Excels Help.

Sample:

Sub pobierz()
    Const sFile$ = "c:\temp\fcu.txt" '<- your file (change this line)
    Dim strLine$, f%: f = FreeFile
    Dim result$
    Open sFile For Input As #f
        Do While Not EOF(f)
            Line Input #f, strLine
            If InStr(1, strLine, "BIN") > 0 Then result = Trim(Split(strLine, "EXAMPLE")(0)) & vbCr
            If InStr(1, strLine, "PLAN TOTAL TRANSACTIONS     ") > 0 Then result = result & Trim(Split(strLine, "$")(1))
        Loop
    Close #f
    MsgBox result, vbInformation , "VBATools.pl"
End Sub

---

Oskar Shon, Office System MVP

Press if Helpful; Answer when a problem solved