I have a number of large CSV files and would like them in TSV (tab separated format). The complication is that there are commas in the fields of the CSV file, eg:

 A,,C,"D,E,F","G",I,"K,L,M",Z

Expected output:

 A      C   D,E,F   G   I   K,L,M   Z

(where whitespace in between are 'hard' tabs)

I have Perl, Python, and coreutils installed on this server.

  • I would do this with node.js or with perl. – peterh Apr 19 '17 at 2:31
  • 1
    Replace non quoted commas with tabs... – cricket_007 Apr 19 '17 at 2:32
  • Yes, if I had more than 5 minutes to this question. But I will happily support the answerers with my votes. What I tried to say, that the common sed/awk things are probably ineligible for that (at least in their commonly used usage). – peterh Apr 19 '17 at 2:36
  • 6
    I'm not sure if your example is representative of the actual data, but if those are going to be actual text strings then don't forget that you may need to handle the case where the string includes a tab... – A C Apr 19 '17 at 4:47
  • 2
    The other tricky part is that CSV is a very loosely defined format, there is no real standard (there is a RFC but it was written years after the fact). I have written code that used a language-supplied CSV parser and then had to rewrite it with a custom parser because I found the input data was in a broken variant of the csv format. – plugwash Apr 19 '17 at 16:55

12 Answers 12

up vote 34 down vote accepted

Python

Add to file named csv2tab.sh, and make it executable

#!/usr/bin/env python
import csv, sys
csv.writer(sys.stdout, dialect='excel-tab').writerows(csv.reader(sys.stdin))

Test runs

$ echo 'A,,C,"D,E,F","G",I,"K,L,M",Z' | ./csv2tab.sh                         
A       C   D,E,F   G   I   K,L,M   Z

$ ./csv2tab.sh < data.csv > data.tsv && head data.tsv                                                   
1A      C   D,E,F   G   I   K,L,M   Z
2A      C   D,E,F   G   I   K,L,M   Z
3A      C   D,E,F   G   I   K,L,M   Z
  • 2
    You don't need the StringIO, just use sys.stdin directly: ideone.com/SAeeya – muru Apr 19 '17 at 6:12
  • 3
    A possible bug: this answer doesn't escape internal tabs. – Morgen Apr 19 '17 at 7:41
  • 4
    @Morgen csv.writer(sys.stdout, dialect='excel-tab').writerows(csv.reader(sys.stdin))? Eliminates the loop as well. – muru Apr 19 '17 at 9:02
  • 1
    @chx try python -c 'import csv,sys; csv.writer(sys.stdout, dialect="excel-tab").writerows(csv.reader(sys.stdin))'. I doubt -m works that way. – muru Apr 19 '17 at 10:04

For fun, sed.

sed -E 's/("([^"]*)")?,/\2\t/g' file

If your sed doesn't support -E, try with -r. If your sed doesn't support \t for a literal tab, try putting a literal tab (in many shells, ctrl-v tab) or in Bash, use a $'...' C-style string (in which case the backslash in \2 needs to be doubled). If you want to keep the quotes, use \1 instead of \2 (in which case the inner pair of parentheses is useless, and can be removed).

This makes no attempt to handle escaped double quotes inside double quotes; some CSV dialects support this by doubling the quoted double quote (sic).

  • I think that i tried about 100 different sed scripts to achieve this one but all my attempts failed. This is awesome. – George Vasiliou Apr 19 '17 at 22:46

One option might be perl's Text::CSV module e.g.

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

to demonstrate

echo 'A,,C,"D,E,F","G",I,"K,L,M",Z' |
  perl -MText::CSV -lne 'BEGIN { $csv = Text::CSV->new() }
  print join "\t", $csv->fields() if $csv->parse($_)
'
A       C   D,E,F   G   I   K,L,M   Z
  • 1
    Wouldn't be correct if a field contains a tab – Neil McGuigan Apr 19 '17 at 20:49

Using csvkit utility (Python), for example:

$ csvformat -T in.csv > out.txt

Does streaming, with correct CSV and TSV quoting and escaping

It's in apt and other package managers

Perl

perl -lne '
   my $re = qr/,(?=(?:[^"]*"[^"]*")*(?![^"]*"))/;
   print join "\t", map { s/(?<!\\)"//gr =~ s/\\"/"/gr } split $re;
'

Awk

awk -v Q=\" -v FPAT="([^,]*)|(\"[^\"]+\")" -v OFS="\t" '{
   for (i=1; i<=NF; ++i)
      if ( substr($i, 1, 1) == Q )
         $i = substr($i, 2, length($i) - 2)
   print $1, $2, $3, $4, $5, $6, $7, $8
}'

Result:

A               C       D,E,F   G       I       K,L,M   Z

The thermonuclear flyswatter solution must be using libreoffice. While https://ask.libreoffice.org/en/question/19042/is-is-possible-to-convert-comma-separated-value-csv-to-tab-separated-value-tsv-via-headless-mode/ suggests this is not possible but it is wrong (or just outdated?) and the following command works on my 5.3.:

loffice "-env:UserInstallation=file:///tmp/LibO_Conversion" --convert-to csv:"Text - txt - csv (StarCalc)":9,34,UTF8 --headless --outdir some/path --infilter='csv:44,34,UTF8' *.csv

the env argument could be skipped but this way the documents won't appear in your recent document.

  • 2
    I think the true thermonuclear flyswatter would be writing a Java utility to do it via LibreOffice's UNO API :). – Pont Apr 20 '17 at 12:12

If you have, or can install, the csvtool utility:

csvtool -t COMMA -u TAB cat in.csv > out.ctv

Note that for some reason csvtool doesn't have a man page, but csvtool --help will print a couple hundred lines of documentation.

I authored an open-source CSV to TSV converter that handles the transformations described. It's quite fast, may be worth a look if there's an on-going need to convert large CSV files. Tool is part of eBay's TSV utilities toolkit (csv2tsv documentation here). Default options suffice for the input described:

$ csv2tsv file.csv > file.tsv

Vim

Just for fun, regex substitutions can be performed in Vim. Here's a potential four line solution, adapted from: https://stackoverflow.com/questions/33332871/remove-all-commas-between-quotes-with-a-vim-regex

  1. Commas between quotes are first changed to underscores (or other absent character),
  2. All other commas are replaced with tabs,
  3. Underscores inside quotes are restored to commas,
  4. Quotation marks are removed.

    :%s/".\{-}"/\=substitute(submatch(0), ',', '_' , 'g')/g
    :%s/,/\t/g
    :%s/_/,/g
    :%s/"//g
    

To script the solution somewhat, the four lines above (sans leading colon) can be saved to a file, e.g. to_tsv.vim. Open each CSV for editing with Vim and source the to_tsv.vim script on the Vim command line (adapted from https://stackoverflow.com/questions/3374179/run-vim-script-from-vim-commandline/8806874#8806874):

    :source /path/to/vim/filename/to_tsv.vim

Using mlr is almost succinct, but disabling headers requires long options:

mlr --c2t --implicit-csv-header --headerless-csv-output cat file.csv 

Output:

A       C   D,E,F   G   I   K,L,M   Z

The following is simply a correction to the answer from @triplee so that it strips any quotes from the final field just as it does to all the other fields.

To show what's being corrected, below is a triplee's answer, plus a slight modification to the OP's example data with added quotes around the final 'Z' field.

echo 'A,,C,"D,E,F","G",I,"K,L,M","Z"' |  sed -r -e 's/("([^"]*)")?,/\2\t/g'
A       C   D,E,F   G   I   K,L,M   "Z"

You can see that 'Z' is left with quotes around it. This is different to how the inner fields are handled. For example, the 'G' does not have quotes on it.

The following command uses a second substitution to clean the final column:

echo 'A,,C,"D,E,F","G",I,"K,L,M","Z"' |  sed -r -e 's/("([^"]*)")?,/\2\t/g' \
                                                -e 's/\t"([^"]*)"$/\t\1/'
A       C   D,E,F   G   I   K,L,M   Z
  • When the input data 'A,,C,"D,E,F","G",I,"K,L,M","Z,A"' is input to this answer, then the "Z,A" is incorrectly replaced with Z A, rather than the correct Z,A. – agc Apr 13 at 12:13

Here is the example of converting CSV into TSV using jq utility:

$ jq -rn '@tsv "\(["A","","C","D,E,F","G","I","K,L,M","Z"])"'
A       C   D,E,F   G   I   K,L,M   Z

or:

$ echo '["A","","C","D,E,F","G","I","K,L,M","Z"]' | jq -r @tsv
A       C   D,E,F   G   I   K,L,M   Z

However the CSV format needs to be well formatted, so each string needs to be quoted.

Source: Simple TSV output format.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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