Read from csv file and extract specific columns of data based on the value of the first column

Question

This is my first batch program, and I searched the Internet, but am still trying to write a solution.

I have the following CSV file:

"RH",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005, "FH",01 "SH",2013/06/14 00:00:00 -0400,2013/06/14 23:59:59 -0400,"LQ3SUEEWPWKL6","" "CH","TransactionID","InvoiceID", ...... 

I am trying to write a simple program to do the following:

• If column1 = "RH", then retrieve the value of column2 (2013/06/15 02:14:58 -0400)
• If column1 = "SH", then extract the value of column4 (LQ3SUEEWPWKL6)

and channel output to a file.

---

This is my code so far, but the if condition is not working for me

 @echo off :: Set input file in variable ::Set _InputFile=%1 :: Store input line into different variables FOR /F "tokens=1-18* delims=," %%A IN (%_InputFile%) DO ( Set _var1=%%A Set _var2=%%B Set _var3=%%C Set _var4=%%D Set _var5=%%E Set _var6=%%F Set _var7=%%G Set _var8=%%H Set _var9=%%I Set _var10=%%J Set _var11=%%K Set _var12=%%L Set _var13=%%M Set _var14=%%N Set _var15=%%O Set _var16=%%P Set _var17=%%Q Set _var18=%%R IF "%_var1%"=="RH" echo %var2% ) 

---

My CSV file looks great in Excel and Notepad, but when I run the script to display the first variable, it looks like some garbage characters are in front of “RH” on the first record - I cannot get around it, since I need to extract additional column data if var1 = "RH":

 "RH" FH 01 SH CH TransactionID,PaymentTrackingID, SF SF SC RF CAD,CR,0 RF USD,CR,0 RC FF 

Answer 1

 ( FOR /F "tokens=1-18* delims=," %%A IN (%_InputFile%) DO ( if "%%~A"=="RH" echo %%~B if "%%~A"=="SH" echo %%~D ) )>youroutputfilename 

It should work - there is no need to assign all values ​​to different variables - BUT, if you plan to use them, then

 FOR /F "tokens=1-18* delims=," %%A IN (%_InputFile%) DO ( ... Set _var17=%%Q Set _var18=%%R CALL :PROCESS ) ... GOTO :EOF :PROCESS IF %_var1%=="RH" echo %_var2% IF %_var1%=="SH" echo %_var4% GOTO :EOF 

Note that with this method, since you assign %%x to _varx , then if %%x quoted, quotation marks will be included in the assigned value. To remove enclosed quotes (if they exist), use SET _varx=%%~x .

---

Application 20130703-1956Z for a problem with OP

 @ECHO OFF SETLOCAL SET _Inputfile=u:\noname1.txt ( FOR /F "tokens=1-18* delims=," %%A IN (%_InputFile%) DO ( SET "RH=" SET "SH=" ECHO(%%A|FINDSTR /l /c:"\"RH\"" >NUL IF NOT ERRORLEVEL 1 SET RH=Y ECHO(%%A|FINDSTR /l /c:"\"SH\"" >NUL IF NOT ERRORLEVEL 1 SET SH=Y if DEFINED RH echo %%~B if DEFINED SH echo %%~D ) )>u:\youroutputfilename TYPE u:\youroutputfilename del u:\youroutputfilename echo========First way ( FOR /F "tokens=1-18* delims=," %%A IN (%_InputFile%) DO ( SET _var1=%%A SET "RH=" SET "SH=" CALL :process if DEFINED RH echo %%~B if DEFINED SH echo %%~D ) )>u:\youroutputfilename TYPE u:\youroutputfilename del u:\youroutputfilename echo========Second way SETLOCAL ENABLEDELAYEDEXPANSION ( FOR /F "tokens=1-18* delims=," %%A IN (%_InputFile%) DO ( SET _var1=%%A IF "!_var1:~-4!"==""RH"" echo %%~B IF "!_var1:~-4!"==""SH"" echo %%~D ) )>u:\youroutputfilename TYPE u:\youroutputfilename del u:\youroutputfilename echo========Third way ENDLOCAL GOTO :EOF :process IF "%_var1:~-4%"==""RH"" SET RH=Y IF "%_var1:~-4%"==""SH"" SET SH=Y GOTO :EOF 

Answer 2

You have a parsing problem. First, complete the for loop with c ) , after , you can use the new variables:

 @echo off :: Set input file in variable ::Set _InputFile=%1 :: Store input line into different variables FOR /F "tokens=1-18* delims=," %%A IN (%_InputFile%) DO ( Set "_var1=%%A" Set "_var2=%%B" Set "_var3=%%C" Set "_var4=%%D" Set "_var5=%%E" Set "_var6=%%F" Set "_var7=%%G" Set "_var8=%%H" Set "_var9=%%I" Set "_var10=%%J" Set "_var11=%%K" Set "_var12=%%L" Set "_var13=%%M" Set "_var14=%%N" Set "_var15=%%O" Set "_var16=%%P" Set "_var17=%%Q" Set "_var18=%%R" ) IF "%_var1%"=="RH" echo %var2% 

Answer 3

You need to enable pending extension :

 @echo off setlocal EnableDelayedExpansion set "_InputFile=..." for /f "tokens=1-18* delims=," %%A in (%_InputFile%) do ( Set _var1=%%A Set _var2=%%B ... if " !_var1! "=="RH" echo !_var2! ) 

Answer 4

since there was no answer to the question “why does my line begin with“ RH ”? I will make several graves.

Thus,  comes from a specification (order byte of order), which indicates that the file is in UTF, and a way to write bytes, if necessary. for the answer: you can use

 if x%_var1:RH=%x NEQ x%_var1%x (echo %_var2%) 

this will check if RH is in% _var1% (if after replacing RH in var it has not changed, RH is not in var) which means that Bom is here or not, it does not matter. Although, if you need an exact match, you will have problems.

another way to deal with this is to not include bom in your file, which means saving in either ASCII or UTF-8 without a specification; Or using a tool to cut a file from your UTF-8.