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
    Apr 19, 2017 at 2:31
  • 1
    Replace non quoted commas with tabs... Apr 19, 2017 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, 2017 at 2:36
  • 8
    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, 2017 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, 2017 at 16:55

17 Answers 17



Add to file named csv2tab, and make it executable

touch csv2tab && chmod u+x csv2tab

Add to it

#!/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                     
A       C   D,E,F   G   I   K,L,M   Z

$ ./csv2tab < 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
  • 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, 2020 at 17:42
  • 3
    @kev This code doesn't require an X Server, so seems you may have some other issue Feb 11, 2020 at 18:04

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

  • 3
    Command line I used to fix the quoting: csvformat -U 3 -Q "" -T in.csv > out.tsv May 13, 2020 at 15:30

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

If your sed doesn't support either -E or -r, try

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

again possibly with the tweaks suggested above if \t is not supported.

For additional fun, here's the same thing with the Bash "here-string" syntax, just to demonstrate what it looks like. Notice how the literal backslashes we want sed to receive are now doubled:

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

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. Apr 19, 2017 at 22:46
  • your command works with my sed version (4.4). Unfortunatly with another Linux the sed version is 4.2.2 and don't work with the same data, even after replace -E by -r. Do you have a clue how to fix that ? Thanks
    – nstatam
    Aug 30, 2021 at 13:31
  • @nstatam If you get an error message for both -E and -r, you will need to refactor to a POSIX BRE regex (see updated answer now). If you don't, I'm guessing you transcribed the script incorrectly. Did you try with a literal tab instead of the symbolic sequence \t? Which Linux distro exactly is this?
    – tripleee
    Aug 30, 2021 at 14:22
  • Great answer, but note that it doesn't strip the double quotes from the final field if there is no trailing comma (e.g. "a","b" becomes a<TAB>"b"). This can be fixed by appending a comma at the start, then removing the corresponding trailing tab afterward: sed -E 's/$/,/; s/("([^"]*)")?,/\2\t/g; s/\t$//'
    – tom
    Dec 31, 2021 at 11:50

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 Apr 19, 2017 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, 2019 at 18:33

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.


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, 2017 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 

Newer releases of Miller combine these long options into a single -N option:

mlr --c2t -N cat file.csv


A               C       D,E,F   G       I       K,L,M   Z
  • I didn't see anything in the original question about disabling headers. When I decided to use mlr to convert a CSV file (with headers) to an equivalent TSV file (keeping headers, naturally), I just used: mlr --c2t cat input.csv > output.tsv The results were perfect. Oct 22, 2020 at 16:07
  • 1
    @Mr.LanceESloan If the file only contains a line such as the one shown in the question, and if you don't tell mlr to read the data without a header, then the output will be empty.
    – Kusalananda
    Feb 9 at 12:21
  • I understand. I applied the solution to a similar problem, but I'd forgotten that the original question used input without column headers. Feb 28 at 14:38


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

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

perl -pe 's{"(.*?)"|,}{$1//"\t"}ge'
  • obfuscated but very cool!
    – JJoao
    Jul 11, 2020 at 9:19

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.

  • This does not convert CSV to TSV. It converts a list of JSON strings to TSV.
    – Kusalananda
    Feb 9 at 12:26

sed solution (with requirements): One really should also handle literal double-quotes, which are represented by a pair of double-quotes within a CSV field.

First change all such double-quote literals to a stand-in flag; then delete all field delimiters (solitary double-quotes), and convert field separators (commas) to tabs; then change the stand-in flags to double-quotes.

Here I'm using \v (vertical tab) as the stand-in flag for embedded double-quotes:

sed -r 's/""/\v/g; s/("([^"]+)")?,/\2\t/g; s/\v/"/g\'

This solution relies on your CSV data not containing vertical-tab characters, and on empty CSV fields not being quoted.

  • 1
    This version also works if a line ends with a double-quoted field: echo '1337,"test input"'|sed -r 's/""/\v/g;s/("([^"]+)")?,/\2\t/g;s/"([^"]+)"$/\1/;s/\v/"/g' (the dollar sign is not required, but it's used to clarify the intent of the command). Both your and my version fail when a field consists of 4 double quotes (i.e. a single double quote in escaped form): "next field is a single escaped double quote","""".
    – nisetama
    Jul 21, 2020 at 7:39

After gem install csv:

$ time </tmp/a ruby -rcsv -e'puts CSV.parse($<).map{|x|x*"\t"}'>/dev/null
$ time ruby -rcsv -e'CSV.foreach("/tmp/a"){|x|puts x*"\t"}'>/dev/null
$ time </tmp/a ruby -rcsv -ne'puts CSV.parse($_)*"\t"'>/dev/null

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, 2018 at 12:13

Parsing context sensitive formats is indeed a constant annoyance esp. when csv quoting is inconsistent and even irregular. Here is some straightforward awk that anyone ought to be able to use/revise. It's not as clever as terse awk, but perhaps a good building block that is easily tweaked. Yes, I know it could be shortened, but I like reusable awk to have a bit of slack.

  mark[++nmark] = 0
  thislength = length($0)
  for (i=1; i<=thislength; i++) {
    thisc = substr($0,i,1)
    if (thisc == "\\") { i++; continue } # skip \x for any x
    if (thisc == "\"") qflag = 1-qflag
    if (thisc == "," && !qflag) mark[++nmark] = i
  mark[++nmark] = thislength+1
  for (i=1; i<nmark; i++) {
    thiso = outfield[++noutfield] = substr($0, mark[i]+1, mark[i+1]-mark[i]-1)
    if (thiso ~ /^".*"$/) {
  for (i=1; i<noutfield; i++) printf "%s", outfield[i]"\t"
  print outfield[noutfield]

A one-liner using Python and Pandas:

python -c 'import sys; import pandas as pd; pd.read_csv(sys.argv[1]).to_csv(sys.argv[2], sep="\t", index=False)' input.csv output.tsv

Formatted for readability:

python -c '
import sys
import pandas as pd
pd.read_csv(sys.argv[1]).to_csv(sys.argv[2], sep="\t", index=False)
' input.csv output.tsv

You must log in to answer this question.