CSV to TSV without replacing commas within quotes

Question

All the CSV to TSV tutorials are suggesting a simple:

tr ',' '\t'

though some CSVs look like this:

1,310,"IntAct,PINA"

in which case I would like to keep "IntAct,PINA":

1   310 "IntAct,PINA"

How could I parameterize the tr command (or sed, etc.) in order to do that?

I appreciate any suggestions.

did you have you try any of the answers? – aborruso Jul 15 '19 at 12:42 — aborruso, Jul 15 '19 at 12:42

pLumo · Answer 1 · 2019-06-25T13:34:53.347

2

Use csvformat from csvkit:

csvformat -d, -D$'\t' file

or shorter:

csvformat -T file

-d input delimiter (not needed here, as , is the default input delimiter)

-D output delimiter

-T set tabs as output delimiter

It will remove the quotes, as they are not needed for a tsv.

You should be able to install csvkit via pip:

sudo apt install python-pip
pip install csvkit

edited Jun 25 '19 at 13:34

answered Jun 25 '19 at 13:31

pLumo

... FWIW I had a bit of a play with the quoting options and it seems you can use `-U2` to force quoting of the non-numeric field in the output, but it only works if you use `-u2` for the input format - which appears to force floating-point conversion as well – steeldriver Jun 25 '19 at 13:39

score 0 · Answer 2 · answered Jun 25 '19 at 14:12

If csvkit (which I recommend) is not available, then you could use the perl Text::CSV module:

perl -MText::CSV -lne '
  BEGIN{$p = Text::CSV->new} print join "\t", $p->fields() if $p->parse($_)
' file

If you insist on retaining the quoting (which is unnecessary, since the embedded , is no longer a separator), then you could do something like

print join "\t", map { $_ =~ s/.*,.*/"$&"/r } $p->fields() if $p->parse($_)

score 0 · Answer 3 · answered Jun 25 '19 at 22:05

0

Using your CSV without heading

1,310,"IntAct,PINA"

mlr --nidx --ifs "," --ofs "\t" cat input.csv

gives you back

1       310     "IntAct PINA"

answered Jun 25 '19 at 22:05

aborruso

3 Answers3