Search code examples
csvbatch-filecommand-line-tooljrepl

Need to insert a column in csv and parse current date in it - JREPL.BAT?

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

Solution

  • 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.