在Window Batch中-如何解析CSV文件，其中的字段包括逗号和双引号 [英] In Window Batch - how do I parse CSV file where fields include Comma and double quote

问题描述

我有一个输入CSV文件ttt.csv，该文件以逗号分隔，每个字段都可以包含双引号和逗号:

I have an input CSV file, ttt.csv, which is comma delimited, each field may include double quote and comma:

这是ttt.csv的内容:

Here is the contents of ttt.csv:

"CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA,[e-mail]

"CN=Boo\\,Ryan,OU=Users,OU=Headquarters,DC=CORP",Boo,Ryan,"Boo,Ryan",BABBBB,[e-mail]

我需要循环此文件，对于每一行，我需要获取6个值中的每个值，并向数据库创建SQL插入语句.

I would need to loop this file, for each line, I would need to get each of the 6 values and create my SQL insert statement to database.

对于2号线，我需要获得:

In my case for Line 2 I would need to get:

Value1=       CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP
Value2=       Boo
Value3=       Ryan
Value4=       Boo,Ryan
Value5=       BABBBB
Value6=       [e-mail]

我使用了包含双引号的定界符，但似乎不起作用:

I used delimiter which includes double quotes and it does not seems working:

set str2="CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BAR‌​AAA,[e-mail]
echo %str2%
for /f "tokens=1 delims=(,")" %%a in ("!str2!") do ( set newstr2=%%a )
echo !newstr2!

推荐答案

正如我上面评论的，只需使用普通的for循环-否/f，否/r，否/d，否，只是一个简单的for循环.它将处理CSV分隔符，同时将引用的内容视为单个令牌.

As I commented above, just use a plain for loop -- no /f, no /r, no /d, no /l, just a plain, simple for loop. It'll handle CSV delimiters while treating quoted stuff as a single token.

@echo off
setlocal enabledelayedexpansion

set str2="CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA,[e-mail]
echo %str2%

set idx=0

for %%a in (%str2%) do (
    set "newstr[!idx!]=%%~a"
    set /a idx += 1
)

set newstr

输出:

C:\ Users \ me \ Desktop> test.bat "CN = Bar \\，Alex，OU =用户，OU =总部，DC = CORP"，Bar，Alex，"Barziza，Alex"，BARAAA， [e-mail]
newstr [0] = CN = Bar \\，Alex，OU =用户，OU =总部，DC = CORP
newstr [1] = Bar
newstr [2] = Alex
newstr [3] = Barziza，Alex
newstr [4] = BARAAA
newstr [5] =[e-mail]

C:\Users\me\Desktop>test.bat "CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP",Bar,Alex,"Barziza,Alex",BARAAA, [e-mail]
newstr[0]=CN=Bar\\,Alex,OU=Users,OU=Headquarters,DC=CORP
newstr[1]=Bar
newstr[2]=Alex
newstr[3]=Barziza,Alex
newstr[4]=BARAAA
newstr[5]=[e-mail]

---

如果您的csv数据包含不应被视为标记定界符的未加引号的空格，则可以在分割之前将空格临时转换为下划线，然后像这样转换回去:

---

If your csv data contains unquoted spaces that should not be treated as token delimiters, you can temporarily convert spaces to underscores before splitting, then convert back like this:

@echo off
setlocal enabledelayedexpansion

set str2="CN=Ryan\\,David Paul,OU=Users,OU=Singapore,DC=GLOBAL,DC=CORP",Ryan,David Paul,"Ryan, David Paul",RPAUL123,[e-mail]
echo %str2%

set idx=0

for %%a in (%str2: =_%) do (
    set "str=%%~a"
    set "newstr[!idx!]=!str:_= !"
    set /a idx += 1
)

set newstr

如果愿意，您可以详细了解子字符串替换.输出:

You can read more on substring substitution if you wish. Output:

C:\ Users \ me \ Desktop> test.bat
"CN = Ryan \\，David Paul，OU =用户，OU =新加坡，DC = GLOBAL，DC = CORP"，Ryan，David Paul，"Ryan，David Paul"，RPAUL123，David @ aaad.com
newstr [0] = CN = Ryan \\，David Paul，OU =用户，OU =新加坡，DC = GLOBAL，DC = CORP
newstr [1] = Ryan
newstr [2] = David Paul
newstr [3] = Ryan，大卫·保罗
newstr [4] = RPAUL123
newstr [5] =[e-mail]

C:\Users\me\Desktop>test.bat
"CN=Ryan\\,David Paul,OU=Users,OU=Singapore,DC=GLOBAL,DC=CORP",Ryan,David Paul,"Ryan, David Paul",RPAUL123,[e-mail]
newstr[0]=CN=Ryan\\,David Paul,OU=Users,OU=Singapore,DC=GLOBAL,DC=CORP
newstr[1]=Ryan
newstr[2]=David Paul
newstr[3]=Ryan, David Paul
newstr[4]=RPAUL123
newstr[5]=[e-mail]

当然，如果您的数据已经包含下划线，请使用不包含下划线的字符-反引号，波浪号，美元符号或其他.

Of course, if your data already contains underscores, then use a character it doesn't contain -- a backtick, a tilde, a dollar sign, or something else.

这篇关于在Window Batch中-如何解析CSV文件，其中的字段包括逗号和双引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！