0

This is my first time working with batch files. I am trying to extract certain columns from original csv and pipe output to new csv. The following code is what I wrote based on this link:

https://stackoverflow.com/a/17557532/16034206

@echo off 
setlocal EnableDelayedExpansion

Rem for /f "skip=1 usebackq tokens=1,2,10,11 delims=," %%i in (sample.csv) do @echo %%i,%%j,%%k,%%l >>output.csv
echo "Your script is starting..."

FOR /F "skip=1 usebackq delims=" %%L in (sample.csv) DO (
    set "line=%%L,,,,,,,,"
    set "line=#!line:,=,#!"
    FOR /F "tokens=1,2,10,11 delims=," %%a in ("!line!") DO (
        set "param1=%%a"
        set "param2=%%b"
        set "param10=%%c"
        set "param11=%%d"
        set "param1=!param1:~1!"
        set "param2=!param2:~1!"
        set "param10=!param10:~1!"
        set "param11=!param11:~1!"
        if "%%~A"=="RH" echo !param1!, !param2!, !param10!, !param11! >> output.csv
    )
)

echo "Your script has completed"

I am looking to apply logic to check param1 contains a substring "@gmail.com" AND that param10 starts with a specific string "100" before outputting that specific row of 4 columns into the csv.

I checked how to use if-statement from this link: https://stackoverflow.com/a/17474377/10671013 but I have not found any links on SO discussing "containing substring" or checking for "starting with a string". Please advise.

experiment unit 1998X
  • 677
  • 1
  • 7
  • 18
  • 2
    `if not "!param1:@gmail.com=! == "!param1!" if "!param10:~0,3!" == "100" echo ...` (btw: you use `%%~A`, but there is no `%%~A` (`for` variables are case sensitive)) – Stephan Jul 22 '22 at 14:37
  • oh, i havent changed that for the new logic yet. I have been trying to find relevant code for that. Much thanks, I ll try it right now! – experiment unit 1998X Jul 22 '22 at 14:40
  • Do I add it at the end of the inner for-loop? I am getting a `3!" == "100" echo !param1!, !param2!, !param10!, !param11! >> output.csv was unexpected at this time.` error – experiment unit 1998X Jul 22 '22 at 14:45
  • 2
    Oops - I missed a quote in the first `if` – Stephan Jul 22 '22 at 14:51
  • haha, for a moment I thought it didnt work since there was no output, but turns out I specified the wrong column. Thanks a bunch! if you dont mind posting it as an ans so I can accept it? – experiment unit 1998X Jul 22 '22 at 14:59

1 Answers1

2

Remove the substring you look for from the first column and compare it with the original string, if not equal (string contains substring), check the first three characters of the other column. (This substring substitution is case insensitive):

if not "!param1:@gmail.com=!" == "!param1!" if  "!param10:~0,3!" == "100" echo ...
Stephan
  • 53,940
  • 10
  • 58
  • 91
  • Hi @Stephan, I just found that there are a few outliers where although the param10 starts with 100, but param 1 is completely null. However it is still output into the csv. Why does the first if statement not filter this out? – experiment unit 1998X Jul 23 '22 at 05:46
  • 1
    because there is no `@gmail.com` in an empty string. So there is nothing to remove. So the empty string stays the same (a.k.a empty) (same scenario as a non-empty string without the searched substring). – Stephan Jul 23 '22 at 06:14
  • 1
    Another `if` should cure that: `if not "!param1:@gmail.com=!" == "!param1!" if "!param10:~0,3!" == "100" if not "!param1!" == "" echo ...` – Stephan Jul 23 '22 at 06:19