I'm a newbie in regards to parsing text files. My application requires a multiline bank file, text form comma delimited. I have to obtain the account number denoted by "03" and the balance "015" .
The file contains a lot of information that I will never use, header and validation codes, etc. I need to parse this long text file and only separate the fields that I need to use. Any suggestions on how to pull out only those items. The fields are all numbers but the file is a text file, comma delimited. Any help will be appreciated. Thanx

If you can post some sample lines from the text file, including any opening lines that might have a different format, I can help you write some code that will pull the values you want. This is pretty easy. I use it to pull medical data into one of my databases, and associate the data with the patient by SSN. This sounds similar (associate balance to account number)

The important thing is to include the entire line as it will appear in the text file (not real data though). For example, one of my sample lines is:


"110905,1105,111223333, , , , , , ,20,SE,20,25,15,05,25,10,25,15,20,35,15,20,38949,0 805"

The first group is the date, the second, the time of day, the third is the SSN, there are a few blank fields, then the medical data, then info about the machine that captured the data. I pull the date, time, SSN, and medical data and put it in the appropriate fields in a table, and it is associated with the patient's SSN. So knowing the placement of the fields is important as you can see.

HTH


mmcdonal

03,000001588866002,USD,010,152736,,,015,105542,,,0 40,105542,,,045,105542,,/
88,063,000,,,072,000,,,074,000,,,100,32623,4,,140, 32623,4,,400,79817,4,,450/
88,79817,4,/
Here is some of the sample code. The numbers following the field '03' is the account number and the numbers following the '015' is the dollar amount with 2 decimal points. There is no consistency as to the location of the fields. It depends on which back is sending them. the only consistency is the '03' is always followed by the account number and the 015 is always followed by the dollar amount.
Thanks very much for any suggestions or help you can give me. I was thinking about pattern matching but have no idea how to proceed.
tom

I have used code in the past that breaks an entire line of data into individual pieces and loads each into an array. You could then loop through the array, and check for the various indicators. Let me know if you would like me to post the code. I deal with a lot of data that is randomnly sequenced, so I feel your pain!!

Mike

Mike
EchoVue.com

Mike,
I would appreciate the code examples. I have been pulling my hair out on this one and at this point I can't afford to do that continually. I am already follicle challenged enough.
Thanks

Tom

Here you go...

    Dim arrToken(40) As String
    Dim strBuffer As String
    Dim i As Long
    Dim t As Long
    i = 1
    t = 1
    strBuffer = ""
    While i <= Len(strLine)
        If Mid(strLine, i, 1) <> "," Then
            strBuffer = strBuffer & Mid(strLine, i, 1)
            i = i + 1
        Else
            arrToken(t) = Replace(Trim(strBuffer), Chr(34), "")
            strBuffer = ""
            i = i + 1
            t = t + 1
        End If
    Wend
    arrToken(t) = strBuffer
    'there are t columns
    For i = 1 To t
        'determine what header it is
        Select Case UCase(arrToken(i))
            Case "ACCOUNT"
                colAccount = i
            etc....

The object of this was to figure out which columns my headings were in, and then I can reference the right column when I need that data type.

In your case, I would loop through and check for the 03 and 05, and then save the following column of data as the Account or Amount respesctively.

Hope that helps - Goodness knows it cost me enough hair!!

Mike

Mike
EchoVue.com

Here is what I came up with:

'-----Code Starts-----
Dim strAccount As String
Dim curBalance As Double
'etc.

Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "DSN=YourDSN;"
    objRS.Open "SELECT * FROM tblYourTable", objConn, 3, 3
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\data.txt") Then
    Set objStream = fso.OpenTextFile("C:\data.txt", 1, False, 0)
End If
Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine
    YourArray = Split(strLine, ",")
        i = 0
        Do Until i = UBound(YourArray)
            If YourArray(i) = "03" Then
                n = i + 1
                strAccount = YourArray(n)
            End If
            If YourArray(i) = "015" Then
                n = i + 1
                curBalance = YourArray(n)
            End If
            i = i + 1
        Loop

     If IsNull(strAccount) Or IsNull(curBalance) Then
     'Error handling here...
     Else
        objRS.AddNew
        objRS("Account") = strAccount
        objRS("Balance") = curBalance
        objRS.Update
        objRS.Close
        objConn.Close
     End If
Loop

Set objFile = fso.CreateTextFile("C:\Data.txt")
MsgBox i & " File(s) Transferred"
'-----Code Ends-----

The last fso.CreateTextFile overwrites the data file so that duplicate information can't be entered. Anyway, I tried this on your string and it works.

HTH


mmcdonal

Sorry, the two lines:

objRS.Close
objConn.Close

should come AFTER the last "Loop"

HTH


mmcdonal

Sorry, this doesn't address the decimals in the curBalance variable.

Perhaps:

Dim strDec1 As Integer
Dim strDec2 As Integer


strDec1 = Left(curBalance, (Len(curBalance - 2))
strDec2 = Right(curBalance, 2)
curBalance = strDec1 & "." & strDec2

   I am not sure about typing violations here, so you might have to play around with it to get the decimal inserted. Look up regular expressions on MSDN for guidance.

HTH

:D



mmcdonal

Sorry, left out a closing paren:

strDec1 = Left(curBalance, (Len(curBalance - 2)))

mmcdonal