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.

(as per this Stack Overflow solution)

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

enter image description here

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

enter image description here

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

  • 1
    I'm pretty sure that originally said "50,000 PAGES", which explains your problem, but I don't know how to fix it. Jul 25 '19 at 8:44
  • ... use a tool that understands how to quote embedded delimiters, such as csvformat -t goods.txt > goods.csv Jul 25 '19 at 10:25
  • 2
    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
    Jul 25 '19 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. Jul 25 '19 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) Jul 25 '19 at 11:36

Using Miller (https://github.com/johnkerl/miller)

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

you will have this output https://gist.github.com/aborruso/fab6a48a390a9c54de55dece9141bea6

Is what you want?


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


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.