0

I could (thanks to dbenham and its powerful JREPL.BAT) remove header row from CSV File with command 

jrepl "^(Date,)?.*" "($1?i++:i)?$0:false" /jmatch /jbeg "var i=0" /f test.txt /o output.txt

I now need to insert in this csv below the date in first column (here 2016-03-27) for every row and delete last row (total). Would jrepl do this too? Thanks!

Report,Begin Date,End Date,Currency,Change Currency
Activity Summary By Account,2016-03-27 00:00:00.000 -0600,2016-03-28 00:00:00.000 -0600,USD,Change Currency

Affiliate,Account,Screen Alias,Total Wagered,Total Payout,Net Win Loss,Percent Payout
FaridZ,BuF,BuFis,1153.00,828.00,325.00,71.81%
JohnX,adel,adel,104.70,71.70,33.00,68.48%
FaridZ,chat00,shat,49065.00,45987.50,3077.50,93.72%
,,Total:,"50,657.70","47,247.20","3,410.50",93.26%

Updated: screenshot of final csv output... Final output

dbenham
  • 127,446
  • 28
  • 251
  • 390
R_life_R
  • 786
  • 6
  • 26
  • Your question is not clear. Show what the output should look like. – dbenham Mar 27 '16 at 17:28
  • Hi, thanks dbenham for looking into this. I am going to add a screenshot (excel) of final output! – R_life_R Mar 27 '16 at 17:32
  • Rather than a screenshot, I think a simple text representation of the output would be better. – dbenham Mar 27 '16 at 17:33
  • Ok, I already posted the screenshot. Tell me if you need it in text and I will add it. Thanks! – R_life_R Mar 27 '16 at 17:35
  • Screenshot is OK. But the output shown has not deleted the Total: line, so I am confused as to your requirement. – dbenham Mar 27 '16 at 17:38
  • You are 100% right. I Did the "mockup" too quick and forgot to remove the "total" line which should not be included, rather deleted. – R_life_R Mar 27 '16 at 17:40

1 Answers1

1

This can be done efficiently with JREPL.BAT, but the solution is fairly complicated if you want to do everything with a single pass of the input data. I'm not sure that the solution is any simpler than writing a custom JScript or VBS script.

Note that I discovered a minor JREPL.BAT bug while developing this solution, so there is an updated version 3.8 at the link with a few bug fixes.

jrepl "^$|^,,Total:,.*|^.*?,(.+?),.*"^
      "i=1;''|false|if(ln==2){dt=$4;$0}else i?$0+','+((i++==1)?'Date':dt):$0"^
      /jmatch /jbeg "var i=0, dt" /t "|" /f test.txt /o output.txt

I used line continuation to make the code easier to read.

A bit of explanation of the solution is in order.

/JBEG defines a couple of variables that are needed during the find/replace operation.

  • dt - Holds the captured date string.
  • i - Used to control whether anything is appended:
    • if i=0 then no change
    • if i=1 then append the Date header
    • else append dt

I used /JMATCH along with the /T option with | as a delimiter. The /T option is similar to the unix tr command. For each delimited search in the find string, there is a corresponding JScript expression in the replacement string.

  • $1 search ^$ - Looks for an empty line
    replace i=1;'' - Triggers i so that subsequent non-empty lines have the date column appended. The replacement value for this line is an empty string.

  • $2 search ^,,Total:,.* - Looks for the final Total line
    replace false - Prevents the total line from being printed

  • $3 search ^.*?,(.+?),.* - Looks for a line with at least 3 fields, capturing the 2nd field in $4
    replace if(ln==2){dt=$4;$0}else i?$0+','+((i++==1)?'Date':dt):$0 - This is where most of the complicated logic resides:

    • If this is the 2nd line, then save the date string ($4) in dt and replace with the full matched string
    • else if i is not 0, then increment i and replace with the full matched string plus append string ',Date' the first time, else append the dt value
    • else i=0, so replace with the original string.
dbenham
  • 127,446
  • 28
  • 251
  • 390
  • you are the best. Thanks for this second help during a complicated week end, you saved the day twice. Brilliant !!! – R_life_R Mar 29 '16 at 23:13