Reading from a csv file and extracting certain data columns based on first column value

Question

This is my first batch program and I have been searching online but still struggling to write up 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'm trying to write a simple program to do the following:

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

and pipe 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 fine in Excel and Notepad but when I execute the script to display the first variable, it looks like there's some garbage characters just before the "RH" on the first record - I cannot bypass 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

This is what I have so far but the "if" condition is not working fo me. — user2550880
– user2550880, Commented Jul 4, 2013 at 15:29
Do you mean IF "%_var1%"=="RH" echo %_var2% ? I don't see a var2 (i.e. without a preceding '_'). — Rapnar
– Rapnar, Commented Nov 27, 2014 at 19:19

Magoo · Accepted Answer · 2013-07-04 19:57:39Z

10

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

Should work - no need to assign all the 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 are assigning %%x to _varx then if %%x is quoted, the quotes will be INCLUDED in the value assigned. To remove the enclosing quotes (if they exist) use SET _varx=%%~x.

Addendum 20130703-1956Z for OP's problem

@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

edited Jul 4, 2013 at 19:57

answered Jul 4, 2013 at 15:58

Magoo

80.8k8 gold badges68 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2550880 Over a year ago

Thank you - I chose your first sample code as it was the simplest one. First record has garbage characters in _var1, how to get rid of it because it's not printing var2

Magoo Over a year ago

Without actually seeing the record in question and without any notion of what the garbage characters are, I'm at a loss. If it's a header record, then including skip=1 in the FOR/f directives should ignore that line. Note also that since you've not executed a setlocal you may be dealing with stale values from a prior run - IF you are using _var1 - but I'm confused - if you're using the first example, which avoids the setting of _varx in any case, why should _var1's value be significant? Or are you referring to the value in the first element of the first line in your datafile?

user2550880 Over a year ago

I have added some sample data that shows some unwanted characters on the first variable record. As a result my logic is not working where I want to extract the second variable if the first variable = "RH". Thank you.

Endoro · Accepted Answer · 2013-07-04 15:48:40Z

4

You have a parsing issue. First end the for loop with ), after this 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%

answered Jul 4, 2013 at 15:48

Endoro

37.7k8 gold badges54 silver badges64 bronze badges

Comments

Ansgar Wiechers · Accepted Answer · 2013-07-04 15:40:58Z

3

You need to enable delayed expansion:

@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!
)

answered Jul 4, 2013 at 15:40

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Comments

satibel · Accepted Answer · 2016-10-12 11:49:40Z

as there was no answer to the "why does my line starts with ∩╗┐"RH"", I'll do some gravedigging.

So, the ∩╗┐ comes from the BOM (Byte Order Mark) which indicates the file is in UTF, and the way the bytes are written 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 the var, it is unchanged, RH is not in the var) which means, whether the Bom is here or not is not important. Though, you'll have problems if you want an exact match.

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

Collectives™ on Stack Overflow

Reading from a csv file and extracting certain data columns based on first column value

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related