I have a .CSV file (lets say named as file.csv) with numeric and string values. The string might contain commas hence they are enclosed in double quotes as in the below format.
我有一个.CSV文件(简称为file.csv),带有数值和字符串值。该字符串可能包含逗号,因此它们用双引号括起来,如下面的格式所示。
column1,column2,column3,column4,column5,column6,column7
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455
11,22,"simple, string",77,777,333,22
When I am trying to add empty columns at the end of the file, using the below code
当我尝试在文件末尾添加空列时,使用下面的代码
awk -F, '{NF=13}1' OFS="," file.csv > temp_file.csv
the output is not as per my requirement. The code is also counting the commas in the text qualifier fields too whihc are enclosed in double quotes. The output of the file cat temp_file.csv using the above command is as below:
输出不符合我的要求。代码也在计算文本限定符字段中的逗号,并且用双引号括起来。使用上面的命令输出文件cat temp_file.csv如下:
column1,column2,column3,column4,column5,column6,column7,,,,,,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88
11,22,"simple, string",77,777,333,22,,,,,
Where as I need the total number of fields in the field to be 13. Any input on this issue using either awk or sed is greatly appreciated.
我在哪里需要字段中的字段总数为13.非常感谢使用awk或sed对此问题的任何输入。
2 个解决方案
#1
0
awk -F, '{sub(/ *$/,"");$0=$0 ","}1' OFS=,
column1,column2,column3,column4,column5,column6,column7,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455,
11,22,"simple, string",77,777,333,22,
This removes trailing space and add one filed at the end.
这将删除尾随空格并在末尾添加一个字段。
#2
-1
If your input always has 7 fields as posted then take your pick:
如果您的输入总是有7个已发布的字段,那么请选择:
awk '{print $0 ",,,,,,"}' file
sed 's/$/,,,,,,/' file
or to remove trailing spaces:
或删除尾随空格:
awk '{sub(/ *$/,",,,,,,")}1' file
sed 's/ *$/,,,,,,/' file
If your input file can have different numbers of fields present but still has the header line as you show:
如果输入文件可以包含不同数量的字段,但仍显示标题行:
awk -F, 'NR==1{flds=sprintf("%*s",13-NF,""); gsub(/ /,FS,flds)} {sub(/ *$/,flds)} 1' file
column1,column2,column3,column4,column5,column6,column7,,,,,,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,,,,,,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455,,,,,,
11,22,"simple, string",77,777,333,22,,,,,,
#1
0
awk -F, '{sub(/ *$/,"");$0=$0 ","}1' OFS=,
column1,column2,column3,column4,column5,column6,column7,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455,
11,22,"simple, string",77,777,333,22,
This removes trailing space and add one filed at the end.
这将删除尾随空格并在末尾添加一个字段。
#2
-1
If your input always has 7 fields as posted then take your pick:
如果您的输入总是有7个已发布的字段,那么请选择:
awk '{print $0 ",,,,,,"}' file
sed 's/$/,,,,,,/' file
or to remove trailing spaces:
或删除尾随空格:
awk '{sub(/ *$/,",,,,,,")}1' file
sed 's/ *$/,,,,,,/' file
If your input file can have different numbers of fields present but still has the header line as you show:
如果输入文件可以包含不同数量的字段,但仍显示标题行:
awk -F, 'NR==1{flds=sprintf("%*s",13-NF,""); gsub(/ /,FS,flds)} {sub(/ *$/,flds)} 1' file
column1,column2,column3,column4,column5,column6,column7,,,,,,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,,,,,,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455,,,,,,
11,22,"simple, string",77,777,333,22,,,,,,