Batch script to delete rown where column value is "IN" and "BR"

Question

I Have a csv file which has 35 columns and in 19th column i get these country values like IN, BR, US etc. I need to remove the rows where the country value in 19th column is IN and BR.

I tried the following but has many issues as i can use tokens upto 26 only and also not being able to use both IN and BR.

@echo off &setlocal
set /p "header="<"old.csv"
>"your new.csv" echo.%header%
for /f "usebackq skip=1 delims=, tokens=1-26*" %%a in ("old.csv") do (
  if not "%%t" == "BR" 
  (
    >>"your new.csv" echo.%%a,%%b,%%c,%%d.....,%%Z
  )
)

Why is only twenty six tokens an issue, you're only wanting to check the values allocated to the 19th record fields. What you probably need to do is to nest your `for /f` loop within another `for /f` loop, where the outer one retrieves all records except for the first, and the nested one checks the 19th token data. Please note however, and this is very important, a `for /f` loop treats multiple adjacent delimiters as a single one, so if there's empty fields in any of your records, it will not select the field data you require. e.g. ```1,2,,4,5,6,,,,10```, the 6th comma delimited token is `10`. — Compo, Jul 08 '22 at 10:15
Try `findstr /V "IN,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*$ BR,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*$" "old.csv" >> "new.csv"`, which can only work if no (quoted) fields contain ` on their own; the search expressions are anchored from the end of the lines, because `findstr` does not accept more than 16 character classes, if I recall correctly… — aschipfl, Jul 08 '22 at 12:37
Just a small point: you want not rows that in 19th column have "IN" **OR** "BR", right? (not both IN and BR, like you said). — Aacini, Jul 08 '22 at 15:28

Aacini · Answer 1 · 2022-07-08T15:43:12.980

This should work:

@echo off
setlocal EnableDelayedExpansion

set "remove=IN|BR"

set /P "header=" < "old.csv"

> "your new.csv" (
   echo %header%
   for /F "usebackq skip=1 delims=, tokens=1-19*" %%a in ("old.csv") do (
      if "!remove:%%~s=!" equ "%remove%" (
         echo %%a,%%b,%%c,%%d,...,%%s,%%t
      )
   )
)

From the FOR /? help screen:

If the last character in the tokens= string is an asterisk, an additional variable is assigned that receive the rest of the text in the line after the last analyzed token.

In other words, in previous code the %%t token receive all the columns after the 19th one...

score 0 · Answer 2 · answered Jul 08 '22 at 21:58

@echo off

setlocal

set "remove1=IN"
set "remove2=BR"

set /P "header=" < "old.csv"

> "your new.csv" (
   echo %header%
   for /F "usebackq skip=1 delims=" %%e in ("old.csv") do (
    for /F "delims=, tokens=19" %%b in ("%%e") do (
      if "%remove1%" neq "%%~b" if "%remove2%" neq "%%~b" (
         echo %%e
      )
    )
   )
)

[untested]

...but keep in mind the points raisd in the comments.

Batch script to delete rown where column value is "IN" and "BR"

2 Answers2