How to format columns using AWK

ora_umair · July 24, 2009, 6:44pm

Hi,

How to format something like this:

John Roberts 324-141-984 Acct
Jack Williams 159-555-555 Acct
William Jackson  949-911-888 Acct
Mark J Walton 145-852-252            Acct
Fred P Milton 483-244-390                Acct
Bill P J Miller                 404-050-223  Acct

into this: Using AWK (Equal distance between columns)

John Roberts                324-141-984        Acct
Jack Williams                159-555-555        Acct
William Jackson             949-911-888        Acct
Mark J Walton              145-852-252        Acct
Fred P Milton               483-244-390        Acct
Bill P J Miller                404-050-223        Acct

As you can see the names are 2,3,4 fields long.

Here is my code but it is not working because the Names column is messing the other columns:

if (NF >= 6) {
     printf ("\n%s %s %s %-10s %s\t\t %s", $1, $2, $3, $4, $5, $6)
            }
else
        if (NF == 5) {
     printf ("\n%s %s %-25s %-15s\t %s", $1, $2, $3, $4, $5)
            }
else {
printf ("\n%s %s\t\t\t  %s\t\t %s\t", $1, $2, $3, $4)
     }
}
END { printf("\n")}

Please assist ... Thanks in advance

Scott · July 24, 2009, 6:49pm

Hi.

Where you have %s, change it to %20s (or another number) (where %20s is the width, padded with spaces to the right, or %-20s to the left).

You did this once already in a couple of column as I can see.

ora_umair · July 24, 2009, 6:57pm

Thanks Scottn!

There are multiple names that are 2,3,4 fields long. Every name is different length.

When I pad with spaces I can fix one line, but the some other line doesn't align and so on.

Is there a way to make the whole name a variable or something and align it from right column to left column.

Sorry ... I have tried it multiple ways but still can not align all columns...

Scott · July 25, 2009, 9:36am

Sorry, yes I should have seen that.

It's a bit tricky if you don't know how many fields there are and you use whitespace (spaces and tabs) to separate them (unless you use 1 tab to separate the fields, in which cast you could say:

awk -F"\t" ....

Otherwise, exactly four is easy:

printf( "%-30s %16s %s\n", $1 " " $2, $3, $4 )

Exactly five is easy:

printf( "%-30s %16s %s\n", $1 " " $2 " " $3, $4, $5 )

I would suggest changing your input file to:

John Roberts|324-141-984|Acct
Jack Williams|159-555-555|Acct
William Jackson|949-911-888|Acct
Mark J Walton|145-852-252|Acct
Fred P Milton|483-244-390|Acct
Bill P J Miller|404-050-223|Acct

and then use

awk -F"|" ....

That way the full name is always $1, the number $2 and Acct $3

danmero · July 25, 2009, 10:37am

ora_umair:

into this: Using AWK (Equal distance between columns)

John Roberts                324-141-984        Acct
Jack Williams                159-555-555        Acct
William Jackson             949-911-888        Acct
Mark J Walton              145-852-252        Acct
Fred P Milton               483-244-390        Acct
Bill P J Miller                404-050-223        Acct

You have to think backward

awk '{a=$(NF-1);b=$NF;$(NF-1)=$NF="";printf "%s%20s%10s\n",$0,a,b}' file

or

awk '{a=$(NF-1);b=$NF;$(NF-1)=$NF="";printf "%s%+*s%5s\n",$0,30 -length($0),a,b}' file

edidataguy · July 25, 2009, 11:38am

If you think the number of fields may be more then give that example.
You have to know the data structure and the fields.
I cannot give a solution for some thing I dont know about.

I can change you data to this.

John Roberts|324-141-984|Acct
Jack Williams|159-555-555|Acct
William Jackson|949-911-888|Acct
Mark J Walton|145-852-252|Acct
Fred P Milton|483-244-390|Acct
Bill P J Miller|404-050-223|Acct

But will it be of any help to you?

ora_umair · July 25, 2009, 12:31pm

edidataguy,

Please tell me how to change the data into "John Roberts|324-141-984|Acct" format. After that I will try to use my script to format.

Thanks!

edidataguy · July 25, 2009, 12:35pm

xx='Mark J Walton 145-852-252            Acct'
echo $xx  | sed 's/ \([[:digit:]]\)/|\1/; s/\([[:digit:]]\) /\1|/'

Franklin52 · July 25, 2009, 12:38pm

What's wrong with the solutions of danmero?

edidataguy · July 25, 2009, 12:53pm

I dont see any thing wrong with Danmero's solution. I fact I think it might be more flexible and goingforth that might give the solution than mine.
Right now itisnotthebest.
Try this:

xx='Mark J Walton 145-852-252            Acct Department'
echo $xx  | awk '{a=$(NF-1);b=$NF;$(NF-1)=$NF="";printf "%s%20s%10s\n",$0,a,b}'

Output:

Mark J Walton 145-852-252                  AcctDepartment

And this:

xx='Mark J Walton 145-852-252            Acct Department'
echo $xx  | sed 's/ \([[:digit:]]\)/|\1/; s/\([[:digit:]]\) /\1|/'

output:

Mark J Walton|145-852-252|Acct Department

ora_umair · July 25, 2009, 1:14pm

Hi, All -

Thanks for all your help. I just wanted to see the creative use of SED in this situation.

Thanks again for all your help. I have learned a lot while solving this issue.

Franklin52 · July 25, 2009, 1:16pm

The 2nd solution of danmero meet the needs of what the OP wants (check the first post). This is what I get:

$ cat file
John Roberts 324-141-984 Acct
Jack Williams 159-555-555 Acct
William Jackson  949-911-888 Acct
Mark J Walton 145-852-252            Acct
Fred P Milton 483-244-390                Acct
Bill P J Miller                 404-050-223  Acct
$
$ awk '{a=$(NF-1);b=$NF;$(NF-1)=$NF="";printf "%s%20s%10s\n",$0,a,b}' file
John Roberts           324-141-984      Acct
Jack Williams           159-555-555      Acct
William Jackson           949-911-888      Acct
Mark J Walton           145-852-252      Acct
Fred P Milton           483-244-390      Acct
Bill P J Miller           404-050-223      Acct

Am I missing something?

edidataguy · July 25, 2009, 3:41pm

No, I dont think u r missing anything.
I posted my code because "ora_umair" asked for it.