4

This problem is related to my attempt to import questions and their answers in a Excel file into .txt -file which Anki flashcard program handles as described here. I cannot have more than 2 fields so I need to make options one field.

Data stored as CSV from LibreOffice (semicolon as field separator - only distinction what the manual says) as instructed in Anki manual

Question ipsun; option 1 ; option 2 ; option 3 ; option 4 ; ... ; option n
Question ipsun; option 1 ; option 2 ; option 3 ; option 4 ; ... ; option n
...

where each entry with all options is in one line i.e. one "flashcard". In one card, front-part before semicolon, and back-part after semicolon. Second flashcard in newline and so on.

Wanted output which should be in UTF-8

Question ipsun; option 1 | option 2 | option 3 | option 4 | ... | option n
Question ipsun; option 1 | option 2 | option 3 | option 4 | ... | option n
...

My pseudocode in Perl based on this answer

perl -00 -pe s/;/\0/; s/;/ |/g; s/\0/;/' file

Commented

perl -00 -pe '   # each record is separated by blank lines (-00)
                 # read the file a record at a time and auto-print (-p)
    s/;/\0/;    # turn the first semicolon into a null byte
    s/;/ |/g;     # replace all other semicolons with " |"
    s/\0/;/     # restore the first semicolon
' file

How can you replace all semicolons after 1st semicolon?

7
sed 'y/|;/\n|/;s/|/;/;y/\n/|/' <<\IN
Question ipsun; option 1 ; option 2 ; option 3 ; option 4 ; ... ; option n
IN

Note that this does not use a regexp to handle the majority of the replacements, but rather uses a more basic (and far more performant) translation function to do so - and does so in a POSIX portable fashion. This should work on any machine with a POSIX sed installed.

It translates ; semicolons to | pipes and | pipes to \newlines simultaneously. The | pipes are set aside as \newlines in case any occur on an input line. It then s///ubstitutes the first occurring | pipe for a ; semicolon, and then translates all \newlines to | pipes - thus restoring any it might have set aside to robustly handle the single s///ubstitution.

While I use a <<\IN here-document for the sake of copy/pastable demonstration, you should probably use <infile >outfile.

OUTPUT:

Question ipsun; option 1 | option 2 | option 3 | option 4 | ... | option n
9

With GNU sed​:

sed 's/;/|/2g'

Which globally replaces ; with | starting from the 2nd occurrence.

While sed 's/;/|/2 and s/;/|/g are POSIX, the combination is not and the behaviour varies across implementations. With the GNU implementation of sed however, the behaviour is clearly documented.

  • This looks great! Can you please explain this /2g? Globally after second replacement. Is this efficient operation? I do not understand how you can build so short option efficiently. Can you, please, also compare this to Mikeserv's answer. Your is much more readable. How are they different etc? – Léo Léopold Hertz 준영 Jun 11 '15 at 19:00
  • @Masi, mikeserv's is portable, this one's shorter and more efficient with GNU sed. I'd go with mikeserv's unless you know your code or any copy of it will never end-up on a non-GNU system. Performance is probably not an issue unless your input is several megabytes – Stéphane Chazelas Jun 11 '15 at 19:10
  • I am studying this command more here unix.stackexchange.com/q/209086/16920 Interested in how you can extend it. – Léo Léopold Hertz 준영 Jun 11 '15 at 19:25
  • 1
    Do you have any idea how it's handled? I've come to believe // works out under the hood to something like a call to regexec() after /address/ calls regcomp(), but the s///[num]g confuses me in that context - would it work by doing both for one? – mikeserv Jun 11 '15 at 19:26
4

Simply sed -e 's/;/|/g' -e 's/|/;/' data.csv gets you:

Question ipsun; option 1 | option 2 | option 3 | option 4 | ... | option n

Which looks like what you wanted.

2

I didn't notice an awk version, so here it is:

awk -F';' '{for(i=2;i<=NF;i++)gsub(" ; "," | ");print}' testfile1.txt

Output example:

$ cat testfile1.txt                                                                                
Question ipsun; option 1 ; option 2 ; option 3 ; option 4 ; ... ; option n
Question ipsun; option 1 ; option 2 ; option 3 ; option 4 ; ... ; option n

$ awk -F';'  '{for(i=2;i<=NF;i++)gsub(" ; "," | ");print}' testfile1.txt                           
Question ipsun; option 1 | option 2 | option 3 | option 4 | ... | option n
Question ipsun; option 1 | option 2 | option 3 | option 4 | ... | option n
2

You made a slight mistake:

~$ perl -00 -pe 's/;/\0/; s/;/\n/g; s/\0/;/' file
Question; option 1
 option 2
 option 3
 option 4
 ...
 option n

what you have done:

s/;//g;     # remove all other semicolons
s/\0/;/     # restore the first semicolon

what you stated you want:

s/;/\n/g;     # change all other semicolons
s/\0/;/     # restore the first semicolon

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.