blocks|key|997460|text|在awk中编写如下所示的函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|997461|$+awk+'func+isnum(x){return(x==x%2B0)}BEGIN{print+isnum("hello"),isnum("-42")}'
0+1|code-block|syntax|javascript|997462|你可以在你的脚本中加入这个函数，检查第三个字段是数字还是数字不是数字，然后去第四个字段，如果第四个字段不是数字，去第五个...till你会得到一个数字value.probably，在这里循环会有帮助，并把它加到和中。|997463|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

you write a function in awk like below:

<pre><code>$ awk 'func isnum(x){return(x==x+0)}BEGIN{print isnum("hello"),isnum("-42")}'
0 1
</code></pre>

you can incorporate in your script this function and check whether the third field is numeric or not.if not numeric then go for the 4th field and if the 4th field inturn is not numberic go for 5th ...till you reach a numeric value.probably a loop will help here, and add it to the sum.

blocks|key|1211867|text|使用GNU+awk和FPAT的单向方法|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1211868|awk+'BEGIN+{+FPAT+=+"([%5E,+]%2B)%7C(\"[%5E\"]%2B\")"+}+{+sum%2B=$3+}+END+{+print+sum+}'+file.txt|code-block|syntax|javascript|1211869|结果：|1211870|192|1211871|entityMap|0|LINK|mutability|MUTABLE|url|http://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html^0|2|7|A|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

One way using <code>GNU awk</code> and <a href="http://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html" rel="noreferrer">FPAT</a>

<pre><code>awk 'BEGIN { FPAT = "([^, ]+)|(\"[^\"]+\")" } { sum+=$3 } END { print sum }' file.txt
</code></pre>

Result:

<pre><code>192
</code></pre>

blocks|key|997749|text|我正在使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|997750|`FPAT="([%5E,]%2B)%7C(\"[%5E\"]%2B\")"+`|code-block|syntax|javascript|997751|使用gawk定义字段。我发现当字段为空时，它无法识别正确的字段数量。因为"%2B“在字段中至少需要一个字符。我将其更改为：|997752|`FPAT="([%5E,]*)%7C(\"[%5E\"]*\")"`|997753|并用"*"替换"%2B"。它工作正常。|offset|length|style|CODE|997754|我也发现GNU+Awk用户指南也有这个问题。https://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html|997755|entityMap|0|LINK|mutability|MUTABLE|url|https://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html^0|0|0|0|0|2|3|7|3|0|M|24|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|14|8|@$M|15|N|16|O|P]|$M|17|N|18|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@$M|1A|N|1B|1|1C]]|A|$]]|$1|S|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

I am using 

<pre><code>`FPAT="([^,]+)|(\"[^\"]+\")" `
</code></pre>

to define the fields with gawk. I found that when the field is null this doesn't recognize correct number of fields. Because "+" requires at least 1 character in the field. 
I changed it to:

<pre><code>`FPAT="([^,]*)|(\"[^\"]*\")"`
</code></pre>

and replace <code>"+"</code> with <code>"*"</code>. It works correctly. 

I also find that GNU Awk User Guide also has this problem.
<a href="https://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html" rel="noreferrer">https://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html</a>

blocks|key|997472|text|在perl中使用Text::CSV可能会更好，因为这是一个快速而健壮的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|997473|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You're probably better off doing it in perl with Text::CSV, since that's a fast and robust solution.

blocks|key|997605|text|对于如此简单的输入文件，您只需编写一个小函数，将引号之外的所有实际FS转换为其他值(我选择RS，因为记录分隔符不能是记录的一部分)，然后将其用作FS，例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|997606|$+cat+decsv.awk
BEGIN{+fs=FS;+FS=RS+}

{
+++decsv()

+++for+(i=1;i<=NF;i%2B%2B)+{
+++++++printf+"Record+%25d,+Field+%25d+is+<%25s>\n"+,NR,i,$i
+++}
+++print+""
}

function+decsv(+++++++++curr,head,tail)
{
+++tail+=+$0
+++while+(+match(tail,/"[%5E"]%2B"/)+)+{
+++++++head+=+substr(tail,+1,+RSTART-1);
+++++++gsub(fs,RS,head)
+++++++curr+=+curr+head+substr(tail,+RSTART,+RLENGTH)
+++++++tail+=+substr(tail,+RSTART+%2B+RLENGTH)
+++}
+++gsub(fs,RS,tail)
+++$0+=+curr+tail
}

$+cat+file
id,+name,+value
1,+foo,+17
2,+bar,+76
3,+"I+am+the,+question",+99

$+awk+-F",+"+-f+decsv.awk+file
Record+1,+Field+1+is+<id>
Record+1,+Field+2+is+<name>
Record+1,+Field+3+is+<value>

Record+2,+Field+1+is+<1>
Record+2,+Field+2+is+<foo>
Record+2,+Field+3+is+<17>

Record+3,+Field+1+is+<2>
Record+3,+Field+2+is+<bar>
Record+3,+Field+3+is+<76>

Record+4,+Field+1+is+<3>
Record+4,+Field+2+is+<"I+am+the,+question">
Record+4,+Field+3+is+<99>|code-block|syntax|javascript|997607|只有当你必须处理引号中嵌入的换行符和嵌入的转义引号时，它才会变得复杂，即使这样，它也不是太难，而且以前都已经做过了……|997608|有关详细信息，请参阅What's+the+most+robust+way+to+efficiently+parse+CSV+using+awk?。|offset|length|997609|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/45420535/whats-the-most-robust-way-to-efficiently-parse-csv-using-awk^0|0|0|0|A|1Q|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

For as simple an input file as that you can just write a small function to convert all of the real FSs outside of the quotes to some other value (I chose RS since the record separator cannot be part of the record) and then use that as the FS, e.g.:

<pre><code>$ cat decsv.awk
BEGIN{ fs=FS; FS=RS }

{
 decsv()

 for (i=1;i&lt;=NF;i++) {
 printf "Record %d, Field %d is &lt;%s&gt;\n" ,NR,i,$i
 }
 print ""
}

function decsv( curr,head,tail)
{
 tail = $0
 while ( match(tail,/"[^"]+"/) ) {
 head = substr(tail, 1, RSTART-1);
 gsub(fs,RS,head)
 curr = curr head substr(tail, RSTART, RLENGTH)
 tail = substr(tail, RSTART + RLENGTH)
 }
 gsub(fs,RS,tail)
 $0 = curr tail
}

$ cat file
id, name, value
1, foo, 17
2, bar, 76
3, "I am the, question", 99

$ awk -F", " -f decsv.awk file
Record 1, Field 1 is &lt;id&gt;
Record 1, Field 2 is &lt;name&gt;
Record 1, Field 3 is &lt;value&gt;

Record 2, Field 1 is &lt;1&gt;
Record 2, Field 2 is &lt;foo&gt;
Record 2, Field 3 is &lt;17&gt;

Record 3, Field 1 is &lt;2&gt;
Record 3, Field 2 is &lt;bar&gt;
Record 3, Field 3 is &lt;76&gt;

Record 4, Field 1 is &lt;3&gt;
Record 4, Field 2 is &lt;"I am the, question"&gt;
Record 4, Field 3 is &lt;99&gt;
</code></pre>

It only becomes complicated when you have to deal with embedded newlines and embedded escaped quotes within the quotes and even then it's not too hard and it's all been done before...

See <a href="https://stackoverflow.com/questions/45420535/whats-the-most-robust-way-to-efficiently-parse-csv-using-awk">What&#39;s the most robust way to efficiently parse CSV using awk?</a> for more information.

blocks|key|1211906|text|您可以帮助awk处理包含逗号(或换行符)的数据字段，方法是使用我编写的一个名为csvquote的小脚本。它用非打印字符替换带引号的字段中有问题的逗号。如果需要，您可以稍后恢复这些逗号-但在这种情况下，您不需要这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211907|下面是命令：|1211908|csvquote+inputfile.csv+%7C+awk+-F,+'{sum%2B=$3}+END+{print+sum}'|code-block|syntax|javascript|1211909|代码请参见https://github.com/dbro/csvquote|offset|length|1211910|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/dbro/csvquote^0|0|0|0|5|W|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

You can help awk work with data fields that contain commas (or newlines) by using a small script I wrote called csvquote. It replaces the offending commas inside quoted fields with nonprinting characters. If you need to, you can later restore those commas - but in this case, you don't need to.

Here is the command:

<pre><code>csvquote inputfile.csv | awk -F, '{sum+=$3} END {print sum}'
</code></pre>

see <a href="https://github.com/dbro/csvquote" rel="nofollow">https://github.com/dbro/csvquote</a> for the code

blocks|key|997542|text|本文确实帮助我解决了同样的数据字段问题。大多数CSV都会用空格或逗号将字段括起来。这会打乱awk的字段计数，除非您将它们过滤掉。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|997543|如果您需要那些包含垃圾的字段中的数据，那么这不适合您。ghostdog74提供了答案，它清空该字段，但最终保持总字段计数，这是保持数据输出一致性的关键。我不喜欢这个解决方案引入新行的方式。这是我使用的这个解决方案的版本。前三个字段在数据中从未出现过这个问题。包含客户名称的第四个字段通常是这样做的，但我需要该数据。显示问题的其余字段我可以毫不费力地丢弃，因为我的报告输出中不需要它。因此，我首先非常明确地删除了第四个字段的垃圾，并删除了引号的前两个实例。然后，我应用ghostdog74提供的内容清空其余包含逗号的字段-这也删除了引号，但我使用printf来维护单个记录中的数据。我开始时有85个字段，最后在所有情况下都有85个字段，这些字段都来自我的8000%2B行杂乱的数据。一个完美的分数！|offset|length|style|CODE|997544|grep+-i+$1+$dbfile+%7C+sed+'s/\,+Inc.//;s/,+LLC.//;s/,+LLC//;s/,+Ltd.//;s/\"//;s/\"//'+%7C+awk+-F'"'+'{+for(i=1;i<=NF;i%2B=2)+printf+($i);printf+("\n")}'+>+$tmpfile|code-block|syntax|javascript|997545|当然，清空包含逗号的字段但也保留记录的解决方案是：|997546|awk+-F'"'+'{+for(i=1;i<=NF;i%2B=2)+printf+($i);printf+("\n")}|997547|万分感谢ghostdog74的伟大解决方案！|997548|NetsGuy256/|997549|entityMap^0|0|R|A|6H|A|7K|6|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]|$D|10|E|11|F|G]|$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|16|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|17|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|V|$]]

This article did help me solve this same data field issue. Most CSV will put a quote around fields with spaces or commas within them. This messes up the field count for awk unless you filter them out. 

If you need the data within those fields that contain the garbage, this is not for you. <code>ghostdog74</code> provided the answer, which empties that field but maintains the total field count in the end, which is key to keeping the data output consistent. I did not like how this solution introduced new lines. This is the version of this solution I used. The fist three fields never had this problem in the data. The fourth field containing customer name often did, but I needed that data. The remaining fields that exhibit the problem I could throw away without issue because it was not needed in my report output. So I first sed out the 4th field's garbage very specifically and remove the first two instances of quotes. Then I apply what <code>ghostdog74</code>gave to empty the remaining fields that have commas within them - this also removes the quotes, but I use <code>printf</code>to maintain the data in a single record. I start off with 85 fields and end up with 85 fields in all cases from my 8000+ lines of messy data. A perfect score!

<pre><code>grep -i $1 $dbfile | sed 's/\, Inc.//;s/, LLC.//;s/, LLC//;s/, Ltd.//;s/\"//;s/\"//' | awk -F'"' '{ for(i=1;i&lt;=NF;i+=2) printf ($i);printf ("\n")}' &gt; $tmpfile
</code></pre>

The solution that empties the fields with commas within them but also maintains the record, of course is:

<pre><code>awk -F'"' '{ for(i=1;i&lt;=NF;i+=2) printf ($i);printf ("\n")}
</code></pre>

Megs of thanks to ghostdog74 for the great solution!

NetsGuy256/

blocks|key|1211975|text|FPAT是一个优雅的解决方案，因为它可以处理可怕的引号内逗号问题，但是对于最后一列中的一列数字求和，而不考虑前面的分隔符的数量，$NF工作得很好：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211976|awk+-F","+'{sum%2B=$NF}+END+{print+sum}'|offset|length|style|CODE|1211977|要访问倒数第二列，您可以使用以下命令：|1211978|awk+-F","+'{sum%2B=$(NF-1)}+END+{print+sum}'|1211979|entityMap^0|0|0|12|0|0|0|16|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

FPAT is the elegant solution because it can handle the dreaded commas within quotes problem, but to sum a column of numbers in the last column regardless of the number of preceding separators, $NF works well:

<code>awk -F"," '{sum+=$NF} END {print sum}'</code>

To access the second to last column, you would use this:

<code>awk -F"," '{sum+=$(NF-1)} END {print sum}'</code>

blocks|key|1211766|text|如果您确定'value‘列始终是最后一列：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1211767|awk+-F,+'{sum%2B=$NF}+END+{print+sum}'|code-block|syntax|javascript|1211768|NF表示字段的数量，因此$NF是最后一列|1211769|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you know for sure that the 'value' column is always the last column:

<pre><code>awk -F, '{sum+=$NF} END {print sum}'
</code></pre>

NF represents the number of fields, so $NF is the last column

blocks|key|1212074|text|完全成熟的CSV解析器，比如Perl的Text::CSV_XS，就是专门为处理这种奇怪的事情而构建的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1212075|perl+-MText::CSV_XS+-lne+'BEGIN{$csv=Text::CSV_XS->new({allow_whitespace+=>+1})}+if($csv->parse($_)){@f=$csv->fields();$sum%2B=$f[2]}+END{print+$sum}'+file|1212076|由于输入数据的逗号分隔符周围有空格，因此需要使用allow_whitespace。非常旧的Text::CSV_XS版本可能不支持此选项。|1212077|我在这里的回答中提供了关于Text::CSV_XS的更多解释：parse+csv+file+using+gawk|1212078|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/314384/parse-csv-file-using-gawk/33380831#33380831^0|J|C|0|0|49|0|O|G|19|C|0|D|C|V|P|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@$9|17|A|18|1|19]]|E|$]]|$1|L|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]]]

Fully fledged CSV parsers such as Perl's <code>Text::CSV_XS</code> are purpose-built to handle that kind of weirdness. 

<code>perl -MText::CSV_XS -lne 'BEGIN{$csv=Text::CSV_XS-&gt;new({allow_whitespace =&gt; 1})} if($csv-&gt;parse($_)){@f=$csv-&gt;fields();$sum+=$f[2]} END{print $sum}' file</code>

<code>allow_whitespace</code> is needed since the input data has whitespace surrounding the comma separators. Very old versions of <code>Text::CSV_XS</code> may not support this option.

I provided more explanation of <code>Text::CSV_XS</code> within my answer here: <a href="https://stackoverflow.com/questions/314384/parse-csv-file-using-gawk/33380831#33380831">parse csv file using gawk</a>

blocks|key|1212124|text|您可以尝试通过perl正则表达式通过管道传输该文件，以将引用的，转换为其他类型，如%7C。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1212125|cat+test.csv+%7C+perl+-p+-e+"s/(\".%2B?)(,)(.%2B?\")/\1\%7C\3/g"+%7C+awk+-F,+'{...|code-block|syntax|javascript|1212126|上面的正则表达式假设双引号中总是有一个逗号。因此，需要做更多的工作才能使逗号成为可选的|1212127|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

you could try piping the file through a perl regex to convert the quoted , into something else like a |.
<pre><code>cat test.csv | perl -p -e &quot;s/(\&quot;.+?)(,)(.+?\&quot;)/\1\|\3/g&quot; | awk -F, '{...
</code></pre>
The above regex assumes there is always a comma within the double quotes. so more work would be needed to make the comma optional

I am using awk to perform counting the sum of one column in the csv file. The data format is something like:

<pre><code>id, name, value
1, foo, 17
2, bar, 76
3, "I am the, question", 99
</code></pre>

I was using this awk script to count the sum:

<pre><code>awk -F, '{sum+=$3} END {print sum}'
</code></pre>

Some of the value in name field contains comma and this break my awk script. 
My question is: can awk solve this problem? If yes, and how can I do that?

Thank you.

Can awk deal with CSV file that contains comma inside a quoted field?

Perl

我使用awk对csv文件中一列的总和进行计数。数据格式类似于：id, name, value1, foo, 172, bar, 763, "I am the, question", 99我使用这个awk脚本来计算总和：awk -F, '{sum+=$3} END {print sum}'name字段中的一些值包含逗号，这会中断我的awk脚本。我的问题是: awk能解决这个问题吗？如果是，我该怎么做

问awk能处理带引号的CSV文件吗？
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问awk能处理带引号的CSV文件吗？EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问awk能处理带引号的CSV文件吗？
EN