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:


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 - Reinstate Monica 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 - Reinstate Monica Apr 19 '17 at 2:36
  • 7
    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
  • 4
    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

13 Answers 13



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
  • 5
    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
  • i get the following error when I try your solution: No protocol specified import-im6.q16: unable to open X server :0' @ error/import.c/ImportImageCommand/358. csv2tab.sh: line 3: syntax error near unexpected token sys.stdout,' csv2tab.sh: line 3: csv.writer(sys.stdout, dialect='excel-tab').writerows(csv.reader(sys.stdin))' – kev Feb 11 at 17:42
  • 1
    @kev This code doesn't require an X Server, so seems you may have some other issue – cricket_007 Feb 11 at 18: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).

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

Using csvkit (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


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


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


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


A               C       D,E,F   G       I       K,L,M   Z
  • +1 Perl version works like a charm – ATorras Jan 9 '19 at 18:33

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.


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

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


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

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

A consideration when converting CSV to TSV is handling of field and record delimiters (comma and newline) in the data. CSV uses an escape syntax. If the goal is to use the output with Unix tools like cut, awk, etc., the output needs to be free of escapes. Most solutions listed here produce CSV style escapes when delimiters are in the data. csv2tsv is differentiated from other solutions in that it produces TSV without escapes. See the documentation for details.

To see what a particular solution does, convert a CSV containing commas, tabs, quotes, and newlines in the data. For example:

$ echo $'Line,Field1,Field2\n1,"Comma: |,|","Quote: |""|"\n"2","TAB: |\t|","Newline: |\n|"' | <conversion-script-or-command>

Solutions generating escapes will put double quotes around the fields containing quotes, newlines, or tabs.



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

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

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


$ 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.


With perl, assuming the csv fields have no embedded " or newlines or tabs:

perl -pe 's{"(.*?)"|,}{$1//"\t"}ge'

The following is simply a correction to the answer from @tripleee 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 tripleee'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
  • 1
    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 '18 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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