从csv文件读取并基于第一列值提取某些数据列 [英] Reading from a csv file and extracting certain data columns based on first column value

问题描述

这是我的第一个批处理程序，我一直在网上搜索，但仍然努力写一个解决方案。

我有以下CSV文件：

 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，
 ...... 
  

我试图写一个简单的程序来执行以下操作：

• 如果column1 =RH，则提取column2值/ 06> 15 02:14:58 -0400）


• 如果column1 =SH，则提取column4值（LQ3SUEEWPWKL6）

和管道输出到文件。

---

这是我的代码， if条件不适用于我

  @echo off 
 ::在变量
中设置输入文件:: Set _InputFile =％1 
 
 ::将输入行存储到不同的变量
 FOR / Ftokens = 1-18 * delims =，%% A IN（％_InputFile％） DO（
设置_var1 = %% A 
设置_var2 = %% B 
设置_var3 = %% C 
设置_var4 = %% D 
设置_var5 = ％E 
设置_var6 = %% F 
设置_var7 = %% G 
设置_var8 = %% H 
设置_var9 = %% I 
设置_var10 = ％J 
设置_var11 = %% K 
设置_var12 = %% L 
设置_var13 = %% M 
设置_var14 = %% N 
设置_var15 = ％O 
设置_var16 = %% P 
设置_var17 = %% Q 
设置_var18 = %% R 
 
 
如果％_var1％ ==RHecho％var2％
 
）
  

---

我的CSV文件在Excel和记事本中看起来不错，但是当我执行脚本显示第一个变量，它看起来像在第一个记录RH之前有一些垃圾字符 - 我不能绕过因为我需要提取额外的列数据，如果var1 =RH：

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

< div=h2_lin>解决方案

 （
 FOR / Ftokens = 1-18 * delims =，%% A IN _InputFile％）DO（
如果%%〜A==RHecho %%〜B 
如果%%〜A==SHecho %%〜D 
）
）> youroutputfilename 
  

应该工作 - 无需将所有值不同的变量 - 但如果您计划使用它们，则

  FOR / Ftokens = 1-18 * delims = %% A IN（％_InputFile％）DO（
 ... 
设置_var17 = %% Q 
设置_var18 = %% R 
 CALL：PROCESS 
） 
 ... 
 GOTO：EOF 
 
：PROCESS 
 IF％_var1％==RHecho％_var2％
 IF％_var1％= =SHecho％_var4％
 GOTO：EOF 
  

请注意，因为您将 %% x 分配给 _varx ，则如果 %% x 被引用，引号将被分配的值INCLUDED。要删除包含的引号（如果存在），请使用 SET _varx = %%〜x 。

---

OP的问题补遗20130703-1956Z

  @ECHO OFF 
 SETLOCAL 
 SET _Inputfile = u：\\\
oname1.txt 
（
 FOR / Ftokens = 1-18 * delims =，%% A IN（％_InputFile％）DO（
 SET RH =
 SETSH =
 ECHO（%% A | FINDSTR / l / c：\RH\> NUL 
如果不是错误1 SET RH = Y 
 ECHO（%% A | FINDSTR / l / c：\SH \> NUL 
如果没有错误1 SET SH = Y 
如果DEFINED RH echo %%〜B 
 if DEFINED SH echo %%〜D 
）
）> u：\youroutputfilename 
 TYPE u：\youroutputfilename 
 del u ：\youroutputfilename 
 echo ========第一种方式
 
（
 FOR / Ftokens = 1-18 * delims =，%% A IN （％_InputFile％）DO（
 SET _var1 = %% A 
 SETRH =
 SETSH =
 CALL：process 
如果DEFINED RH echo %%〜B 
 if DEFINED SH echo %%〜D 
）
）> u：\youroutputfilename 
 
 TYPE u：\youroutputfilename 
 del u：\youroutputfilename 
 echo ========第二种方式
 
 SETLOCAL ENABLEDELAYEDEXPANSION 
（
 FOR / Ftokens = 1-18 * delims =，%% A IN（％_InputFile％）DO（
 SET _var1 = %% A 
 IF！_var1：〜-4！==RHecho %% 〜B 
 IF！_var1：〜-4！==SHecho %%〜D 
）
）> u：\youroutputfilename 
 
 TYPE u：\youroutputfilename 
 del u：\youroutputfilename 
 echo ========第三路
 ENDLOCAL 
 
 GOTO：EOF 
 
：process 
如果％_var1：〜-4％==RHSET RH = Y 
如果％_var1：〜-4％ =SHSET SH = Y 
 GOTO：EOF 
  

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

解决方案

(
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

这篇关于从csv文件读取并基于第一列值提取某些数据列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！