VBS or similar script to extract data from a huge file and rearrange it

**stek** · 5 January 2012, 12:14

Originally posted by Peoplesoft bloke View Post

I have a huge file (P14 for HMRC since you ask) that is in a daft HMRC format - it has a line that identifies the payee, followed by a variable number of lines of data. Each monetary value data item is on a single line, with a single code to identify it on the previous line. What I need to do is rearrange this so I get a row per EE with a column (fixed length or value separated - it doesn't matter).

I've been trying to write a .vbs script to do this (mainly by using Google to try and find similar code). Why vb? - The file has 1,079,198 rows and is therefore not really a candidate for excel - I don't have (and can't get) access and the data can't leave the premises (otherwise I could download some fancy freeware), but my programming skills are pretty meagre to say the least.

The only other viable tool I have access to is an oldish version of ultra edit (too old for scripting)

Any tips, links to sites would be greatly appreciated - bonus points awarded for "use excel, access, take file off site etc etc"

Can you get it on a Unix box and awk it?

**eek** · 5 January 2012, 12:16

VBS should be fine for that.

Create 2 file objects and open both of them (1 reads in the current file, 2 saves the old file)

then create a string that you can slurp data into until you reach the end of that payee at which point you can save the line to the second file.

The code below does most of what you want its old code tho and you will need to add logic to identify if its a new payee line.

Code:

dim payee

If objFSO.fileExists( "c:\temp\a.txt" ) Then
        'Open a file for reading 
        Set oInStream = objFSO.OpenTextfile( "c:\temp\a.txt", Forreading, False )

set oOutStream =fs.CreateTextFile("c:\test.txt",true)

lcount=0
        Do Until oInStream.AtEndOfStream
            
            Line = oInStream.readLine 
            // some validation logic.
// add logic to check line to see if its a new new payee record
//if new payee save string to second file
oOutStream .WriteLine(payee) 
payee=""
end if
payee=payee & "/t"&Line 
  
        Loop
        oInStream.Close 
        Set oInStream = Nothing
oOutStream.WriteLine(payee) 
oOutStream.Close 
Set oOuStream=Nothing
end if

**Peoplesoft bloke** · 5 January 2012, 12:43

Thanks a million both of you - sadly I can't get it onto a Unix box - awk was my first thought (well grep, then awk actually) some of these young lads here haven't even heard of it!

Eek - many thanks the flow control logic was my "missing link" which you have kindly and generously supplied - thanks again.

VBS or similar script to extract data from a huge file and rearrange it