Re: variable size record length file parsing

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Matt Williamson" <ih8spam@xxxxxxxxxxx> wrote in message news:ubPlTOnlHHA.1776@xxxxxxxxxxxxxxxxxxxxxxx

Mike, your sub just gives me 250. I wrote this before I saw
your post and we're both heading in the same direction I think.
This runs in less than a second and gives me different sizes,

Are you sure we are both using the same data file? Or that you haven't got your own Sub and mine swapped around? I tried my own suggested code on *exactly* the data you supplied in your second post and it returns the length of each line and the total length of each "group of lines ending in X" in the ListBox (it could of course also display the data itself, but I left that out for test purposes), so I get something like the following in the ListBox:

25
213
9 247
25
183
40 248 etc

In the case of your own code, on exactly the same data, I get only the first line of data followed by a sequential list of numbers from 2 upwards, as follows:

GMA000000013J6000015 000 1
2
3
4 etc

In fact, looking at your own code (as shown below) that is exactly what I would expect it to return because it concatenates *only* the lines it finds with the "00000001" in the specified position, instead of "all lines from that point onwards until a different value is discovered". So the first and only line from the file that your code stores is:

GMA000000013J6000015 000

Thereafter it increments the "counter" at every line, looking for lines with that sequentially increasing value in the appriate position, and in the sample code provided it never finds any, with the counter racing ahead of any GMA000000n values it is ever likely to find. This causes it to repeatedly dump empty strings into the collection. In fact, at this end it is your own code that picks up just the first line whereas mine picks up every line correctly (except that I am currently displaying only the lengths of those lines for test purposes and have not yet done the index thing). So, are you sure we are both using the same data file, and that you haven't got the two blocks of code mixed up? I can see what you are attempting to do in your own code, but as it stands it doesn't yet do it.

Also, be wary of using CLng to convert parts of a string that might represent a specific value you are looking for, because it will return an error if it hits a string in which the text at that position cannot be legitimately converted into a Long. Try using Val (substring) instead, or perhaps Clng(Val(substring)). Another thing you might like to look out for is the possibility that some lines of data that are not actually one of the GM lines might also by chance contain "000000n" (or another substring string that converts to the current index) at the specified position. Looking at your sample data that appears to be a distinct possibility, so perhaps you might be wise checking for the GM as well.

There are others here who have given you various other ideas that you might also like to look into, but I think the very first thing you need to do is to ensure that both yourself and the group are working with the very same data file extract, which appears not to be the case if what you have said in this message is correct, unless of course you have mixed up the two blocks of code, which seems like a possibility? As I said earlier, with these things, the devil is in the detail ;-)

Mike

Public Function GetRecordSizes(sFile As String) As Collection

Dim f As Long, lSeq As Long
Dim sLine As String, sTmp As String

Dim gs As New Collection

f = FreeFile
lSeq = 1

Open sFile For Input As f
Do While Not EOF(f)
Line Input #f, sLine
'not the first or last lines
If InStr(sLine, "BOF") = 0 Then
If InStr(sLine, "EOF") = 0 Then
'check for record seq id
If CLng(Mid(sLine, 4, 8)) = lSeq Then
sTmp = sTmp & vbCrLf & sLine
Else
gs.Add Len(sTmp), CStr(lSeq)
lSeq = lSeq + 1
sTmp = ""
End If
End If
End If
Loop

Close f

Set GetRecordSizes = gs
Set gs = Nothing

End Function




.

Relevant Pages

  • Re: variable size record length file parsing
    ... Dim s1 As String, recLong As Long, fn As Long ... Public Function GetRecordSizes(sFile As String) As Collection ... Dim f As Long, lSeq As Long ... Dim sLine As String, sTmp As String ...
    (microsoft.public.vb.general.discussion)
  • Re: variable size record length file parsing
    ... into another string. ... Dim f As Long, lSeq As Long, lSize As Long ... Dim sLine As String, sTmp As String ...
    (microsoft.public.vb.general.discussion)
  • Re: 1.000er Trennzeichen in Word automatisch setzen
    ... Dim rTmp As Range ... Public Function MitPunkt(sTmp As String) As String ... If j> 0 Then ... sTmp = Left- 3) ...
    (microsoft.public.de.word)
  • Re: Euro
    ... Dim sTmp As String ... ' result probably depending on local settings ... If you need the value in a variable of type single, ...
    (microsoft.public.word.vba.general)
  • Re: 1.000er Trennzeichen in Word automatisch setzen
    ... Dim rTmp As Range ... Public Function MitPunkt(sTmp As String) As String ... If j> 0 Then ... sTmp = Left- 3) ...
    (microsoft.public.de.word)