?
Solved

Delete rows using batch file

Posted on 2012-08-20
22
Medium Priority
?
5,887 Views
Last Modified: 2012-08-20
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
0
Comment
Question by:Govinda2020
  • 9
  • 6
  • 4
  • +1
22 Comments
 
LVL 86

Assisted Solution

by:oBdA
oBdA earned 400 total points
ID: 38311067
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
 
LVL 11

Assisted Solution

by:paultomasi
paultomasi earned 800 total points
ID: 38311126
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
 
LVL 43

Accepted Solution

by:
Steve Knight earned 800 total points
ID: 38311275
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:paultomasi
ID: 38311345
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
 
LVL 43

Expert Comment

by:Steve Knight
ID: 38311370
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
 
LVL 86

Expert Comment

by:oBdA
ID: 38311436
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 38311454
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 38311470
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 38311480
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
 
LVL 86

Expert Comment

by:oBdA
ID: 38311499
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 38311560
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 38311582
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
 
LVL 43

Expert Comment

by:Steve Knight
ID: 38311858
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
 

Author Comment

by:Govinda2020
ID: 38311942
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
 
LVL 43

Expert Comment

by:Steve Knight
ID: 38311996
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
 

Author Comment

by:Govinda2020
ID: 38312190
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
 
LVL 43

Expert Comment

by:Steve Knight
ID: 38312252
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
 

Author Comment

by:Govinda2020
ID: 38312314
That's fine , Is it possible to give 500 points to everyone who answered?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 38312327
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 38312483
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 38312495
Govinda2020

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

LOL !!
0
 

Author Closing Comment

by:Govinda2020
ID: 38312871
Very quick and helpful
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month12 days, 15 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question