linux - 如何将值中包含逗号的制表符分隔文件转换为 .CSV 并将包含逗号的值括在双引号中？

Question

我有一个 .CSV 文件(比方说 tab_delimited_file.csv)，我是从特定供应商的门户网站下载的。当我将文件移动到我的 Linux 目录之一时，我注意到这个特定的 .CSV 文件实际上是一个 tab delimited 文件，被命名为 .CSV 。请在下面找到文件的几个样本记录。

"""column1"""   """column2"""   """column3"""   """column4"""   """column5"""   """column6"""   """column7"""  
12  455 string with quotes, and with a comma in between 4432    6787    890 88  
4432    6787    another, string with quotes, and with two comma in between  890 88  12  455  
11  22  simple string   77  777 333 22

以上示例记录以tabs分隔.我知道文件的标题很奇怪，但这是我收到文件格式的方式。

我尝试使用 tr 命令替换 tabs与 commas但是由于记录值中的额外逗号，文件被完全搞砸了。我需要将其中带有逗号的记录值用双引号引起来。我使用的命令如下。

tr '\t' ',' < tab_delimited_file.csv > comma_separated_file.csv    

这会将文件转换为以下格式。

"""column1""","""column2""","""column3""","""column4""","""column5""","""column6""","""column7"""
12,455,string with quotes, and with a comma in between,4432,6787,890,88
4432,6787,another, string with quotes, and with two comma in between,890,88,12,455
11,22,simple string,77,777,333,22

我需要帮助将示例文件转换为以下格式。

column1,column2,column3,column4,column5,column6,column7
12,455,"string with quotes, and with a comma in between",4432,6787,890,88
4432,6787,"another, string with quotes, and with two comma in between",890,88,12,455
11,22,"simple string",77,777,333,22

使用 sed 的任何解决方案 或 awk 将非常有用。

Answer 1

这将产生您要求的输出，但不清楚我假设的标准是否正确，例如，哪些字段要放在引号中(任何包含逗号或空格的字段)实际上是什么想要自己用其他输入进行测试以查看:

$ awk 'BEGIN { FS=OFS="\t" }
  {
     gsub(/"/,"")
     for (i=1;i<=NF;i++)
         if ($i ~ /[,[:space:]]/)
             $i = "\"" $i "\""
     gsub(OFS,",")
     print
  }
  ' file
column1,column2,column3,column4,column5,column6,column7
12,455,"string with quotes, and with a comma in between",4432,6787,890,88
4432,6787,"another, string with quotes, and with two comma in between",890,88,12,455
11,22,"simple string",77,777,333,22