bash 如何使用awk在列周围添加单引号

How to add single quotes around columns using awk

提问by Deano

Just wondering how can I add single quotes around fields, so I can import it to mysql without warnings or errors.

只是想知道如何在字段周围添加单引号，以便我可以将它导入到 mysql 中而不会出现警告或错误。

I have a csv file with lots of content.

我有一个包含大量内容的 csv 文件。

16:47:11,3,r-4-VM,250000000.,0.50822578824,131072,0,0,0,0,0

Desired output

期望输出

'16:47:07','3','r-4-VM','230000000.','0.466028518635','131072','0','0','0','60','0'

I really have no idea where to start, your help is highly appreciated.

我真的不知道从哪里开始，非常感谢您的帮助。

回答by Alper

You could try this

你可以试试这个

awk -F"," -v quote="'" -v OFS="','" '= {print quote 
sed -r 's/[^,]+/'\''&'\''/g' file
 quote}' file

1. Replace each separator (, comma) with (',' quote-comma-quote) -> (-F"," -v OFS="','")
2. Add quotes to the begin and end of line -> (print quote $0 quote)

1. 将每个分隔符 (, 逗号) 替换为 (',' quote-comma-quote) -> (-F"," -v OFS="','")
2. 在行首和行尾添加引号 -> (print quote $0 quote)

回答by potong

This might work for you (GNU sed):

这可能对你有用（GNU sed）：

sed -r "s/[^,]+/'&'/g" file

or:

或者：

#!/usr/bin/awk -f

BEGIN { FS=OFS=","}

{
    for (i = 1; i <= NF; ++i)
        $i = "'" $i "'"
    print
}

回答by steveha

awk '{gsub(/^|$/,"\x027");gsub(/,/,"\x027,\x027")}7' file

At the beginning, set FS(the field separator) to a comma; also set OFS, the output field separator, to a comma.

开始时，将FS（字段分隔符）设置为逗号；还将OFS输出字段分隔符设置为逗号。

For every input line, loop over all fields. NFis the number of fields parsed out of the current line. Set each field to its own value surrounded by single quotes.

对于每个输入行，循环遍历所有字段。 NF是从当前行解析出的字段数。将每个字段设置为由单引号括起来的自己的值。

When done updating the fields, print the modified line.

完成更新字段后，打印修改后的行。

回答by Kent

try this:

尝试这个：

kent$  echo "16:47:11,3,r-4-VM,250000000.,0.50822578824,131072,0,0,0,0,0"|awk '{gsub(/^|$/,"\x027");gsub(/,/,"\x027,\x027")}7'
'16:47:11','3','r-4-VM','250000000.','0.50822578824','131072','0','0','0','0','0'

example

例子

QUOTE_CSV_PY="import sys; import csv; csv.writer(sys.stdout, quoting=csv.QUOTE_ALL, quotechar=\"'\").writerows(csv.reader(sys.stdin))"
python -c "$QUOTE_CSV_PY" < file

回答by Heath Raftery

awkand sedare not going to (easily) determine whether the field separator (,) is escaped or not. The csv file format escapes ,characters within fields by surrounding the whole field in double quotes (see Section 2.6 of RFC4180).

awk并且sed不会（轻松）确定字段分隔符 ( ,) 是否被转义。csv 文件格式,通过用双引号将整个字段括起来来对字段内的字符进行转义（请参阅RFC4180 的第 2.6节）。

As I describe in this answer, a more robust method is to use a csv library, rather than parsing as text using regular expressions and the like.

正如我在这个答案中所描述的，更强大的方法是使用 csv 库，而不是使用正则表达式等解析为文本。

I found Python's library was the best choice because it's:

我发现 Python 的库是最好的选择，因为它是：

1. widely available without onerous dependencies, with the exception of Python itself;
2. not particular sensitive to the version of Python you use;
3. lends itself to being embedded within a shell script; and
4. is quite compact (a one-liner will do!).

1. 除了 Python 本身之外，广泛可用，没有繁重的依赖；
2. 对您使用的 Python 版本不是特别敏感；
3. 适合嵌入在 shell 脚本中；和
4. 非常紧凑（单线就行！）。

Based on the question's tags, I suspect these criteria will be appealing to you too.

根据问题的标签，我怀疑这些标准也会对您有吸引力。

Thus, try the following:

因此，请尝试以下操作：

awk 'BEGIN{FS=OFS=","}{for (i=1;i<=NF;++i)  $i="~"$i"~"}{print}' $input_csv_file

To break it down:

分解：

• QUOTE_CSV_PYis a shell variable containing the Python one-liner commands
• The Python commands simply:
  • import the standard sys and csv modules;
  • create a csv writer that writes to standard output (stdout) with QUOTE_ALLset so all fields get quoted using quotechar, which is set to a single quote;
  • feed the csv writer a csv reader that reads from standard input (stdin).
• The second line simply passes the one-liner to the python interpreter, and feeds the csv file (called file) into its stdin.

• QUOTE_CSV_PY是包含 Python 单行命令的 shell 变量
• Python 命令很简单：
  • 导入标准的 sys 和 csv 模块；
  • 创建一个stdout使用QUOTE_ALLset写入标准输出 ( )的 csv 编写器，以便所有字段都使用 引用quotechar，它被设置为单引号；
  • 为 csv 编写器提供一个从标准输入 ( stdin)读取的 csv 阅读器。
• 第二行只是将单行代码传递给 python 解释器，并将 csv 文件（称为file）输入到它的stdin.

回答by Sara Farheen

##代码##

This works. Here I am enclosing all the csv file columns with a ~.

这有效。在这里，我用 .csv 文件将所有 csv 文件列括起来~。