Extracting a variable number of records out of a textfile 1

[mart1n0]

Hi,

I have a big (more than a GB) textfile with a lot of fields in it out of which I wan't to extract a few fields and put these in a new textfile.

The textfile (ORIGNINAL.txt) is representing basic data of a lot of companies. The even lines have a fixed layout like this:
[tt]
Field|Length|Beginning of the field|Description
_______________________________________________
1| 9| 1|Id
2| 2| 10|Record type
3| 8| 12|Date
...
27| 4| 150|Amount of ratios in line 2
_______________________________________________
[/tt]

The uneven lines have an amount of ratios, the amount is described in field 27 in the previous line.

I want to make a textfile (RESULT.txt) where each line represents a ratio of a company. So the structure would be:
the company id + ratioid + ratio for year 2 + ratio for year n-1 - new line ....
So for each company you get as much lines as there are ratios of wich the amount is in field 27 in the previous line.

I don't know if this explanation is clear enough, anyhow I hope somebody can help me.

 

[EBGreen]

Could you post a bit of the actual file as is. I'm not getting a clear picture of the existing structure in my head.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[ClayG]

Somthing like ..?

set readOpen = FSO.OpenTextFile( 'ORIGINAL.TXT', ForReading, CreateFalse)
set writeOpen = FSO.OpenTextFile( 'RESULT.TXT', ForWriting, CreateTrue)
While not readOpen.AtEndOfStream
    textline = readOpen.ReadLine
    companyid = Mid( textline, 1,9)
    numratios = CInt( Mid( textline, 150,4))
    allratios = ""
    for rationum = 1 to numratios
        ratioline = readOpen.ReadLine
        allratios = allratios +","+ ratioline '(?Whatever?)
        next
    writeOpen.Writeline companyid + ratioline
    wend
readOpen.Close
writeOpen.Close

'* Put constants first:
Const ForReading = 1, ForWriting = 2,CreateTrue = True, CreateFalse = False

A more robust approach would be to recognise the record type, using a 'state' instead of the inner loop, but thats harder to explain.

 

[mart1n0]

Thnx ClayG,

My problem is solved. Works like a charm now (and keeps the processor warm .

The records where a bit more complicated then my first question, but i guessed that if i could solve the simplified, that the complicated would work too.

Anyway this does the job now:

Dim FSO 'File System Object
Dim readOpen, writeOpen, writeOpen2 'open importfile en exportfile
Const ForReading = 1, ForWriting = 2,CreateTrue = True, CreateFalse = False


Set fso = CreateObject("Scripting.FileSystemObject")
Set readOpen   = FSO.OpenTextFile( "totaal.txt", ForReading, CreateFalse)
Set writeOpen  = FSO.OpenTextFile( "RESULT 1.TXT", ForWriting, CreateTrue)
Set writeOpen2 = FSO.OpenTextFile( "RESULT 2.TXT", ForWriting, CreateTrue)


While not readOpen.AtEndOfStream
    	firstRecord = readOpen.ReadLine
    	companyid = Mid( firstRecord, 1,9)
    	numratios = CInt( Mid( firstRecord, 150,4))
	finRatios = ""
	For i = 0 to 63
		finRatios = ratios & Mid(firstRecord,(162 + (i*13)),13) & Chr(9)
		Next
	writeOpen2.Writeline  companyid + Chr(9)+ _
		Mid(firstRecord, 10,  2) + Chr(9) + Mid(firstRecord, 12,  8) + Chr(9) + Mid(firstRecord, 20,  8) + Chr(9) + _ 
		Mid(firstRecord, 28,  8) + Chr(9) + Mid(firstRecord, 36,  8) + Chr(9) + Mid(firstRecord, 44,  8) + Chr(9) + _ 
		Mid(firstRecord, 52,  8) + Chr(9) + Mid(firstRecord, 60,  8) + Chr(9) + Mid(firstRecord, 68,  8) + Chr(9) + _ 
		Mid(firstRecord, 76,  8) + Chr(9) + Mid(firstRecord, 84,  8) + Chr(9) + Mid(firstRecord, 92,  8) + Chr(9) + _ 
		Mid(firstRecord,100,  8) + Chr(9) + Mid(firstRecord,108,  3) + Chr(9) + Mid(firstRecord,111,  3) + Chr(9) + _ 
		Mid(firstRecord,114,  3) + Chr(9) + Mid(firstRecord,117,  2) + Chr(9) + Mid(firstRecord,119,  2) + Chr(9) + _ 
		Mid(firstRecord,121,  2) + Chr(9) + Mid(firstRecord,123,  5) + Chr(9) + Mid(firstRecord,128,  4) + Chr(9) + _ 
		Mid(firstRecord,132,  5) + Chr(9) + Mid(firstRecord,137,  4) + Chr(9) + Mid(firstRecord,141,  5) + Chr(9) + _ 
		Mid(firstRecord,146,  4) + Chr(9) + Mid(firstRecord,150,  4) + Chr(9) + Mid(firstRecord,154,  4) + Chr(9) + _ 
		Mid(firstRecord,158,  4) + Chr(9) + finRatios    	


    	secondRecord = readOpen.ReadLine
    	For rationum = 0 to numratios-1
		allratios = ""
    		allratios = allratios + Chr(9)+ Mid(secondRecord, 1+(49*rationum+11), 7) + Chr(9) & _
		Mid(secondRecord, 8+(49*rationum+11),13) + Chr(9) & _
		Mid(secondRecord, 9+(49*rationum+11), 1) + Chr(9) & _
		Mid(secondRecord,22+(49*rationum+11),13) + Chr(9) & _
		Mid(secondRecord,23+(49*rationum+11), 1) + Chr(9) & _
		Mid(secondRecord,36+(49*rationum+11),13) + Chr(9) & _
		Mid(secondRecord,49+(49*rationum+11), 1)
		writeOpen.Writeline companyid + allratios
    		Next    	
	Wend