11

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:

TSV opening in Calc

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

Proper columns

16

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 :

  1. 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/\r\n/\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).

  • 1
    A note about Windows-style line endings: the standard tools to convert between them and Unix-style are dos2unix and unix2dos. – David Foerster Apr 26 '18 at 10:50
13

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
  • This method has the benefit that it behaves reasonably even when the total number of lines of input is not a multiple of four. – Eliah Kagan Apr 25 '18 at 16:16
3

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.

3

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.)

3

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:

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/ $//'
2

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 "%s\n" "$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.

  • 1
    This isn't portable to POSIX compatible shells in general, because the $' ' form of quoting is not required by POSIX. For example, in dash (which provides sh by default on Ubuntu), running printf '%s\n' $'a\tb' just outputs $a\tb. That doesn't mean this isn't useful though; it does work in bash. However, as with some of the other solutions people have posted, it produces incomplete output if the number of lines of input is not a multiple of four. Also, I recommend using read -r, since there's no reason to think the expansion of backslash escapes in the input file is desired here. – Eliah Kagan Apr 25 '18 at 18:14
  • You could simply do printf '%s\t%s\t%s\t%s\n' "$one" "$two" "$three" "$four" – terdon Apr 26 '18 at 8:59
2

A vim macro (recorded with q) could apply your operation, then skip three lines. Then, you just run that macro n times.

eg:

qq $ J i <TAB> <ESC> $ J i <TAB> <ESC> $ J i <TAB> <ESC> ^^ j qq 100 @q
2

Since you asked for a Gedit solution, something like this should work:

Find:

(\w+)[\r\n]+(\w+)[\r\n]+(\w+)[\r\n]+(\w+)[\r\n]+

Replace with:

\1\t\2\t\3\t\4\n

Make sure that the checkbox for regular expressions is marked.

How it works:

The first step is to find a series of word characters, with \w+, and capture the results in the variable \1 by wrapping parentheses around the expression:

(\w+)

Next we search for a series of line ending characters, \r and \n, or CR and LF. Since Windows formatted files use both, we create a character class by wrapping these two characters in square brackets. The plus makes it search for one or more characters:

[\r\n]+

Finally, we repeat this 3 more times, storing each subsequent word in variables \2, \3, and \4. This makes our replace with expression simple. We just need to place tab characters, \t, and a new line character, \n, in the appropriate places for the formatting you need.

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.