[RESOLVED] Random Access Reading of a Non-Standard Text file
I hope someone can give me a little advice on how to proceed.
One of our customers is going to be sending me a flat file (ASCII-Text) that I have to break down and scan.
Every line ends in a CrLf - So I know I can use StreamReader and ReadLine(). I did get it to work on one of the test files they sent.(A very Short file) However, the file is not delimited at all and every field is padded to the full length they specified.
Here's the format of the file:
It consists of a header Line, some quantity of detail lines, and a Trailer line.
- Header-[1 Record only]9 Fields- 63 bytes including Crlf.
- Detail Lines[Highest record count up to 630,000 records](1 detail line is described) 60 Fields-1117 bytes Including crlf.
- Trailer - [1 Record only] 33 Fields - 395 bytes including crlf.
I figure I can just break the Header and Trailer lines with a Left, Mid, Right function for each field after I do a readline.
When I look at the Detail Line description and the quantity(OMG I hope they are exagerating) I wonder if I can somehow turn it into a random access ADODB file so that each field can be described in a structure.
The question I have is, How can I read the detail record starting from position 64 one record at a time, until I get to the beginning of the Trailer?
I know I can pull all the Detail Lines out with a MID(Detail,64,no_of_Rec*1117), but if there are as many records as they say, that's 700 Megs of records. I don't think that a string will hold that much.
Any help or insite that you can suggest will be deeply appreciated.
Thanks
FrankG
Re: Random Access Reading of a Non-Standard Text file
you're looking at it from a vb6 point of view. is this a vb.net question?
a streamreader + readline would read the file line by line. alternatively you can specify a starting position + how many characters to read. i can't see how you're going to split a line into fields if they're not delimited in any way?
Re: Random Access Reading of a Non-Standard Text file
you could read it in blocks and decode each line using ReadLine as you intended (ReadLine will do it and end the line when a Cr, Lf or CrLf is found).
The first line would be the header (the crlf wont be in the line) then read the next line check its length >395 if so it is a detail line so process it if it is <400 it is the Trailer and should be followed by an eof
Regarding processing it i'd be tempted myself to go with an xml type structure, open a new file and as you process each element then output it to this file.
EDIT sorry .paul same point ;)
Re: Random Access Reading of a Non-Standard Text file
Are you saying that the file looks like:
Header - 63 Bytes - required always this size
Detail - unknown amount of bytes
Trailer - 395 bytes - required always this size
If yes
Code:
Dim b(1023) As Byte
Dim fs As New FileStream("c:\foo.txt", FileMode.OpenOrCreate)
Dim trlr As Long = fs.Length - 395
fs.Seek(0, SeekOrigin.Begin)
fs.Read(b, 0, 63)
fs.Seek(trlr, SeekOrigin.Begin)
fs.Read(b, 0, 395)
fs.Close()
Re: Random Access Reading of a Non-Standard Text file
Quote:
Originally Posted by
dbasnett
Are you saying that the file looks like:
Header - 63 Bytes - required always this size
Detail - unknown amount of bytes
Trailer - 395 bytes - required always this size
If yes
Code:
Dim b(1023) As Byte
Dim fs As New FileStream("c:\foo.txt", FileMode.OpenOrCreate)
Dim trlr As Long = fs.Length - 395
fs.Seek(0, SeekOrigin.Begin)
fs.Read(b, 0, 63)
fs.Seek(trlr, SeekOrigin.Begin)
fs.Read(b, 0, 395)
fs.Close()
the op says each detail field is also fixed length "60 Fields-1117 bytes Including crlf" but the quantity varies
Re: Random Access Reading of a Non-Standard Text file
Hey Thanks for the input.
Let me see if I can answer your questions...
.paul.
vb.net.
Although I have sometimes gone to earlier versions of vb just to get the job completed in the time allotted.
Also, splitting the lines shouldn't be overly hard. I do know that each line is 1117 bytes long. I also know the length of each field in the line and that the customer is padding each of the fields to capacity. For example, if a field called Quantity has a length of 10 bytes and a value of 1, it will be written in the record as "0000000001". I also know that if the value is nothing, it will be written as "0000000000". I also know the order of the fields, so I should be able to tell where any detail is. What worries me is the length of time to the line by line process.
dbasnett,
I know that each detail record is 1117 bytes. And I know each of the fields and their order. So Readline() will work, but it's the processing time that has me worried. In reality, I have to make 2 passes through each file. The first pass I have to read each detail line and validate it. If any detail line fails, then the whole report is skipped. The second pass actually has to format the output to ftp to the Mainframe for printing
About your code,
shouldn't the byte array has been b(1117) rather than b(1023)?
I am able to determine the amount of detail records knowing the information I do. Like this.
Code:
Dim HeaderLength As Long = 63
Dim TrailerLength as Long = 395
Dim DetailRecordLength As Long = 1117
Dim DetailRecordCount As Long
Dim f as New FileInfo(inList(x).ToString) 'InList() is a directory listing
Dim sz As Long = f.Length 'File size
'Determine the Quantity of detail records
DetailRecordCount = (sz - (HeaderLength + TrailerLength)) / DetailRecordLength
Using some of the info above, for the Detail Records wouldn't your first fs.Read be fs.Read(b, 64, 1117)? Or would it be first, fs.Seek(0, 64) and then fs.Read(b,0,1117)?
Megalith,
As I mentioned above, it's the separate passes that make me to cringe.
I've had very little experience in XML, but are you suggesting using, 1) Readline, 2) Process fields, 3) Using the XMLTextWriter to write a temp file?
Thank you all for your input.
I am going to try some of your suggestion.
FrankG
Re: Random Access Reading of a Non-Standard Text file
Are you saying that the file looks like:
Header - 63 Bytes - required always this size - Yes?
Detail - 1117 Bytes each, 1-N details?
Trailer - 395 bytes - required always this size - Yes?
Something like this:
Code:
Dim b(1116) As Byte
Dim fs As New FileStream("c:\foo.txt", FileMode.OpenOrCreate)
Dim trlr As Long = fs.Length - 395
fs.Seek(0, SeekOrigin.Begin)
fs.Read(b, 0, 63) 'read header
Do While fs.Position < trlr
fs.Read(b, 0, 1117)
'process
Loop
fs.Seek(trlr, SeekOrigin.Begin) 'call me paranoid
fs.Read(b, 0, 395)
Re: Random Access Reading of a Non-Standard Text file
Hey again Frank, well sort of yes, I wasn't sure on how you planned to process the data. tbh though the time taken to process the fields one by one wont be any less than processing the entire lot, the problem with processing the entire lot however, as you said in your initial post, is that of memory. Processing each line will save the resources your app will consume massively.
As dbasnett says in this last example you should process each line, if this means to a local file, and then send the entire completed file at the end, or to open the file remotely and populate it that way is up to you. Myself i would put it local and then ftp the finished file at the end of the process.
