Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Formatting a report using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Formatting a report using awk
# 1  
Old 12-21-2011
Formatting a report using awk
Our vendor produces a report that I would like to format in a particular way.

Here is the sample output from their report:

Code:
# AA.INDEX                       2    11     2      239        52        (7,2)           07 MAY 11        203.1        55
# ACCOUNT                        2 89561     2  1103300     214.9    (60793,4)           07 MAY 11        182.5       129
# ACCT.ID.XREF                   2  4643     1      496        19       (19,1)           13 MAY 11          0.7         0
# ACH.ARCHIVE.FILE               2 26591     4   677153     208.5    (36241,4)           07 MAY 11         11.0       129
# ACH.BATCH.ARCHIVE              2177533     2   435120       158    (32771,2)           07 MAY 11         13.3        18
# ACH.BATCH.FILE                 2  1361     2    30193     152.6     (2221,2)           07 MAY 11         12.9       165
# ACH.CLEAN.ARCHIVE.WK           2   503     1   435120      18.8     (13367,1)           07 MAY 11         30.4      1588

As you can see on line 5, fields 3 & 4 are running into each other. This is due to the fields being fixed width and the value being to large (I'm assuming). I do not actually know the field widths, but could probably figure it out (by counting) if needed.

What I need is to ensure that fields 3 & 4 stay separated so I can perform some calculations with them (ie. $3 * $4). Filed 3 should always be a single digit. So "if length field 3 > 1 then split field 3 into fields 3 & 4 after the first digit in field 3".

Any ideas?

Thanks,
# 2  
Old 12-21-2011
The easiest way is inserting space after 3rd column character in all lines. It assumes fixed width fields:
Code:
awk '{print substr($0,1,34),substr($0,35)}' file

# 3  
Old 12-21-2011
Maybe you should contact your vendor : adding a space will not tell you wether the field has been truncated or not.

I think this is up to your vendor to ensure that the generated report that is sent to you does not contain truncated values.

Of course, if you are sure that those fields never contain truncated values, then, indeed, you can separate them as per Bartus suggestion.
# 4  
Old 12-21-2011
Quote:
Originally Posted by bartus11
The easiest way is inserting space after 3rd column character in all lines. It assumes fixed width fields:
Code:
awk '{print substr($0,1,34),substr($0,35)}' file

Wouldn't that cause the lines without the issue to be double spaced?

Could I use something like this:

Code:
awk '{if (length($3) > 1)<more code>;}' file.txt

---------- Post updated at 01:53 PM ---------- Previous update was at 01:51 PM ----------

Quote:
Originally Posted by ctsgnb
Maybe you should contact your vendor : adding a space will not tell you wether the field has been truncated or not.

I think this is up to your vendor to ensure that the generated report that is sent to you does not contain truncated values.

Of course, if you are sure that those fields never contain truncated values, then, indeed, you can separate them as per Bartus suggestion.
I'd agree with you normally, but they are a real pain, and turn around time would be lengthy. The actual end purpose would still work out, (dose not need to be exact) the first few place values would suffice.
# 5  
Old 12-21-2011
Quote:
Originally Posted by thaller
Wouldn't that cause the lines without the issue to be double spaced?
It will move lines without issues as well, but this way the alignment of the columns will be saved. If you inserted the space only on faulty line, then all the columns after that insertion will be out of line compared to the unchanged lines. Check that output (I've shortened your sample input lines a bit):
Code:
[root@linux ~]# awk '{print substr($0,1,34),substr($0,35)}' a
# AA.INDEX                       2     11     2      239        52        (7,2)           07
# ACCOUNT                        2  89561     2  1103300     214.9    (60793,4)           07
# ACCT.ID.XREF                   2   4643     1      496        19       (19,1)           13
# ACH.ARCHIVE.FILE               2  26591     4   677153     208.5    (36241,4)           07
# ACH.BATCH.ARCHIVE              2 177533     2   435120       158    (32771,2)           07
# ACH.BATCH.FILE                 2   1361     2    30193     152.6     (2221,2)           07
# ACH.CLEAN.ARCHIVE.WK           2    503     1   435120      18.8     (13367,1)          09
[root@linux ~]# 
[root@linux ~]# awk 'length($3)>1{$0=substr($0,1,34)" "substr($0,35)}1' a
# AA.INDEX                       2    11     2      239        52        (7,2)           07
# ACCOUNT                        2 89561     2  1103300     214.9    (60793,4)           07
# ACCT.ID.XREF                   2  4643     1      496        19       (19,1)           13
# ACH.ARCHIVE.FILE               2 26591     4   677153     208.5    (36241,4)           07
# ACH.BATCH.ARCHIVE              2 177533     2   435120       158    (32771,2)           07
# ACH.BATCH.FILE                 2  1361     2    30193     152.6     (2221,2)           07
# ACH.CLEAN.ARCHIVE.WK           2   503     1   435120      18.8     (13367,1)          09

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Suffix formatting with awk

i would like to format the 9 character with suffix as "0". i tried below it doesn't work. >a=12345 > echo $a | awk '{printf "%-09s\n",$1}' >12345 required output is 123450000 can you guys help me out ? (7 Replies)
Discussion started by: expert
7 Replies

2. Shell Programming and Scripting

awk : finding and formatting

Hi , I was trying to find files of a particular date and did that but then I also wanted to format a field based on some condition so had put another if else in awk. Now it is getting the files of particular date or also the files which are matching that if else condition. find . -name "*"... (1 Reply)
Discussion started by: abhi1988sri
1 Replies

3. Shell Programming and Scripting

Formatting Report and Reading data and fetching the details from contents file

Data I was trying to write shell script which will be return the output in the below format First i was trying to do these using sed. sed -n '/.ksh/p' mainksh.ksh sed -e 's/*\(.*\)/\1/g' mainksh.ksh $RUN_DIR, $SUB_DIR and the variables which will be defined in the profile file. when i am... (0 Replies)
Discussion started by: rameshds
0 Replies

4. UNIX for Dummies Questions & Answers

awk formatting

Hi all, I'm writing a simple awk code: awk 'BEGIN {FS="|"};{print "Type\tNumber\ttypes\tTotal";};{print $1, "\t", $2, "\t", $3, "\t", $4, "\t";}' db_query.txt it gives me the result: Type Number types Total XXX 498.0 5100.0 5274.661 Type Number types Total... (7 Replies)
Discussion started by: messi777
7 Replies

5. Shell Programming and Scripting

Formatting of output from awk

Hi; i m running an command like; getfacl $(find /home/fstl/testShell/ -type f -ls | awk '{print $11}') and i m getting outpiut for all files in "testShell" folder as; # file: home/fstl/testShell/.script1.sh.swp # owner: root # group: root user:fstl:rw- user:ajay:rw- group:root:r--... (1 Reply)
Discussion started by: ajaypadvi
1 Replies

6. Shell Programming and Scripting

formatting awk

when i try this awk its giving out put as below. awk '!(/^$/||/--/||/selected/||/^ *$/){print $1}' tmp.txt output ===== 1 2010-08-03-12.31.26.126000 how excluede the 1st line ? i mean i want output only 2nd line i.e 2010-08-03-12.31.26.126000; (5 Replies)
Discussion started by: rocking77
5 Replies

7. Shell Programming and Scripting

AWK formatting help.

Dear all I require help with AWK regarding this situation Input is : fn1 12345 fn1 23456 fn3 231513 fn1 22325 fn3 123125 Desired output is fn1 12345 23456 22325 fn3 231513 123125 (5 Replies)
Discussion started by: Peasant
5 Replies

8. UNIX for Dummies Questions & Answers

SQL PLUS report formatting

Hi I am fetcthing the data from the oracle database using SQLPLUS. Here is my script #!/bin/ksh echo `sqlplus -s <<EOF set feedback off set linesize 5000 set pages 0 set space 0 set echo off set trimspool on set colsep '|' SELECT col1 , col2... (4 Replies)
Discussion started by: max_hammer
4 Replies

9. Shell Programming and Scripting

Formatting a report from 2 files

I have 2 files with sample data enclosed. 1. GL (already sorted in ascending order by Gl number) Gl number*glname*Year*opening balance 1000*Interest Income*2006*100.00 1005*Rental Income*2006*0.00 ... 2. Transactions (file is not sorted on GL number or any other field) Branch*Year*Gl... (12 Replies)
Discussion started by: augustinep
12 Replies

10. Shell Programming and Scripting

Formatting using awk

Let's say I write a simple script that contains the following: date | awk '{print $1}' date | awk '{print $2}' Of course, when I run the script the output will look similar to: Tue Mar What if I want my ouput to be on one line as follows: Tue Mar What changes would I need to... (2 Replies)
Discussion started by: cdunavent
2 Replies
Login or Register to Ask a Question