1

How can I change tab-delimited .txt files into CSV files without harming the format?

I only managed to change the type from .txt to .csv but all data that was separated by tab, was placed in the same Excel cell.

So what I'm asking here is:

  1. how to change tab to a comma for many files?
  2. how to then change the files from .txt to .csv?
  • Is there exactly one tab between the columns or could there be more that need to replaced by a single comma? – Philippos Aug 23 '17 at 10:25
  • it's just one tab – old_man_river Aug 23 '17 at 10:56
  • realizing that you're asking on Unix & Linux, I removed the "cmd" solution request along with the other edits. – Jeff Schaller Aug 23 '17 at 12:19
2

You could do:

perl -MText::CSV -F'\t' -i.orig -lape '
  BEGIN{$c = Text::CSV->new({binary=>1, eol=>""})}
  $_ = $c->string if $c->combine(@F)' ./*.txt

To convert to proper CSV (see perldoc Text::CSV in case you need to adjust the format of the CSV). If all you need is to convert tabs to commas, it's just a matter of tr '\t' , < file.txt > file.csv.

  • yes. CSV has a few nasty edge cases, so using a well-tested CSV library is crucial. – glenn jackman Aug 23 '17 at 12:32
1

Using csvkit:

Assuming an input file like

a       10,000  e       u
a       o       e       u
a       o       "hello  world"  u
a       o       e       u

where all fields are tab-delimited and where there's also a tab in-between the hello and world (even though the two words belong to the same field as indicated by the quoting).

$ csvformat -t file.txt
a,"10,000",e,u
a,o,e,u
a,o,hello       world,u
a,o,e,u

The -t option to csvformat tells the utility that the input is tab-delimited.

Notice that the 10,000 is properly quoted and that the quotes around hello<tab>world were removed as it's now unambiguous.

To force quoting of all fields:

$ csvformat -t -U 1 file.txt
"a","10,000","e","u"
"a","o","e","u"
"a","o","hello  world","u"
"a","o","e","u"

If the input is using another quote character, as in

$ cat file.txt
a       10,000  e       u
a       o       e       u
a       o       'quote: "hello  world"' u
a       o       e       u

then do this:

$ csvformat -t -q "'" file.txt
a,"10,000",e,u
a,o,e,u
a,o,"quote: ""hello     world""",u
a,o,e,u

We specify that the single quote is used in the input data with -q "'".


To run this on several files, converting each from tab-delimited to CSV, assuming we want to process all .txt files in the current directory:

for name in ./*.txt; do
    csvformat -t "$name" >"${name%.txt}.csv"
done

The ${name%.txt}.csv bit removes the extension .txt from the filename and adds a .csv extension instead. This can also be written as

for name in ./*.txt; do
    csvformat -t "$name" >"./$( basename "$name" .txt ).csv"
done

You will then have a set of (untouched original) .txt files together with their corresponding .csv files.

-1

You can try the command below

sed 's/\t/,/g' yourtab.txt > updatefile.csv
  • 1
    For proper CSV handling, you would have to double quote any data field that contains a comma already. ... and escape any existing quotes obviously. – Kusalananda Aug 23 '17 at 10:06
  • Thanks but can I use it on more than one file at a time? – old_man_river Aug 23 '17 at 10:20
  • Note that \t for tab is a GNU extension – Philippos Aug 23 '17 at 10:26
  • I managed to change all files but into one file using this: – old_man_river Aug 23 '17 at 10:33
  • sed 's/\t/,/g' *.txt > 1.csv How can I keep the files separated? – old_man_river Aug 23 '17 at 10:33

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.