csv - How to convert a tab-separated file into a comma-separated file?

ID : 131394

viewed : 7

Tags : csvunixcsv

Top 5 Answer for csv - How to convert a tab-separated file into a comma-separated file?

vote vote

91

The answer for OSX is different.

MacOS does not understand \t in the sed expression.

You have to insert the tab literal into the sed search pattern by using ctrl+v then tab (see How can I insert a tab character with sed on OS X?)

sed 's/ /,/g' input_file > output_file 
vote vote

87

You can use sed as:

sed 's/\t/,/g' input_file > output_file 

This will keep the input file unchanged and will create a new file output_file with the changes.

If you want to change the input file itself without creating a new file you can use -i option to sed to do inplace changes:

sed -i 's/\t/,/g' input_file  
vote vote

74

Bear in mind that there are many flavours of comma-separated-value file. Since you didn't specify one, I'll assume RFC-4180 format, in UTF-8 encoding, and the TSV to be the same but using tabs instead of commas.

The naive approach would be to simply replace every tab with a comma:

tr '\t' , 

This falls down if any of the values already contain a comma, or if any contain a quoted tab. You'll need to minimally parse the file, to maintain quoting. Instead of hand-rolling such a parser, it's simpler, clearer and more flexible to use one already written, such as Text::CSV for Perl:

#!/usr/bin/perl -w  use Text::CSV;  my $tsv = Text::CSV->new({ sep_char => "\t", auto_diag => 2 }); my $csv = Text::CSV->new();  while (my $row = $tsv->getline(*ARGV)) {     $csv->print(STDOUT, $row) or die $csv->error_diag();     print $/; } $csv->error_diag() unless $tsv->eof; 
vote vote

61

This can also be achieved with Perl:

In order to pipe the results to a new output file you can use the following:
perl -wnlp -e 's/\t/,/g;' input_file.txt > output_file.csv

If you'd like to edit the file in place, you can invoke the -i option:
perl -wnlpi -e 's/\t/,/g;' input_file.txt

If by some chance you find that what you are dealing with is not actually tabs, but instead multiple spaces, you can use the following to replace each occurrence of two or more spaces with a comma:
perl -wnlpi -e 's/\s+/,/g;' input_file

Keep in mind that \s represents any whitespace character, including spaces, tabs or newlines and cannot be used in the replacement string.

vote vote

59

After testing in MacOS, this is working for converting csv file to tsv file (suppose no tab or comma exists in the column values):

cat file_input.tsv | tr '\t' ',' > file_output.csv

I tried:

sed 's/ /,/g' input_file > output_file

and

sed 's/\t/,/g' input_file > output_file

However, neither of them worked.

Top 3 video Explaining csv - How to convert a tab-separated file into a comma-separated file?

Related QUESTION?