Delete rows using batch file

I am trying to delete all lines starting with either GBP or USD , and also delete first line using batch script.I am using the below code which is not working as expected.


@echo off > data1.csv & setLocal enableDELAYedexpansion
for /f "tokens=* delims= " %%a in (data.csv) do (
set s=%%a
set s=!s:~3!
>> data1.csv echo.!s!
)
Sample.txt
Govinda2020Asked:
Who is Participating?
 
Steve KnightIT ConsultancyCommented:
Somewhat simpler would be to use findstr and more with no issues over any funny chars in the file etc:

more +1 data.csv | findstr /b /v "GBP USD" > output.csv

 more +1 skips the fist line
findstr /b = beginning of line, /v = exclude these ...

Steve
0
 
oBdACommented:
Try this:
@echo off
setlocal enabledelayedexpansion
set InFile=data.csv
set OutFile=data1.csv
if exist "%OutFile%" del "%OutFile%"
for /f "skip=1 delims=" %%a in ('type "%InFile%"') do (
	set Line=%%a
	set SkipLine=0
	set Currency=!Line:~0,3!
	if /i "!Currency!"=="GBP" set SkipLine=1
	if /i "!Currency!"=="USD" set SkipLine=1
	if !SkipLine!==0 echo.!Line!
)

Open in new window

0
 
Paul TomasiCommented:
I believe the proper way to do it is simply like this:

@echo off
setlocal enabledelayedexpansion

(for /f "tokens=* skip=1" %%a in (data.csv) do (
  set "str=%%a"
  if /i not "!str:~0,3!"=="gbp" (
    if /i not "!str:~0,3!"=="usd" echo !str!
  )
))>data1.csv

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Paul TomasiCommented:
Sorry Steve, you can't do that - it's TOO simple !! LOL

While you're at it, you may like to remove the spaces around the pipe and redirection symbols to conserve a few more characters. Oh, but don't forget the '/i' switch in FINDSTR. Like this:

more +1 data.csv|findstr /b /i /v "GBP USD">data1.csv
0
 
Steve KnightIT ConsultancyCommented:
Oh come on Paul... cutting out spaces!   The /i is only relevant if he wants it case-insensitive which may well be an issue I suppose.

Surprised you didn't get that one :-)

Steve
0
 
oBdACommented:
Sorry, but it's not that easy with findstr; that version will delete all lines containing "GBP" or "USD" anywhere in the line, not "[...] all lines starting with either GBP or USD".
So if there happens to be an entry "NZDUSD", it will be removed as well, and so will any currency exchange that contains "USD" or "GBP" anywhere in the string (like "XUSDXX").
To fix that, you'd need to work with regular expressions, and Microsoft has implemented their very own, stripped down, interpretation of REs in findstr ("|" as alternation isn't supported, for example).
And then "more" isn't actually needed to drop the header line:
findstr /V /I /R "^EUR ^GBP ^ac:string,ac:double$" "data.csv" >"data1.csv"

Open in new window

0
 
Paul TomasiCommented:
Steve

I modelled my first reply closely to Govinda2020's own code... as it wasn't a million miles off track (just a few thousand!).

Looking at the attached Sample.txt file (which I missed first time around) we can safely remove the '/i' switch...

more +1 data.csv|findstr /b /v "GBP USD">data1.csv
0
 
Paul TomasiCommented:
oBdA

more +1 data.csv|findstr /b /v "GBP USD">data1.csv

Works just great for me because of the '/b' switch in FINDSTR.
0
 
Paul TomasiCommented:
I can get this down to just 33 characters if the filenames are just 'A' and 'B' and the two spaces infront of '/b' and '/v' are removed:

more +1 A|findstr/b/v "GBP USD">B

BTW, you'll notice I did not enclose the filenames in double-quotes as there are no spaces in them !! LOL
0
 
oBdACommented:
That's true, missed that. But the "more" is still not required ...
findstr /V /I /B "EUR GBP ac:string,ac:double" "data.csv" >"data1.csv"

Open in new window

Which would allow for 4 bytes less, while we're at it:
findstr /b /v "GBP USD ac:" A>B

Open in new window

0
 
Paul TomasiCommented:
oBdA

I can improve upon that by removing the space infront of the '/b' and looking at the sample data 'a' would suffice instead of 'ac:'.

I've now got this down to a meagre 28 characters which beats your 31! LOL

findstr/b /v "GBP USD a" A>B
0
 
Paul TomasiCommented:
Finally, looking at the three-letter ISO Currency Codes, it's safe to use just 'GB' and 'US' instead of 'GBP' and 'USD'!

Just 26 characters and surely that must be it!

findstr/b /v "GB US a" A>B

LOL !!!
0
 
Steve KnightIT ConsultancyCommented:
All potentially valid and good fun to save a byte or or two... maybe valid in a 16k 1980's computer or embedded system but in a PC batch file in 2012?!

We can speculate that the first line is always as shown but why not just make sure in case the format changes and exclude the first line with a more +1.

Anyway upto the asker if we haven't lost him.  Aside from the 'extras' the first three posts should be OK, personally I like my suggestion!
0
 
Govinda2020Author Commented:
It's great to see experts express their expertise, I used the below , as I have to remove first three characters after removing rows with "GBP" or "USD". Any suggestions for improvment.

more +1 data.csv|findstr /b /i /v "GBP USD">data1.csv

@echo off > data2.csv & setLocal enableDELAYedexpansion
for /f "tokens=* delims= " %%a in (data1.csv) do (
set s=%%a
set s=!s:~3!
>> data2.csv echo.!s!
)

del "data1.csv"

exit
0
 
Steve KnightIT ConsultancyCommented:
Well you could do it all in one for loop but if you want to keep the findstr / more still you can change your for command to use the output without the intermediate data1.csv:

@echo off
setLocal enableDELAYedexpansion

(for /f "tokens=* delims= " %%a in ('more +1 data.csv^|findstr /b /i /v "GBP USD"') do (
set s=%%a
echo !s:~3!)
)> data2.csv

Steve
0
 
Govinda2020Author Commented:
That works perfect @ dragon-it , one more last question surely. I am trying to delete currency INR after above code(it's not in the sample file). Again I have used the 2 files, any suggestion.

@echo off
setLocal enableDELAYedexpansion

(for /f "tokens=* delims= " %%a in ('more +1 data.csv^|findstr /b /i /v "GBP USD"') do (
set s=%%a
echo !s:~3!)
)>data1.csv

findstr /b /i /v "INR" data1.csv > data2.csv
0
 
Steve KnightIT ConsultancyCommented:
You could do that - what you have there should work, or can just add an extra entry on the existing first findstr line "GBP USD INR".  You can add any number of entries here.  We can get more complicated but the basic check /B is beginning of line and it will exclude (/v) any entries space seperated in the " "

Steve
0
 
Govinda2020Author Commented:
That's fine , Is it possible to give 500 points to everyone who answered?
0
 
Steve KnightIT ConsultancyCommented:
No, sorry you can split evenly, or spread as you wish.  While we are here for the points it;s not the end of the world.  Just select one(s) that have been useful to you or you have used in the end

Steve
0
 
Paul TomasiCommented:
Hey Steve

...personally I like my suggestion!
Funnily enough, personally I like MY suggestion too!!

(Note the additional exclamation mark adding greater emphasis to my statement. LOL).

I'm good with a split... Was fun!
0
 
Paul TomasiCommented:
Govinda2020

Can I have the first 500 points please?...

LOL !!
0
 
Govinda2020Author Commented:
Very quick and helpful
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.