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

Question

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.

See https://stackoverflow.com/questions/45772525/why-does-my-tool-output-overwrite-itself-and-how-do-i-fix-it?noredirect=1#comment97778025_45772525 — Ed Morton, May 14 '20 at 19:05
Add output of `hexdump -C file` or `xxd file` to your question. — Cyrus, May 15 '20 at 04:47

RavinderSingh13 · Accepted Answer · 2020-05-14T17:51:28.620

2

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

edited May 14 '20 at 17:51

answered May 14 '20 at 17:41

RavinderSingh13

130,504
14
57
93

Thanks but same case. I guess its related with that its on Mac. I will try it on Windows or Linux.. – Nikolay Rumenov May 14 '20 at 17:47
2

I suggest to add `RS="\r\n"; FS=",";` in `BEGIN` section. – Cyrus May 14 '20 at 17:48
Sorry but where exactly? – Nikolay Rumenov May 14 '20 at 17:50
@NikolayRumenov, as per Cyrus sir suggestion I have added them now. – RavinderSingh13 May 14 '20 at 17:57
I pasted the result below – Nikolay Rumenov May 14 '20 at 18:13
1

@NikolayRumenov, Could you please do lemme know if you have tried my 2nd solution `awk 'BEGIN{FS=",";RS="\r\n";s1="\"";OFS="\",\""} NF{$1=$1;$0=s1 $0 s1} 1' Input_file` this should work IMHO, lemme know please. – RavinderSingh13 May 14 '20 at 18:16
@Cyrus - any such suggestion that would fix an `awk` solution should, likely, fix the OP's original `sed` solution too. Which would be a much better approach; `awk` is great, but it's not fast - compared to `sed`. – May 15 '20 at 04:42
@RavinderSingh13 i tested it. Same case. I tested all solutions on Windows and they work very well. Thank you all – Nikolay Rumenov May 15 '20 at 07:11
Do you mind if I ask you how to remove the quotes of the first string after the file is done.. New requirement... – Nikolay Rumenov May 15 '20 at 10:19
@NikolayRumenov, np, you could try like, `awk 'BEGIN{FS=",";RS="\r\n";s1="\"";OFS="\",\""} NF{$1=$1;$0=$0} 1' Input_file` and lemme know how it goes. – RavinderSingh13 May 15 '20 at 10:27
12","John Doe","doe@gmail.com","324234 – Nikolay Rumenov May 15 '20 at 11:36
1

It removes the first one and last one( the last value). so close – Nikolay Rumenov May 15 '20 at 11:37

score 1 · Answer 2 · answered May 15 '20 at 04:40

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:]]*.

score 0 · Answer 3 · answered May 14 '20 at 19:34

0

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

answered May 14 '20 at 19:34

glenn jackman

238,783
38
220
352

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

3 Answers3