Home / ubuntu / Questions / 1153856
In Process
tr3quart1sta
Asked: 2019-06-26 05:24:55 +0800 CST

CSV to TSV without replacing commas within quotes

  • 772

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.

sed text-processing csv tr

3 Answers

  1. 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
    • 2

  2. 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($_)
    • 0

  3. Using your CSV without heading

    1,310,"IntAct,PINA"

    and Miller (https://github.com/johnkerl/miller)

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

    gives you back

    1       310     "IntAct PINA"
    • 0