mjhjmtu

I'm doing my course completion work where I need to manipulate some columns of a .csv file that has six hundred thousand lines. I have already searched in several forums on sed and awk and I did not find anything like it (and unfortunately my knowledge about Linux is not very deep). What I found generally handles only one column or all ... what I need is to put double quotes only in the first, fifth, and sixth columns.

For example:

2018-03-18 4:56:17,255.255.255.255,00,ssh,admin,123

To be

"2018-03-18 4:56:17",255.255.255.255,00,ssh,"admin","123"

Still, maybe I still change some column since I'm just starting in data mining and I do not know what the configuration will be like in a few weeks, so if you can do a brief syntax of logic, I'll be eternally grateful.

Using csvtool which has a helpful format command:

csvtool format '"%1",%2,%3,%4,"%5","%6"n' file.csv 

Example:

echo "2018-03-18 4:56:17,255.255.255.255,00,ssh,admin,123" |
csvtool format '"%1",%2,%3,%4,"%5","%6"n' -

Output:

"2018-03-18 4:56:17",255.255.255.255,00,ssh,"admin","123"

csvtool also can call shell functions and external programs to parse lines from .CSV files. To do the same thing using printf, but print the "123" in hexadecimal, do:

echo "2018-03-18 4:56:17,255.255.255.255,00,ssh,admin,123" | 
csvtool call "printf '"%s",%s,%s,%s,"%s","%x"n'" -

Output:

"2018-03-18 4:56:17",255.255.255.255,00,ssh,"admin","7b"

I might approach it with awk this way:

1. set the output field separator to what the input field separator is, which we assign as the comma -F,
   


2. for each line, reassign the values of fields 1, 5, and 6 as being the original values but surrounded by double-quotes. The apparent quoting mess is because I used double-quotes to create the surrounding string, and because the only string I want to print is a double-quote, I have to escape it, so each double-quote that I want ends up being """.


3. Once the fields have been updated, print the newly-combined string.

The script is:

awk -F, 'BEGIN OFS=FS $1="""$1"""; $5="""$5"""; $6="""$6"""; print ' < input.csv > output.csv

As you find that you need to quote more fields, simply do the same thing as with fields 1, 5, and 6 above.

With perl

$ perl -F, -lane 'map $_=qq("$_") @F[0,4,5]; print join ",", @F' ip.txt
"2018-03-18 4:56:17",255.255.255.255,00,ssh,"admin","123"

• 
  -F, use , as input field delimiter, results available in @F array


• 
  map $_=qq("$_") @F[0,4,5] double quote required array elements. Index starts from 0. The qq operator is used here to avoid escaping double quotes, qq("$_") is same as ""$_""
  


• 
  print join ",", @F print the modified array with , as separator

Another way to do with awk

$ awk -v q='"' 'BEGINsplit("1 5 6",a); FS=OFS=","
 for(i in a) $a[i]=q $a[i] q 1' ip.txt
"2018-03-18 4:56:17",255.255.255.255,00,ssh,"admin","123"

• 
  -v q='"' save double quote as value in q variable


• 
  split("1 5 6",a) save index to be changed as values in a array (default split on whitespace, FS is not yet changed)


• 
  FS=OFS="," change input/output delimiter to ,
  


• 
  for(i in a) $a[i]=q $a[i] q change required fields


• 
  1 idiomatic way to print contents of $0
  

If you really want to use sed (which I don't recommend if awk or perl are available), then assuming that the sixth column is terminated by the line ending rather than a comma AND that fields do not themselves contain embedded (quoted) commas:

sed -E -e 's/([^,]*),/"1",/1' -e 's/([^,]*),/"1",/5' -e 's/([^,]*)$/"1"/' file
"2018-03-18 4:56:17",255.255.255.255,00,ssh,"admin","123"

The general pattern is s/([^,]*),/"1",/n where n refers to the nth occurrence of zero or more non-, characters followed by a , and 1 is a reference to the captured pattern (between parentheses).