reading lines in text file

Hi,
I'm working on a project where I have to read through a text file. This text file contains records, each separated by the pipe character (|). Here's my question: after each pipe character, the 42nd line contains a number that I need to retrieve. The number will be different with each record and will not always be the same number of digits either. How can I write code that will, upon reading a pipe character, go to the 42nd line and read it? Any help or suggestions would be greatly appreciated. I'm also open to website suggestions that may help me!

Thanks!

Could you post a sample of the text file, I am having trouble following you thoughts.

s-

Here is a samle of the text file:

|
540885328*0643*R610.1*1*3304*1304281561
C0 CPI R610.1
TATE ANNE
F10061956
PO BOX 1099
FRANKLIN VA238510000
0000000000 N
D001CPI R610.1
F52149NOCD
800160394
ALLIANCE
100
J B HUNT
TATE DANIEL
YY02Y00000000

00000000X







E0 CPI R610.1
V762
0000000000000 000000 N
000000000000000000000000
UG56864 SHEFFIELD SHARON
1000 E601CPI R610.1 C 1540885328LRAMOS MD RENIGIO PO BOX 642 FRANKLIN VA238510000 EE
F001CPI R610.1
0905030000
01
11
5
88164

1
002000 ***This is the line I need to get***
000000
RAMOS RENIGIO 00000
J0 CPI R610.1
OB GYN PHYSICIANS, INCSOUTHAMPTON MEDICAFRANKLIN VA238510000
X0 CPI R610.1
006
010102010001
0001
00002000
00000000

000000000000000
1
| ***new record starts here with pipe char***
540885328*0643*R627.1*1*3057*1304281561
C0 CPI R627.1
KNIGHT ANGELA L
F08201983
1862 CHERRY GROVE RD N
SUFFOLK VA234320000
0002554333 F N
D001CPI R627.1
F62308NOCD
230722779
CIGNA
3173896

KNIGHT LYNN
YY03Y00000000

00000000X







E0 CPI R627.1
5409
0000000000000 000000 N
081603000000000000000000
UF15219 ZEILER DAVID Z
1000 E601CPI R627.1 C 1540885328LRAMOS MD RENIGIO PO BOX 642 FRANKLIN VA238510000 EE
F001CPI R627.1
0816030000
01
21
5
88304
26
1
008700
000000
RAMOS RENIGIO 00000
J0 CPI R627.1
LOUISE OBICI MEMORIAL PO BOX 1100 SUFFOLK VA234340000
X0 CPI R627.1
006
010102010001
0001
00008700
00000000

000000000000000
1
|


The number in the line I specified above is a dollar amount that needs formatted. It will change with every record, so I cannot search on a specific number.

Thanks!

The following code shows you for to open a text file (doesn't have to be .txt), grab each line of data, Loop through will you get to the 42 line after the pipe, reset counter after each pipe, and close the file when done



DIM FilePath as string
Dim Counter as string
Dim DollarAmount as double
Dim sRecord as string

FilePath = "C:\windows\File.txt" 'Identify the path to the file
Open FilePath For Input As #1 'Open the text file

Line Input #1, sRecord 'grab first line

Counter = 1 'start counter

Do While Not EOF(1) 'Continue to EOF = true

if Counter = 42 then ' 42 record after Pipe?
'Yes
DollarAmount = val(sRecord)
'Do what ever else you want to do with this record
Else
'No
If instr(0,srecord,"|" then 'Does this record have a pipe in it
'Yes
Counter = 1 'reset the counter
Endif
Endif

Line Input #1, sRecord 'Get next record
Counter = Counter + 1 'Increment counter
Loop


Close #1 'Close the text file

sbaxter,
Thanks so much for your reply. Your code has helped me tremendously. I've got another question (for you or anyone who can help!):

Instead of reading the 42nd line after each pipe, my code has to read the line after each pipe that has "CLM" in it. I adjusted the code appropriately and got that to work.

The main idea of this project is to read through a text file, look for the pipe character. At each pipe, grab a four digit ID and the dollar amount, search for the next pipe, grab that ID and compare them. If they are the same, just add on to the ID Counter and get the amount. Only print each ID once, along with how many times it apeared in the file and the total of all of the amounts for it. This is where I have a problem. I can't get the code to read the dollar amount on the "CLM" line for each ID. When I run the program as is, it prints out each dollar amount in the entire text file next to each different ID. For example, if there are two different ID's throughout the file, 2701 and 2702, the code prints these ID's and next to each of them prints both of the amounts. I only want the amounts that pertain to the given ID's to print next to them.

Here is the code I've got so far:

IDCount = 1
Pipecnt = 0
For Counter = 1 To Len(FileString)
If Asc(Mid(FileString, Counter, Len(FileString))) = 124 Then
sContractName = Mid(FileString, Counter, 15)
ContractName = Mid(sContractName, 12, 2)
SiteName = Mid(sContractName, 14, 2) 'this is the ID

If SiteName <> StrSiteName Then
If Counter > 1 Then
Debug.Print Chr(9), Trim(CStr(IDCount))
IDCount = 1
End If
Debug.Print Trim(ContractName & SiteName)
Call PContractName
Call GetLine
Else
'Increment the id count
IDCount = IDCount + 1
End If

StrSiteName = SiteName
Pipecnt = Pipecnt + 1

End If

Next

Public Sub GetLine()
Seek #1, 1
Line Input #1, sRecord 'grab first line
Counter3 = 0 'start counter
Do While Not EOF(1) 'Continue to EOF = true

If InStr(1, sRecord, "CLM") Then
'Yes
DollarAmount = sRecord
Call GetDelimitedField(3, DollarAmount) 'Do what ever else you want to do with this record
Else
'No, Counter3 does NOT equal 16
If InStr(1, sRecord, "|") Then 'Does this record have a pipe in it
'Yes
Counter3 = 0 'reset the counter
End If
End If

Line Input #1, sRecord 'Get next record
Counter3 = Counter3 + 1 'Increment counter
Loop
End Sub


I appreciate any help anyone can give me with this!!
Thanks!
And thanks again, sbaxter, for your previous help!

I think you have problem in the logic

If I understand you, you are wanting to search through this text file and when you have a CLM appear, grab the ID and Value for that record. When you see the next CLM grab ID and Value, and if the IDs are the same as the previous one grabbed add the values together.

What you are doing is

Seeing if the IDs are the same and then search the text file when you should be searching the text files to see if IDs are the same.

Hope this helps

S-

sbaxter,
Thanks for your response. You are correct in what you think I'm doing. I don't quite understand what you mean, though. Would you be kind enough to explain it a different way?
Thanks!

Based on the assumption that you are searching One Text file

Your comparison and addition code needs to be embedded within the looping of the text file.

You are currently doing a comparison and then looping when the comparison needs to be inside the loop (Inside of GetLine, Not calling getline)

Hope this helps

S-

I'm using the Line Input # code to do something similar, but it's not inputting the entire text file into the string that I'm searching...

Public Function fParseLoop(ByVal lngCmptnID As Long, ByVal strExt As String, _
ByVal strPath As String, ByVal strTable As String)

Dim SearchFor As String
Dim fnum As Integer

Dim str1 As String 'complete html from web
Dim str3 As String 'position of first occurrence of file type
Dim str4 As String 'html without header (starts with first image name)
Dim str5 As String 'display name of image
Dim str6 As String 'upload date
Dim str7 As String 'file size

Dim strFilename As String
Dim rst1 As DAO.Recordset

SearchFor = "." & strExt
Set rst1 = CurrentDb.OpenRecordset(strTable)

fnum = FreeFile(0)

'Open file for reading and load HTML code into a string
Open strPath For Input As #fnum
Line Input #fnum, str1

.. str1 is about a third of the entire file. Any reason why this would be happening?