Is there any way to convert paragraphs to columns in shell script to format it in Excel?

Viewed 126

We have a requirement to format data, given in list format, into a CSV file

Example input:

ORG MANAGER
  No ORG MANAGER found

BILLING MANAGER
  No BILLING MANAGER found

ORG AUDITOR
 xxx
 yyy
 zzz
 aaa
 bbb
 ccc

Example output:

ORG MANAGER               BILLING MANAGER               ORG AUDITOR
No ORG MANAGER found      No BILLING MANAGER found      xxx
                                                        yyy
                                                        zzz
                                                        aaa
                                                        bbb
                                                        ccc

I did split the every paragraph and kept it in separate files and tried printing them using paste -d '\t\t command like below:

paste -d "\t\t\t" file1 file2 file3 > fin.csv

After this I tried formatting the output using below command:

awk '{ $NF = "\t" $NF; print }' fin.csv | column -t -s $'\t'

But the output is not like what I have expected.

paste -d "\t\t\t" file1 file2 file3 > fin.csv  --> to print the files side by side 

awk '{ $NF = "\t" $NF; print }' fin.csv | column -t -s $'\t' --> to format

Expecting to print every paragraph in separate columns such that I can keep them in Excel to format.

I am adding the expected input and output format in the attached snap for the clear understanding.

3 Answers

It is easily done with awk,

awk 'BEGIN{RS=""; FS="\n"; OFS=","; ORS="\n"}
     { for (i=1;i<=NF;++i) {c[FNR,i]=$i; sub(/^[[:blank:]]*/,"",c[FNR,i])} }
     { nf_max= (NF>nf_max?NF:nf_max) }
     END{ for(j=1;j<=nf_max;++j) {
             for(i=1;i<=FNR;++i) { printf ("%s" (i==FNR?ORS:OFS)), c[i,j] }
          }
     }' file

This will output a CSV of the following format:

ORG MANAGER,BILLING MANAGER,ORG AUDITOR
No ORG MANAGER found,No BILLING MANAGER found,xxx
,,yyy
,,zzz
,,aaa
,,bbb
,,ccc

How does this work?

  • By telling awk to set the record separator RS to an empty string, we define each record to be a block of text separated by an empty line.
  • Each field in that record is separated by a newline character.
  • We store each field in an array which is indexed by record number FNR and field number. This way we can fully reconstruct the CSV file.
  • Since you want a CSV file, we set the output field separator OFS to be a <comma> character and the output record separator, which are now lines, to be a <newline>-character.
  • We keep track of the maximum number of fields per record, which indicates the maximum number of rows in the CSV file.
  • If a field has less then the maximum number of fields, we can still request that field content from our array as awk, by default, puts string values to empty.

Your question initially asked for a CSV file, but you requested a TSV file which is aligned. We could expand the above awk command for this, but it would be easier to just parse the full output with the column command:

$ awk ... file | column -s, -o $'\t' -t
ORG MANAGER             BILLING MANAGER                 ORG AUDITOR
No ORG MANAGER found    No BILLING MANAGER found        xxx
                                                        yyy
                                                        zzz
                                                        aaa
                                                        bbb
                                                        ccc

You can use cat to expect that the fields are adjusted with spaces to match the width and only a single tab is inserted between the fields:

$ awk ... file | column -s, -o $'\t' -t | cat -vET
ORG MANAGER         ^IBILLING MANAGER         ^IORG AUDITOR$
No ORG MANAGER found^INo BILLING MANAGER found^Ixxx$
                    ^I                        ^Iyyy$
                    ^I                        ^Izzz$
                    ^I                        ^Iaaa$
                    ^I                        ^Ibbb$
                    ^I                        ^Iccc$

To get output you can import into Excel as a row of cells would be:

$ awk -v RS= '{gsub(/\n +/,"\n"); printf "%s\"%s\"", s, $0; s=","} END{print ""}' file
"ORG MANAGER
No ORG MANAGER found","BILLING MANAGER
No BILLING MANAGER found","ORG AUDITOR
xxx
yyy
zzz
aaa
bbb
ccc"

Save the output in a file "foo.csv", double-click on it in Windows and it'll be displayed as you want in Excel.

To get the output you asked for visually would be:

$ cat tst.awk
BEGIN { numCols=1; OFS="\t" }
NF {
    sub(/^[[:space:]]+/,"")
    vals[++rowNr,numCols] = $0
    wid[numCols] = (wid[numCols] > length() ? wid[numCols] : length())
    numRows = (numRows > rowNr ? numRows : rowNr)
    next
}
{ numCols++; rowNr=0 }
END {
    for (rowNr=1; rowNr<=numRows; rowNr++) {
        for (colNr=1; colNr<=numCols; colNr++) {
            printf "%-*s%s", wid[colNr], vals[rowNr,colNr], (colNr<numCols ? OFS : ORS)
        }
    }
}

$ awk -f tst.awk file
ORG MANAGER             BILLING MANAGER                 ORG AUDITOR
No ORG MANAGER found    No BILLING MANAGER found        xxx
                                                        yyy
                                                        zzz
                                                        aaa
                                                        bbb
                                                        ccc

Here is another awk script.

/^[[:space:]]*$/{  # column separator
    maxRow = (rowCount > maxRow) ? rowCount : maxRow; # find maxRows
    rowCount = 0; # reset rows count
    columnCount++; # increment columns count
    next;         # skip inclusion in cells
}
{  cells[(columnCount + 1)","++rowCount] = $0; } # read each input row as cell
END {
    maxRow = (rowCount > maxRow) ? rowCount : maxRow; # ind maxRows (including last column)
    columnCount++; # increment last column read (suming no new line termination)
    for (row = 1; row <= maxRow; row++) {  # print out each row
        printf("%s", cells[1","row]);     # print out the first element in row
        for (col = 2; col <= columnCount; col++) {
            printf("\t%s", cells[col","row]); # print , delimiter for each element in row
        }
        printf("\n");                    # terminate each row with newline
    }
}

The output is tab delimited csv:

ORG MANAGER     BILLING MANAGER ORG AUDITOR
  No ORG MANAGER found    No BILLING MANAGER found       xxx
                 yyy
                 zzz
                 aaa
                 bbb
                 ccc

You can add as many columns as required.

execution command is:

 awk -f script.awk input.txt > output.csv

To edit with micorsoft-excell or libreOffice-cals. Open a new speardsheet.

Import the data from output.csv using data tools. The output.csv data is tab delimited.

good luck.

Related