7

I have a two-column, space delimited .txt file, but the first column has spaces (which are errors). I need to convert it to a csv, but I can't just replace all the spaces with commas.

Example input:

gi|118592783|ref|ZP_01550172.1|_biphenyl-2  3-diol_1    2-dioxygenase_[Stappia_aggregata_IAM_12614] 1

Desired output:

gi|118592783|ref|ZP_01550172.1|_biphenyl-23-diol_12-dioxygenase_[Stappia_aggregata_IAM_12614],1

How can I use sed (or anything else) to replace the last space in a row with a comma, then remove all remaining spaces? Would that effectively create a CSV file?

8

Something like:

sed -r 's/(.*) /\1,/; s/ //g'

The first substitution, being greedy, will cover all but the last space in the group, replacing the last with a ,. The second will then eliminate the rest.

  • It's not working correctly for me :/ (replaces a space between the line except the last one). – Ravexina Jun 15 '17 at 19:07
  • @Thor Now it's correct ;) – Ravexina Jun 15 '17 at 19:44
  • 1
    @Thor thanks! My keyboard's been a PITA since I spilt coffee on it. :( – muru Jun 16 '17 at 0:50
  • 4
    @muru rule of IT, Software Engineering, and Engineering #1: Thou shall not spill your coffee, but consume it completely, cup by cup – Sergiy Kolodyazhnyy Jun 16 '17 at 0:53
  • Easier solution: sed 's/.* /&,/;s/ //g'. When all spaces get removed anyhow, it makes no difference to append the comma after the last space, so you don't need that \1 stuff and extended RE. – Philippos Aug 15 '17 at 11:45
3

This would do the job:

sed -r "s/\s([0-9]+$)/,\1/" filename.txt | tr -d ' '

or:

sed -r "s/\s([0-9]+$)/,\1/; s/\s//g" filename.txt

Input example:

gi|118592783|ref|ZP_01550172.1|_biphenyl-2 3-diol_1    2-dioxygenase_[Stappia_aggregata_IAM_12614] 1

Output:

gi|118592783|ref|ZP_01550172.1|_biphenyl-23-diol_12-dioxygenase_[Stappia_aggregata_IAM_12614],1
  • 1
    I like the example with tr since it's explicit, so more readable to new users, but second sed is quote good as well. – Sergiy Kolodyazhnyy Jun 16 '17 at 2:36
3

Here's a geeky way - with a sed loop.

  1. if the pattern contains only a single space, replace it with a comma
  2. (otherwise) replace the first space with nothing and goto 1

which we can write in GNU sed as

sed -e :1 -e '/^[^ ]* [^ ]*$/ s/ /,/' -e 's/ //; t1'

Testing:

$ echo 'gi|118592783|ref|ZP_01550172.1|_biphenyl-2  3-diol_1    2-dioxygenase_[Stappia_aggregata_IAM_12614] 1' | 
  sed -e :1 -e '/^[^ ]* [^ ]*$/ s/ /,/' -e 's/ //; t1'
gi|118592783|ref|ZP_01550172.1|_biphenyl-23-diol_12-dioxygenase_[Stappia_aggregata_IAM_12614],1
3

Perl

$ perl -ne 's/\s//g;s/^(.*)([[:digit:]])$/\1,\2/;print' input.txt                                                                                    
gi|118592783|ref|ZP_01550172.1|_biphenyl-23-diol_12-dioxygenase_[Stappia_aggregata_IAM_12614],1

or shorter:

perl -pe 's/\s//g;s/^(.*)([[:digit:]])$/\1,\2/' input.txt 

Effectivelly this is the opposite of muru's approach: we get rid of all spaces first, then group everything before last item (group \1) and last item (group \2, two which happens to be digit). We replace the line with group \1 and \2 being separated by comma.

Note that ([[:digit:]]) can be changed into (.) to reference any character,in case that's necessary (i.e., if we expect last char to be of any type), or we can use ([[:graph:]]) to deal with only printable chars

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.