Gfilui

I am trying to convert a text file into a tab separated spreadsheet. My text file is something like this:

Dog
Cat
Fish
Lizard
Wolf
Lion
Shark
Gecko
Coyote
Puma
Eel
Iguana

With standard search and replace functions in Gedit or LibreOffice, it's easyto replace the end of line with a tab. But if I just swap carriage returns for tabs, I'll get this:

Dog Cat Fish Lizard Wolf Lion Shark Gecko Coyote Puma Eel Iguana

But what I need to do is get it to look like this:

Dog Cat Fish Lizard
Wolf Lion Shark Gecko 
Coyote Puma Eel Iguana

So, can I swap every end of line character for a tab except for every fourth line?

I don't know if that kind of conditional iteration can be done with regular expressions inside a program like Gedit or LibreOffice, so maybe this needs to be some kind of command line function? I'm not even clear on what the best tool to start with is.

Update:

I tried the following commands:

sed 'N;N;N;s/n/t/g' file > file.tsv

paste - - - - < file > file.tsv

pr -aT -s$'t' -4 file > file.tsv

xargs -d 'n' -n4 < inputfile.txt

But when I try to open the resulting tsv file in LibreOffice, the columns are not quite right. I'm not sure if this means I'm not executing the above commands correctly, or if I'm doing something wrong in the LibreOffice import function:

Just for reference, the desired result should look like this:

You could use a command-line editor such as sed

sed 'N;N;N;s/n/t/g' file > file.tsv

or, more programatically, by adding backslash line continuation characters to each of the lines you want to join using GNU sed's n skip m address operator and following it with the classic one-liner for joining continued lines:

sed '0~4! s/$/t\/' file | sed -e :a -e '/\$/N; s/\n//; ta'

See for example Sed One-Liners Explained :

39. 
    

    Append a line to the next if it ends with a backslash "".

    
    
    

    sed -e :a -e '/\$/N; s/\n//; ta'
    

    
    

However IMHO itwould be easier with one of the other standard text-processing utilities e.g.

paste - - - - < file > file.tsv

(the number of - will correspond to the number of columns) or

pr -aT -s$'t' -4 file > file.tsv

(you can omit the -s$'t if you don't mind the output to be separated by multiple tabs).

The strange re-import behavior that you are observing is almost certainly because the original file has Windows-style CRLF line endings. If you need to work with files from Windows, then you can roll the conversion into the command in various ways e.g.

tr -d 'r' < file.csv | paste - - - -

or

sed 'N;N;N;s/rn/t/g' file.csv

The former will remove ALL carriage returns whereas the latter will preserve a CR at the end of each of the new lines (which may be what you want if the intended end user is on Windows).

You can use xargs to always group four lines into one, separated with a single space each:

xargs -d 'n' -n4 < inputfile.txt

-d 'n' sets the input delimiter to a newline character, otherwise it would also break on spaces. If you only have one word per input line anyway, you can even omit this.
-n4 sets the argument number (the number of input items per output line) to 4.

Output:

Dog Cat Fish Lizard
Wolf Lion Shark Gecko
Coyote Puma Eel Iguana

Or if you want tabs as separators instead of a space, you can replace them afterwards. However, if you had spaces in your input lines, those would get replaced too:

xargs -d 'n' -n4 | tr ' ' 't'

Output (look depending on browser/terminal's tab width):

Dog Cat Fish Lizard
Wolf Lion Shark Gecko
Coyote Puma Eel Iguana

You could also use:

awk -v ORS="" 'print $1; print NR%4==0?"n":"t"' file > file.tsv 

The two awk built-in variables are:

• 
  ORS: Output Record Separator(default=newline). It is added at the end of each print command.


• 
  NR: Number of the current Row awk is processing.

This command will, for each line, display the content of the first (and here only) column. Then it choose to add a newline or a tab by testing the remainder of the division of NR by 4.

Another shortest awk approach:

awk 'printf $0 (NR%4?"t":"n")' infile

This printf the only one column followed by next and next and ... and a Tab t character after each but will printf a newline character when Number of Record was factor of 4 (where NR%4 will return 0 (false) which is what Ternary Operator condition(s)?when-true:when-false is doing.)

My solution to this would be to use combination of sed and sed. First, you could mark every fourth line with some special character, for example >, using this solution:

• Adding a character to every other text line

In this case you want to start from line 5 and mark every 4th line after it. In GNU sed that can be given as an address 5~4. You can use this command:

sed '5~4s/^/>/' file1 > file2

Then you need to remove the newlines, which can be done with a sed loop:

sed ':a;N;s/n/ /;ba' file2 > file3

There are easier ways to convert newlines to some other character, for example with tr:

tr 'n' ' ' < file2 > file3

Either way, combining the two gives

Dog Cat Fish Lizard >Wolf Lion Shark Gecko >Coyote Puma Eel Iguana

(the sed version leaves a trailing newline, while the tr version does not)

After that, you need only convert the special characters you inserted to newlines; see for example Convert a tab-delimited file to use newlines. In this case, change > to newlines:

sed 'y/>/n/' file3 > outfile

The y command performs the same function as tr, transforming one character into another, but you can use the s command here equally well. With s, you need g to operate on each match in the line (sed 's/>/n/g').

Rather than making two intermediate files, you can use pipes:

$ sed '5~4s/^/>/' file | sed ':a;N;s/n/ /;ba' | sed 'y/>/n/'
Dog Cat Fish Lizard 
Wolf Lion Shark Gecko 
Coyote Puma Eel Iguana

If trailing spaces are a problem, you can add another command to remove them:

| sed 's/ $//'

For the sake of "completeness" here's a pure bash solution:

#!/usr/bin/env bash

sep=$'t'

while read one 
 && read two 
 && read three 
 && read four
do
 printf "%sn" "$one$sep$two$sep$three$sep$four"
done

Works also with spaces, assuming IFS is properly set (which it should by default, AFAIK). Moreover, I think this could even be a portable shell script and work with any POSIX compatible shell.