How to print values in a text file to columnated file using shell script

Question

I have an output.txt from running a shell script as follows:

abc.txt
errorstatus1
Fri Nov 11 02:00:09 2016
def.txt
errorstatus2.txt
Sat Nov 12 03:00:09 2016

The text file has multiple entries line by line in the same manner. I want to print these values into columns: Filename,Status and Timestamp as follows:

Filename      Status        Timestamp
abc.txt     errorstatus1   Fri Nov 11 02:00:09 2016
def.txt     errorstatus2   Sat Nov 12 03:00:09 2016

Solution 1

With paste:

paste - - - <file.txt

this will output the newline separated file content as columns, and three tab separated columns per line.

Adding the header:

echo Filename Status Timestamp; paste - - - <file.txt

To columnize the output, take help from column:

{ echo Filename Status Timestamp; paste - - - <file.txt ;} | column -t

Example:

% cat file.txt
abc.txt
errorstatus1
Fri Nov 11 02:00:09 2016
def.txt
errorstatus2.txt
Sat Nov 12 03:00:09 2016

% { echo Filename Status Timestamp; paste - - - <file.txt ;} | column -t
Filename  Status            Timestamp
abc.txt   errorstatus1      Fri        Nov  11  02:00:09  2016
def.txt   errorstatus2.txt  Sat        Nov  12  03:00:09  2016

Solution 2

You could use awk:

awk 'NR % 3 {printf "%s ", $0; next}1'

Output might not be as pretty:

$ awk 'NR % 3 {printf "%s ", $0; next} 1' input
abc.txt errorstatus1 Fri Nov 11 02:00:09 2016
def.txt errorstatus2.txt Sat Nov 12 03:00:09 2016

You could use %s\t instead for tab-separated output.

• NR % 3 is zero (and false) for every third line, so the other lines are printed with a space after them instead of a newline. next just starts the next iteration.
• Every third line is printed as-is because of the final 1, with a newline after it, since it doesn't match the first block.

Solution 3

There's also rs (BSD reshape utility):

DESCRIPTION
     rs reads the standard input, interpreting each line as a row of blank-
     separated entries in an array, transforms the array according to the
     options, and writes it on the standard output.  With no arguments it
     transforms stream input into a columnar format convenient for terminal
     viewing.

In particular,

     -e      Consider each line of input as an array entry.

So

$ rs -e < file
abc.txt                   errorstatus1              Fri Nov 11 02:00:09 2016
def.txt                   errorstatus2.txt          Sat Nov 12 03:00:09 2016

or (to add the header)

$ { printf '%s\n' Filename Status Timestamp ; cat file ; } | rs -e
Filename                  Status                    Timestamp
abc.txt                   errorstatus1              Fri Nov 11 02:00:09 2016
def.txt                   errorstatus2.txt          Sat Nov 12 03:00:09 2016

Solution 4

For completeness, you can do this with sed too:

sed -e '1iFilename\tStatus\tTimestamp' -e 'N;N;y/\n/\t/' file.txt

• 1iFilename\tStatus\tTimestamp inserts the header line before line 1
• N;N reads two more lines into the pattern buffer, giving a total of 3 newline separated lines
• y/\n/\t/ replaces all newlines with tabs in the pattern buffer

The i, N and y sed commands are documented here.

Solution 5

It's always possible to cook up something for text processing with AWK or Perl, and of course Python, which is what this answer provides.

As one-liner:

python -c 'import sys;print "Filename\tStatus\tTimestamp"; lines=[l.strip() for l in sys.stdin];print "".join([l+"\n" if i%3 == 0 else l+"\t" for i,l in enumerate(lines,1) ])' < input.txt

As multi-line script

import sys
print "Filename\tStatus\tTimestamp"
lines=[l.strip() for l in sys.stdin]
print "".join([l+"\n" if i%3 == 0 else l+"\t" for i,l in enumerate(lines,1) ])

Basic idea here is to give the script input via stdin ( using shell's redirection <, although a pipe can be used as well). The script uses tabs to separate the fields, although spaces could be used as well for a more "fine-tuned" output.

Sample output using input example provided by OP:

$ python -c 'import sys;print "Filename\tStatus\tTimestamp";                                   
> lines=[l.strip() for l in sys.stdin];
> print "".join([l+"\n" if i%3 == 0 else l+"\t" for i,l in enumerate(lines,1) ])' < input.txt
Filename    Status  Timestamp
abc.txt errorstatus1    Fri Nov 11 02:00:09 2016
def.txt errorstatus2    Sat Nov 12 03:00:09 2016

View more solutions

Comments

[AlexP]

The example format is not a CSV file, it is a fixed-column-width file. You may want to clarify the question or provide a correct example.

[Sergiy Kolodyazhnyy]

Your example is not a CVS format. CVS format is abc.txt,errorstatus1,Fri Nov 11 02:00:09 2016. I edited your question to match what it says with the example you provided. Feel free to roll back, but please note that you really need to clarify what exactly do you want - columnated values or comma-separated values

[linux09]

Cool! Thanks!! But how do print these values in excel format. I want the Column headers in the excel sheet as Filename Status and Timestamp and the values below them

[heemayl]

@linux09 Create a CSV and import with Excel: echo Filename,Status,Timestamp; paste -d ',' - - - <file.txt

[linux09]

Brilliant! Works like a charm. Thank you very much

[Fidel Pérez Menéndez]

Great.. Could you also briefly comment on the expressions you used? thx!

[Fidel Pérez Menéndez]

yes, perfect dude