Unable to simply use tr to convert a tab-delimited-variable file to a comma-delimited-variable file

Question

Regarding this txt file, which is best described as a tab-delimited .txt file with empty lines between rows containing content:

One would think that running

cat goods.txt | tr "\t" "," > output.csv should cleanly convert the .txt file to .csv.

However, I am observing values and text shifting into the wrong columns:

And in other cases, numbers being split up between columns:

I'm not sure what I'm overlooking? What is a correct way to achieve the file conversion so desired here?

I'm pretty sure that originally said "50,000 PAGES", which explains your problem, but I don't know how to fix it. — Michael Homer
– Michael Homer, Commented Jul 25, 2019 at 8:44
... use a tool that understands how to quote embedded delimiters, such as csvformat -t goods.txt > goods.csv — steeldriver
– steeldriver, Commented Jul 25, 2019 at 10:25
You make a , a delimiter but clearly already have , in the file. An(!) option could be to 1st remove all current ,'s from the file. A 50,000 would then turn into 50000 not invoking a delimiter with your tr action. But that depends on your data: that could remove other ,'s too that must not be removed. — Rinzwind
– Rinzwind, Commented Jul 25, 2019 at 10:45
I can't make any sense of it ether. So not surprised that the computer can't. I recommend that you do the first 3 or 4 lines manually, so that you get an understanding of the file. — ctrl-alt-delor
– ctrl-alt-delor, Commented Jul 25, 2019 at 11:36
Would you please give a extract of yout txt file (with anon datas if there are some) ? Some more informations are needed to understand, and eventually give it a try in order to help. (in the question) — ctrl-alt-delor
– ctrl-alt-delor, Commented Jul 25, 2019 at 11:36

aborruso · Accepted Answer · 2019-07-25 16:00:05Z

mlr --t2c cat goods.txt >goods.csv

Is what you want?

ptrcao · Accepted Answer · 2019-07-25 23:31:36Z

The following csvkit utility converts the .txt to .csv file cleanly:

csvformat -t goods.txt

Credit goes to reg at Whirlpool forums.

More about csvformat here (converts a CSV file to a custom output format).

Also note, as per the csvkit manual:

===========

Note that every csvkit tool does the following:

removes optional quote characters, unless the –quoting (-u) option is set to change this behavior
changes the field delimiter to a comma, if the input delimiter is set with the –delimiter (-d) or –tabs (-t) options
changes the record delimiter to a line feed
changes the quote character to a double-quotation mark, if the character is set with the –quotechar (-q) option
changes the character encoding to UTF-8, if the input encoding is set with the –encoding (-e) option

===========

2 Answers 2