I need to process the following csv with a dos command line to save it as a bat file. The file has a resizable header I need to delete, and keep the other lines once a specific string is found In this case, I only want to keep the lines after the string "Date" is found. an example of the file below:
CSV:
----
Report,Begin Date,End Date,Currency,Change Currency
Financial Report,2016-03-26 00:00:00.000 -0600,2016-03-27 00:00:00.000 -0600,USD,Change Currency
Method,Deposits,Withdrawals,Reversepayouts,Reversedeposits,Net
PAYPAL,200.00,0.00,0.00,0.00,200.00
VISA2,1650.00,0.00,0.00,0.00,1650.00
VISA3,190.00,0.00,0.00,0.00,190.00
DISCOUNT,200.00,0.00,0.00,0.00,200.00
Total:,2240.00,0.00,0.00,0.00,2240.00
Date,Affiliate,Username,Account Id,Method,Type,Amount,Transaction Id,Note
2016-03-26 00:36:01.746 -0600,JamesX,ad123,30153,VISA2,Deposit,32.0000,244258410,VISA2
2016-03-26 01:25:53.680 -0600,JamesX,ad123,30153,VISA2,Deposit,32.0000,244263044,VISA2
2016-03-26 02:26:05.776 -0600,ChristineY,Sar,30887,ARESYS,Deposit,200.0000,244267597,PAYPAL
2016-03-26 03:53:28.313 -0600,ChristineY,doo15,35088,VISA2,Deposit,100.0000,244271237,VISA2
2016-03-26 05:01:14.420 -0600,ChristineY,doo15,35088,VISA2,Deposit,320.0000,244273790,VISA2
2016-03-26 08:40:38.593 -0600,JamesX,ad123,30153,VISA2,Deposit,33.0000,244290455,VISA2
2016-03-26 10:08:43.230 -0600,xAZER,veso,36504,VISA3,Deposit,90.0000,244302244,VISA3
I would use a regular expression find/replace utility called JREPL.BAT. JREPL.BAT is pure script (hybrid JScript/batch) that runs natively on any Windows machine from XP onward.
The solution is a basic regex find/replace with a bit of user supplied JScript to handle the logic of which lines to discard.
If you want to discard the "Date,..." header line, then:
jrepl "^(Date,)?.*" "($1?i++:i)?$0:false" /jmatch /jbeg "var i=0" /f test.txt /o output.txt
If you want to preserve the header line, then only a slight change is needed:
jrepl "^(Date,)?.*" "($1?++i:i)?$0:false" /jmatch /jbeg "var i=0" /f test.txt /o output.txt
Use /o - if you want to overwrite the original file with the result.
Use call jrepl if you put the command within a batch script.
It is possible to to solve without user supplied JScript; but that requires the /m (multiline) switch, which loads the entire file into memory, so the maximum file size is limited to around 1GB.
Discard the header line:
jrepl "[\S\s]*?^Date,.*\n?([\S\s]*)" "$1" /m /f test.txt /o output.txt
Preserve the header line:
jrepl "[\S\s]*?(^Date,[\S\s]*)" "$1" /m /f test.txt /o output.txt