1

I want to convert tab delimited csv into " ", add empty space if value is empty

FirstName      LastName        Address1             Address2              City          State  ZIP         
John1           Mark            149 Lower Stereet                          California     CA      05478
John2           Mark                                149 Lower, Stereet    California     CA      05478
John3           Mark            149 ,Lower Stereet                         California     CA      05478

with the desired result

"FirstName","LastName","Address1","Address2","City","State","ZIP"        
"John1","Mark","149 Lower Stereet", ,"California","CA","05478"
"John2","Mark", ,"149 Lower, Stereet","California","CA","05478"
"John3","Mark","149,Lower Stereet", , "California","CA","05478" 

I tried with below command

sed 's/\t\+/,/g;s/^\|$/"/g;s/,/"&"/g' Actual.csv > Actual_V6.csv

Output

"FirstName","LastName","Address1","Address2","City","State","ZIP
"
"John1","Mark1","149 Lower Stereet","California","CA","05489
"
"John2","Mark","149 Lower"," Stereet","California","CA","05489","
"
"John3","Mark","149 ","Lower Stereet","California","CA","05489"

Where

  1. "149 ,Lower Stereet" convert into "149 Lower"," Stereet" it's supposed to be "149 Lower, Stereet"
  2. adding " in the next line
  3. Empty values are not recognized

EDIT

Output of hexdump:

Hexa

Source

Result

closed as unclear what you're asking by RalfFriedl, G-Man, Stephen Harris, jimmij, Romeo Ninov Dec 12 '18 at 7:32

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Do you have two tabs (converted to spaces?) in the line with "John2"? And why is the field "City" repeated? – Christophe Strobbe Dec 11 '18 at 16:22
  • 1
    Possible duplicate of Convert separator character while escaping separator in fields – pLumo Dec 11 '18 at 16:35
  • 1
    Misleading title. You want to convert to , delimited records with double quoted fields. – RudiC Dec 11 '18 at 16:35
  • 1
    Not sure this is the cause for your " on the next line, but your file seems to use Windows CR-LF end of line sequences (you can spot them as 0d 0a). If it is what you want, you can get rid of them by adding a s/\r//g; (or 's/\o015//g') to your sed command, as in sed 's/\r//g; s/\t/","/g; s/.*/"&"/; s/""/ /g'. – fra-san Dec 11 '18 at 18:05
  • 1
    @fra-san .... Thank you very much It's working sed 's/\r//g; s/\t/","/g; s/.*/"&"/; s/""/ /g' – Sanj Dec 11 '18 at 18:10
1

You can use this:

sed 's/\t/","/g; s/.*/"&"/; s/""/ /g' file

s/""/ /g is not really necessary to have a proper csv but to achieve your desired output.

If you have csvtool:

csvtool -t TAB -u ',' cat file

This will only quote the fields where necessary.

  • Hi Rovo,I tried with above it's working fine except adding " to the next line – Sanj Dec 11 '18 at 16:56
  • "FirstName","LastName","Address1","Address2","City","State","ZIP " "John1","Mark1","149 Lower Stereet", ,"California","CA","05489 " "John2","Mark", ,"149 Lower, Stereet","California","CA","05489 " "John3","Mark","149 ,Lower Stereet", ,"California","CA","05489" – Sanj Dec 11 '18 at 16:58
  • "FirstName","LastName","Address1","Address2","City","State","ZIP – Sanj Dec 11 '18 at 17:00
  • " added to next line – Sanj Dec 11 '18 at 17:01
  • "John1","Mark1","149 Lower Stereet","California","CA","05489 – Sanj Dec 11 '18 at 17:05
0

Not near a comp so can’t test but how about a simple

Sed -r ‘s/^/“/;s/$/“/;s/\t/“,”/g;s/“”/ /g’

?
I think the question is wrong though. Quotation marks inside the original tsv will likely confuse any parser requiring them in the final result

  • That works, even though the empty fields get converted into "" instead of just a space between the commas. (See the desired result in the question.) – Christophe Strobbe Dec 11 '18 at 17:10
  • @ChristopheStrobbe added last conversion, hope that’s better, still can’t test – Dani_l Dec 11 '18 at 17:43
  • The new version(s) convert empty fields into quoted single spaces. (In the first version, empty fields were converted into empty quotes, i.e. without a space between them.) B.t.w.: typo: simle -> simple. – Christophe Strobbe Dec 11 '18 at 17:56
  • Why it's getting " in the next line for each row – Sanj Dec 11 '18 at 18:03
  • @Sanj still can’t test but most probably because input has empty lines (or more specifically lines with ^[[:space:]]*\n$ ) – Dani_l Dec 11 '18 at 18:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.