remove header from CSV file when string is found (ms dos command line)

Question

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

The string "Date" is on the first line. Did you just want to remove the first line, or did you only want to keep the lines that start with the date? — SomethingDark, Mar 26 '16 at 22:02
You are right to ask... Only the from the line starting with "DATE". — R_life_R, Mar 26 '16 at 22:05

score 2 · Answer 1 · answered Mar 26 '16 at 20:38

@ECHO OFF
SETLOCAL
SET "sourcedir=U:\sourcedir"
SET "destdir=U:\destdir"
SET "filename1=%sourcedir%\q36240256.csv"
SET "outfile=%destdir%\outfile.csv"
SET "reproduce="
(
FOR /f "usebackqdelims=" %%a IN ("%filename1%") DO (
 IF NOT DEFINED reproduce (
  ECHO(%%a|FINDSTR /b /L /c:"Date," >NUL
  IF NOT ERRORLEVEL 1 SET "reproduce=y"
 )
 IF DEFINED reproduce ECHO(%%a
)
)>"%outfile%"

GOTO :EOF

You would need to change the settings of sourcedir and destdir to suit your circumstances.

I used a file named q36240256.csv containing your data for my testing.

Produces the file defined as %outfile%

Set the reproduce flag to nothing (so it is undefined)

Read each file-line. If reproduce is undefined, use findstr to see whether the line /b begins with /L the literal /c: constant-string "Date,", disposing of any output.

If the errorlevel produced from the findstr is not >1 (ie. 0) then set reproduce to something.

If reproduce is set, then regurgitate the line and don't worry further about the slow findstr for more blinding vitesse...

dbenham · Accepted Answer · 2016-03-27T11:36:45.303

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

your expertise could help me again! I have the same issue with this file below, but I also need to insert the date in first column — R_life_R, Mar 27 '16 at 12:38
@RenaudDUGERT - That sounds like a new question. But if you want to use JREPL.BAT, you should learn regular expressions, as well as rudimentary JScript. Full JREPL.BAT documentation is available from the command line via `jrepl /?`. You ought to try to solve it yourself before you ask a question. — dbenham, Mar 27 '16 at 14:45

remove header from CSV file when string is found (ms dos command line)

2 Answers2