windows 从 csv 文件中读取并根据第一列值提取某些数据列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17473239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reading from a csv file and extracting certain data columns based on first column value
提问by user2550880
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:
我有以下 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",
......
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)
- 如果 column1 = "RH"，则提取 column2 值 (2013/06/15 02:14:58 -0400)
- 如果 column1 = "SH"，则提取 column4 值 (LQ3SUEEWPWKL6)
and pipe output to a file.
和管道输出到一个文件。
This is my code so far but the if condition is not working for me
到目前为止，这是我的代码，但 if 条件对我不起作用
@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":
我的 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
采纳答案by Magoo
(
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 %%xto _varxthen if %%xis quoted, the quotes will be INCLUDED in the value assigned. To remove the enclosing quotes (if they exist) use SET _varx=%%~x.
请注意，使用此方法，由于您要分配%%x给_varxthen if%%x被引用，引号将包含在分配的值中。要删除封闭引号（如果存在），请使用SET _varx=%%~x.
Addendum 20130703-1956Z for OP's problem
OP 问题的附录 20130703-1956Z
@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
回答by Endoro
You have a parsing issue. First end the for loop with ), afterthis you can use the new variables:
你有一个解析问题。首先用 结束 for 循环)，然后您可以使用新变量：
@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%
回答by Ansgar Wiechers
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!
)回答by satibel
as there was no answer to the "why does my line starts with "RH"", I'll do some gravedigging.
由于“为什么我的行以∩╗┐“RH”开头”没有答案，所以我会挖一些坟墓。
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
因此，∩╗┐ 来自 BOM（字节顺序标记），它表明文件是 UTF 格式，以及必要时写入字节的方式。答案：你可以使用
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.
这将检查 RH 是否在 %_var1% 中（如果在 var 中替换 RH 后，它没有改变，RH 不在 var 中）这意味着，Bom 是否在这里并不重要。但是，如果您想要完全匹配，则会遇到问题。
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.
另一种处理方法是不在文件中包含 bom，这意味着以 ASCII 或 UTF-8 格式保存而没有 BOM；或者使用工具从 UTF-8 文件中去除 bom。
