Regular Expression with findstr (ms-dos)

Question

I am trying to use ms-dos command findstr to find a string and eliminate it from the file.

At the moment I can find an explicit string but I am really struggling with regular expressions. The file looks something like the below:

PLs - TULIP Report  
Output_Format, PLS - TULIP REPORT  
NUMLINES,    110907
VARIABLE_TYPES,T1,T8,I,T9,T2,N,N,N  
[[data below]]

The file is an export from some system and annoyingly has that header in it - so I would like to clean it before using SQL Loader to bring it into an Oracle database.

There's more than just the one file and all would have the same type of header but ever so slightly different in every file. Although I am happy to first remove the first 2 lines using hardcoded values, e.g.:

findstr /v "PLs - TULIP Report" "c:\myfiles\file1.PRO"  > "c:\myfiles\file1.csv"</code><br>
findstr /v "Output_Format, PLS - TULIP REPORT" "c:\myfiles\file1.csv" > "c:\myfiles\file2.csv"

(note how I do that in 2 steps - any suggestions to make this happen in a single step, would be massivelly appreciated)

The third line is mnore complicated for me, it will always be in that format:

NUMLINES,    110907

except that the number at the end would be different for each file. So how do I get to find this entire line using a regular expression? I have tried:

findstr /v /b /r "\D+ \s+ \d+"

but without any luck.

FYI, the data in [[data below]] looks like

*,"00000161",456823,"017896532","FU",23.95,3.34,20.61

etc .. Obviously, I do not want to modify the data area.

I hope the above makes sense,

Thanks

+ won't work use asterisk. Unless I got you wrong, this works (findstr /B "NUMLINES, *[0-9]*" dos.txt) . I also don't think your char classes \D, \s, \d are supported either. — Himanshu, Oct 23 '12 at 11:00
Thanks for that - I think I went for the incorrect complicated route! - just tested it on one of the files and it works fine. Would you know how to do the last line too? i.e. `code`VARIABLE_TYPES,T1,T8,I,T9,T2,N,N,N`/code` ... and the holy grail is to do all these lines in one go! :) ... Alternatively, I was also experimenting with the MORE command, since it will always be the first 4 lines of the text file that need to be zapped, I thought that would be a good contender, but it's very slow and it also hung for some reason. — leroyb, Oct 23 '12 at 11:09
Yes you can use this (findstr /B VARIABLE_TYPES, dos.txt). Note that this assumes you won't have VARIABLE_TYPES in your data. And if you are sure that first four lines are to be skipped yes you can use (more +4 dos.txt). — Himanshu, Oct 23 '12 at 11:15
Are you really using ms-dos? Or are you using a Windows (CMD.EXE) batch file? — dbenham, Oct 23 '12 at 14:53

Gabber · Answer 1 · 2012-10-23T12:40:42.480

1

You must exclude single lines, findstr cannot match multiple lines. Just separate the different regexes with a space

findstr /r /b /v "NUMLINES PLs Output_Format" *.txt 
                  ^regex1  ^2  ^3

Specifying /b allows you to find matches only at the beginning of the lines and /v excludes those lines.

EDIT:

Of course the usage is

 findstr /r /b /v "NUMLINES PLs Output_Format" yourfile > yourtarget

And in yourtarget you will find the data of yourfile except the lines excluded by the regex.

EDIT 2:

Based on your comments you need just to add VARIABLE_TYPES to your regex making it

findstr /r /b /v "NUMLINES PLs Output_Format VARIABLE_TYPES" yourfile > yourtarget

This is the way to complete the whole operation in one single instruction.

edited Oct 23 '12 at 12:40

answered Oct 23 '12 at 11:19

Gabber

5,152
6
35
49

Thanks very much Gabber - based on your answer, I have produced the following: `code`findstr /r /b /v "PLs - Tulip Report Output_Format, PLS - TULIP REPORT NUMLINES, [0-9] VARIABLE_TYPES," sourcefile > targetfile`code` and that worked a charm – leroyb Oct 23 '12 at 13:11
and obviously I can't use the mini-markdown tags .. fds – leroyb Oct 23 '12 at 13:13
**Be careful!** your edit will exclude also the lines beginning with a digit because of `NUMLINES, ` **THIS SPACE** `[0-9]`. Those will be considered as two different regular expressions!!!! Also to use code tag in the comments click *help* and you'll see the mini markdown format :) the code is surrounded by two **`** (I usually open the help and copy&paste the character in my comment) – Gabber Oct 23 '12 at 13:16

score 0 · Answer 2 · edited May 23 '17 at 12:27

Here is a one liner using regex that will exclude all four lines. (I used line continuation so that the code looks better.) Each line must match exactly. I allow for each line to end in any number of spaces because I wasn't sure of your format. Note - FINDSTR regex support is very limited and non-standard. There are many other FINDSTR quirks and bugs. See What are the undocumented features and limitations of the Windows FINDSTR command? for more info.

findstr /vrx /c:"PLs - TULIP Report *"^
             /c:"Output_Format, PLS - TULIP REPORT *"^
             /c:"NUMLINES, *[0-9]* *"^
             /c:"VARIABLE_TYPES,T1,T8,I,T9,T2,N,N,N *"^
        "c:\myfiles\file1.PRO"  >"c:\myfiles\file1.csv"

If all you need to do is skip the first 4 lines, then you normally should be able to use MORE. But there are some circumstances with large files where MORE can hang, but I can't remember the specifics. Also MORE will convert tabs into a series of spaces.

more +4 "c:\myfiles\file1.PRO"  >"c:\myfiles\file1.csv"

Another option is to use a FOR /F loop. The FOR /F skips empty lines, but I don't think that is a concern for you.

>"c:\myfiles\file1.csv" (
  for "usebackq skip=4 delims=" %%A in ("c:\myfiles\file1.PRO") do echo(%%A
)

If any of your data can begin with a ; then the code gets a bit uglier. You would then want to disable the EOL option by setting it to a line feed character.

set LF=^


::above 2 blank lines are critical - do not remove
>"c:\myfiles\file1.csv" (
  for usebackq^ skip^=4^ eol^=^%LF%%LF%^ delims^= %%A in ("c:\myfiles\file1.PRO") do echo(%%A
)

re the MORE command, I also tried that but it was much slower than using the findstr command — leroyb, Oct 23 '12 at 15:42

Regular Expression with findstr (ms-dos)

2 Answers2