Script that add double quotes to every column in csv file problem

user_13069668 0
Viewed 647

i have a CSV file with 4 columns. e.g. 

1132,John Doe,johndoe@gmail.com,3534534543

53213,John Doe,johndoe@test.com,51352363126

I want to add double quotes for every value so I use this script on MAC: 

sed 's/[^,]*/"&"/g' file.csv > file2.csv

I receive

"1132","John Doe","johndoe@gmail.com","3534534543
"

"53213","John Doe","johndoe@test.com","51352363126
"

So I get the last quotes on new rows, most probably I should remove /r/n somehow, I tried but I couldn`t. Any ideas? It happens with the files that I receive if I fill values manually it works as expected.

sed add csv awk unix
3 Answers

Could you please try following.

awk 'BEGIN{FS=",";RS="\r\n";s1="\"";OFS="\",\""} {$1=$1;$0=s1 $0 s1} 1' Input_file

In case you want to leave empty lines then try following.

awk 'BEGIN{FS=",";RS="\r\n";s1="\"";OFS="\",\""} NF{$1=$1;$0=s1 $0 s1} 1' Input_file
user_58665800

As you suspected, it is possible that the file you received has different control characters at the end of the line.

One easy fix is to exclude control characters, as well as comma, from matching. That is, instead of searching for [^,]*, you can search for [^,[:cntrl:]]*.

Admin1

I'd use a proper CSV parser on CSV data. Ruby ships with one, so you can write

ruby -rcsv -e '
    csv_in  = CSV.new(STDIN)
    csv_out = CSV.new(STDOUT, force_quotes: true)
    csv_in.each {|row| csv_out << row}
' < file.csv
user_75520
Related