Parsing data in a flate file 1

[tpowers]

Hello everyone, I am trying to import a fixed width flate file and I was informed that the only way that I can do that is if I write a code in vb that will parse eaching line. Now since I am new at this I am unsure how to parse a flate file in vb. If anyone can help me that would be great.

Thanks Tpowers

 

[payback]

Have a look at the following posting. There is an assumption that you know Access VB.

thread958-950508

 

[tpowers]

Let me give an example:


1ACO758400000ANKERAGE NP13469*40261*RHO119291
3AANKERAGE
3BBUDGET BLINDS OF REDDING JERRY BAY 19182 BANNER WAY COTTONWOOD CA96022
4A0001UBLXT2 86146 0000010105500000825000
4BOBLL NC0000000000725000 0008000001055000T 00000000 00000000 R
4C F10010550000082500000000000
5A

in the above text I need access to go in and say the 1A is a head then count 12 in and put that into a field then count another 20 and place that into another field, I hope this makes sense, cause I can't get it to work, I tried importing the data first but that seems more complex. Can someone please help me..

thank you in advance,

TPowers

 

[payback]

That is exactly what this part of the code in my posting does. The rest of the code opens the flat file etc. As I said in that posting, you need to know the structure of your source file and destination tables.

tst = (Nz(Trim(Mid(strline, 1, 11))))
If Len(tst) = 0 Then
rst!ListID = Null
Else
rst!ListID = tst
End If

tst = (Nz(Trim(Mid(strline, 12, 40))))
If Len(tst) = 0 Then
rst!ListName = Null
Else
rst!ListName = tst
End If

 

[tpowers]

You know what thank you so very much, you have just made my day, I can not tell you how very happy I am.


Thank you Thank you,


Tpowers

 

[tpowers]

Ok... hey payback this code is working, but I can not get it to cycle to the next line... I can pull all the data from the first line of the file and then display it, but when it gets ready to go to the next line it bombs out on me, do you know why that is?

Thank you in advance,

Tpowers

 

[payback]

This is the original code I posted -

fp = FreeFile()
Open strFileName For Input As fp

While Not EOF(fp)
strline = ""
strchar = " "
bolend = False
While Not EOF(fp) And Not bolend
If Asc(strchar) <> 10 And Asc(strchar) <> 13 Then
strchar = Input(1, #fp)
If Not EOF(fp) Then
If Asc(strchar) <> 10 And Asc(strchar) <> 13 Then
strline = strline & strchar
End If
End If
Else
bolend = True
End If
Wend
If Len(strline) = 0 Then GoTo Nextline

rst.AddNew

tst = (Nz(Trim(Mid(strline, 1, 11))))
If Len(tst) = 0 Then
rst!ListID = Null
Else
rst!ListID = tst
End If

tst = (Nz(Trim(Mid(strline, 12, 40))))
If Len(tst) = 0 Then
rst!ListName = Null
Else
rst!ListName = tst
End If

rst.Update
Nextline:
Wend

Close #fp

Have you used all of this? Do you have a different delimiter for the end of a line apart from a carriage return/line feed?

Is there an error message?

 

[tpowers]

Hello Payback, the following is the code that I am using:

Dim strFileName As String
Dim strContinue As String
strContinue = "1rst"
fp = FreeFile()

strFileName = "C:\Old_Data\ABS\Development\WESTERN1.txt"
Open strFileName For Input As fp

While Not EOF(fp)
strline = ""
strchar = " "
bolend = False
While Not EOF(fp) And Not bolend
If Asc(strchar) <> 10 And Asc(strchar) <> 13 Then
strchar = Input(1, #fp)
If Not EOF(fp) Then
If Asc(strchar) <> 10 And Asc(strchar) <> 13 Then
strline = strline & strchar
End If
End If
Else
bolend = True
End If
Wend
If Len(strline) = 0 Then GoTo Nextline
If strContinue = "1rst" Then
tst1 = (Nz(Trim(Mid(strline, 1, 2))))
tst2 = (Nz(Trim(Mid(strline, 3, 11))))
tst3 = (Nz(Trim(Mid(strline, 14, 20))))
tst4 = (Nz(Trim(Mid(strline, 34, 1))))
tst5 = (Nz(Trim(Mid(strline, 35, 1))))
tst6 = (Nz(Trim(Mid(strline, 36, 15))))
tst7 = (Nz(Trim(Mid(strline, 52, 20))))
tst8 = (Nz(Trim(Mid(strline, 71, 3))))
MsgBox tst1
MsgBox tst2
MsgBox tst3
MsgBox tst4
MsgBox tst5
MsgBox tst6
MsgBox tst7
MsgBox tst8
ElseIf strContinue = "2nd" Then
tst9 = (Nz(Trim(Mid(strline2, 1, 2))))
If tst9 <> "1A" Then
MsgBox "This will work"
End If
MsgBox tst9
End If
strContinue = "2nd"
MsgBox strContinue
tst9 = (Nz(Trim(Mid(strline, 3, 11))))
'tst10 = (Nz(Trim(Mid(strline, 35, 1))))
'tst11 = (Nz(Trim(Mid(strline, 36, 15))))
'tst12 = (Nz(Trim(Mid(strline, 52, 20))))
MsgBox tst9
'MsgBox tst10
'MsgBox tst11
'MsgBox tst12
'End If
Nextline:


Wend

Close #fp


I have been mising with this code since yesterday and still can not get it to work...

TPowers

End Sub

 

[payback]

I copied your code into a module and set up a dummy text file. I also removed anything in relation to the "strContinue" and was able to cycle through 5 lines of data successfully.

I suggest you try the same before proceeding, however it could be that your text file does not have the same line delimeter. Do you know how it was created?

Sometimes flat files are created differently nand we might just have to tweak the EOL character recognition.

 

[tpowers]

When I run the code with with out the "strContinue" it goes and cycles the first line twice and then moves on to the next line. I don't understand it.


Tpowers

 

[payback]

This is my exact code with a small change - I have used the VB constants to trap for a Linefeed character, a Carriage Return character or a combination of both.

Use this exact code against your file (after you change the file path name) and try it. Copy it from this page, paste into a module, then create a macro to run this function (sorry if I am telling you something you already know).

The next step is to identify how your file was created so we can check that out, but try this first.

Function TestImport()

Dim strchar As String
Dim strline As String
Dim bolend As Boolean

Dim tst1 As String
Dim tst2 As String
Dim tst3 As String
Dim tst4 As String
Dim tst5 As String
Dim tst6 As String
Dim tst7 As String
Dim tst8 As String
Dim tst9 As String

'Dim strLine2 As String

Dim fp As Integer
Dim strFileName As String
Dim strContinue As String
strContinue = "1rst"
fp = FreeFile()

strFileName = "C:\Data\WESTERN1.txt"

Open strFileName For Input As fp

While Not EOF(fp)
strline = ""
strchar = " "
bolend = False

While Not EOF(fp) And Not bolend
'If Asc(strchar) <> 10 And Asc(strchar) <> 13 And strchar <> vbCrLf Then
If strchar <> vbCr And strchar <> vbLf And strchar <> vbCrLf Then
strchar = Input(1, #fp)
If Not EOF(fp) Then
'If Asc(strchar) <> 10 And Asc(strchar) <> 13 And strchar <> vbCrLf Then
If strchar <> vbCr And strchar <> vbLf And strchar <> vbCrLf Then
strline = strline & strchar
End If
End If
Else
bolend = True
End If
Wend

If Len(strline) = 0 Then GoTo Nextline

'If strContinue = "1rst" Then
tst1 = (Nz(Trim(Mid(strline, 1, 2))))
tst2 = (Nz(Trim(Mid(strline, 3, 11))))
tst3 = (Nz(Trim(Mid(strline, 14, 20))))
tst4 = (Nz(Trim(Mid(strline, 34, 1))))
tst5 = (Nz(Trim(Mid(strline, 35, 1))))
tst6 = (Nz(Trim(Mid(strline, 36, 15))))
tst7 = (Nz(Trim(Mid(strline, 52, 20))))
tst8 = (Nz(Trim(Mid(strline, 71, 3))))
MsgBox tst1
MsgBox tst2
MsgBox tst3
MsgBox tst4
MsgBox tst5
MsgBox tst6
MsgBox tst7
MsgBox tst8
'ElseIf strContinue = "2nd" Then
' tst9 = (Nz(Trim(Mid(strLine2, 1, 2))))
' If tst9 <> "1A" Then
' MsgBox "This will work"
' End If
' MsgBox tst9
'End If

'strContinue = "2nd"
'MsgBox strContinue
'tst9 = (Nz(Trim(Mid(strline, 3, 11))))
'tst10 = (Nz(Trim(Mid(strline, 35, 1))))
'tst11 = (Nz(Trim(Mid(strline, 36, 15))))
'tst12 = (Nz(Trim(Mid(strline, 52, 20))))
'MsgBox tst9
'MsgBox tst10
'MsgBox tst11
'MsgBox tst12
'End If
Nextline:


Wend

Close #fp


End Function

 

[tpowers]

I don't mean to be a pain, but can I please ask why you have txt9 and msgbox tst9 commented out? See line 1 is differnt from line 2 so I need to parse at differnt sections of of that line. Really all most every ling is differnt, One record could have 10 lines in this file, before I get to the next record. Does that makes sense what I am trying to do.

 

[payback]

It was not important for my code testing. I was only proving that the code will loop line by line in a flat text file with a delimeter at the end of each line.

Your requirement appears to be more than that.

When you say 10 lines for one record is this with word wrap on or off in notepad? My code assumes that there is one record per line.

There are other ways to handle it if this is not the case but more information is required.

Can you post say the first two records and supply as much information about the text file record structure as possible (hide anything sensitive of course).

 

[tpowers]

Below you will find the first complete record in the file. I have placed a "|" at then end of each line to show you where that line ends, because the space will between the data and these lines will some times contain data.

1ACO758400000ANKERAGE NP13469*40261*RHO119291 |
3AANKERAGE |
3BJohn Doe OF REDDING JERRY BAY 111 BAKER WAY COTTONWOOD MI96022 |
4A0001UBLXT2 86146 0000010105500000825000
4BOBLL NC0000000000725000 0008000001055000T 00000000 00000000 R |
4C F10010550000082500000000000 |
5A

So you can see how each line is differnt. I don't know what else I can say about the file or the data in the file. But I want to thank you for your help, and I hope you can help me with the solution.

 

[payback]

Should not be an issue but I might not be able to reply to you today. I have created flat file exports along the same lines previously so it should not be hard to reverse engineer the logic. I will be making some assumptions about what you have given me but as long as you understand the principles to help you finish the job I assume that will be OK.

 

[payback]

I guess I should have looked at the original posting you gave more closely but I presumed that each line was part of the same record and wrapped.

Just one question - will there always be 7 lines in each record, OR does the last line always start with a 5. I need to know which is the last line so the created record can be saved and a new one started. Either way is OK for me.

 

[tpowers]

Yes the last line of every record starts with 5A.


Thank you so much I am recking my brain over here.

 

[payback]

Have a look at the following. I have assumed that each record consist of 7 lines with each line having the same preceeding two characters. I added code for DAO also because the record update placements are important. If this is not exactly correct, give me more information so I can review but most multiline text file records fit similar situations (apart from Header, Detail, Footer scenarios for banks).

Function Flatfile()

'Dim dbs As database
'Dim rst As Recordset

Dim fp As Integer

Dim strchar As String
Dim strline As String
Dim strFileName As String
Dim tst As String

Dim bolend As Boolean

'Set dbs = CurrentDb
'Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset)

fp = FreeFile()

strFileName = "C:\Data\WESTERN1.txt"

Open strFileName For Input As fp

While Not EOF(fp)
strline = ""
strchar = " "
bolend = False

While Not EOF(fp) And Not bolend
If strchar <> vbCr And strchar <> vbLf And strchar <> vbCrLf Then
strchar = Input(1, #fp)
If Not EOF(fp) Then
If strchar <> vbCr And strchar <> vbLf And strchar <> vbCrLf Then
strline = strline & strchar
End If
End If
Else
bolend = True
End If
Wend

If Len(strline) = 0 Then GoTo Nextline

tst = (Nz(Trim(Mid(strline, 1, 2))))

Select Case tst

Case "1A"
MsgBox ("This is the first line")
MsgBox tst
'rst.addnew to add a new record in your file
'If Len(tst) = 0 Then
' rst!Field1 = Null
'Else
' rst!Field1 = tst
'End If

tst = (Nz(Trim(Mid(strline, 3, 11))))
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field2 = Null
'Else
' rst!Field2 = tst
'End if

'etc for each field in this line

Case "3A"
MsgBox ("This is the second line")
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field3 = Null
'Else
' rst!Field3 = tst
'End If

tst = (Nz(Trim(Mid(strline, 3, 20))))
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field4 = Null
'Else
' rst!Field4 = tst
'End if

'etc for each field in this line

Case "3B"
MsgBox ("This is the third line")
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field3 = Null
'Else
' rst!Field3 = tst
'End If

tst = (Nz(Trim(Mid(strline, 3, 20))))
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field4 = Null
'Else
' rst!Field4 = tst
'End if

'etc for each field in this line

Case "4A"
MsgBox ("This is the fourth line")
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field3 = Null
'Else
' rst!Field3 = tst
'End If

tst = (Nz(Trim(Mid(strline, 3, 20))))
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field4 = Null
'Else
' rst!Field4 = tst
'End if

'etc for each field in this line

Case "4B"
MsgBox ("This is the fifth line")
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field3 = Null
'Else
' rst!Field3 = tst
'End If

tst = (Nz(Trim(Mid(strline, 3, 20))))
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field4 = Null
'Else
' rst!Field4 = tst
'End if

'etc for each field in this line

Case "4C"
MsgBox ("This is the sixth line")
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field3 = Null
'Else
' rst!Field3 = tst
'End If

tst = (Nz(Trim(Mid(strline, 3, 20))))
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field4 = Null
'Else
' rst!Field4 = tst
'End if

'etc for each field in this line

Case "5A"
MsgBox ("This is the last line")
MsgBox tst
'If Len(tst) = 0 Then
' rst!Field3 = Null
'Else
' rst!Field3 = tst
'End If

'NOT SURE IF YOU NEED TO READ ANYTHING FROM THIS LINE - LEAVE OUT THE NEXT PIECE IF NOT NEEDED
'tst = (Nz(Trim(Mid(strline, 3, 20))))
'MsgBox tst
'If Len(tst) = 0 Then
' rst!Field4 = Null
'Else
' rst!Field4 = tst
'End if

'etc for each field in this line

'UPDATE THE RECORD IN THE TABLE
'rst.Update

End Select

Nextline:

Wend

Close #fp

'rst.Close
'Set dbs = Nothing

End Function

The lines I commented out (apart from descriptive information) you will need to modify to suit your specific requirements.

 

[payback]

PS Sorry if I have made any typos etc.

 

[tpowers]

It seems to be working thank you, I will work with it a little bit and let you know, again thank you so very much for your help,