Transform CSV dataset.

[billy_bragg]

Hi. I just need to reformat some CSV output (see example below, original dataset has over 100,000 records).

This should be an easy one... but alas, I can not see it. I have cruised the forum and some VB type solutions using arrays look sexy - but I can't seem to implement them.

So, here is the problem; transform Dataset 1 into Dataset 2.

Any input would be appreciated.

Dataset 1:
********
--------START---------
"VARIABLE1","FREQ"
"1","50"
"2,"51"

"VARIABLE2","FREQ"
"1","123"
"2","23234"
"3","23432"
"4","342362"
"0",234"

"VARIABLE3","FREQ"
"1","123"
"2","21733"
"0","4638"
---------END----------

Dataset 2:
*********
--------START---------
"VARIABLE1","1","50"
"VARIABLE1","2","51"
"VARIABLE2,"1","123"
"VARIABLE2","2","23234"
"VARIABLE2","3","23432"
"VARIABLE2","4","342362"
"VARIABLE2","0",234"
"VARIABLE3","1","123"
"VARIABLE3","2","21733"
"VARIABLE3","0","4638"
---------END----------

[billy_bragg]

Ok, this is the best I could come up with... given the number of records the thing takes a week to complete; any suggestions for speed would be greatly appreciated.

'Global files'
let>input=c:\counts.csv
let>output=C:\output.csv
let>temp=c:\temp.txt
let>varlist=C:\varlist.txt

'Housekeeping (for repeated runs)'
Del>%output%
del>%temp%
del>%varlist%

'Global variables'
let>comma=,
let>k=0

'Start of extraction'
GetTime>time
WriteLn>%varlist%,result,Start: %time%
label>start
let>k=k+1
ReadLn>%input%,k,record
if>record=##NOFILE##,End
If>record=##EOF##,End
if>%record%=
DeleteFile>%temp%
goto>start
else
endif
IfFileExists>%temp%,continue,extract
Label>continue
let>new=
ReadLn>%temp%,1,variable
ConCat>%new%,%variable%,%record%
WriteLn>%output%,result,%new%
goto>start
label>End
GetTime>time
WriteLn>%varlist%,result,End: %time%

srt>extract
GetTime>time
let>comma=,
'Separate>%record%,%comma%,field
StringReplace>%record%,%comma%"Frequency",,var
WriteLn>%temp%,result,%var%
WriteLn>%varlist%,result, %time%: %var%
Message>Current Variable: %var%%CRLF%Start Line: %K%
end>extract

[Me_again]

Here's my solution (to the original question, so no timestamp)

let>comma=,
Let>k=1
Label>start
ReadLn>c:\test\csv1.txt,k,line
If>line=##EOF##,finish
Separate>line,comma,pce
MidStr>pce_1,1,4,var
If>var="VAR,newvar,dostuff
Label>newvar
Let>variable=pce_1
Goto>next
Label>dostuff
WriteLn>c:\test\csvout.txt,result,%variable%%comma%%pce_1%%comma%%pce_2%Label>next
Let>k=k+1
Goto>start
Label>finish

[billy_bragg]

Oh that's clean - I Love clever. Outstanding, thanks for taking the time!