Formatting a report from 2 files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Formatting a report from 2 files
# 1  
Old 12-13-2006
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 number*Trans ID*Posting Date*Currency*Amount
BR1*2006*1000*00002*01/03/2006*USD*36.65
BR5*2006*1005*00001*01/04/2006*USD*2000.00
BR1*2006*1000*00005*01/14/2006*USD*150.00
...

How will I obtain a Gl numberwise control break report combining data from these 2 files.
--------------------------------------------------
Year: 2006 GL number: 1000 GL name : Interest Income
Opening Balance: 100

Posting date Currency TransID Amount
01/03/2006 USD 00002 36.65
01/14/2006 USD 00005 150.00

Total 286.65

Year: 2006 GL number: 1005 GL name : Rental Income
Opening Balance : 0.00
Posting date Currency TransID Amount
01/04/2006 USD 00001 2000.00

Total 2000.00

...

I would appreciate any point to resolve this. It is not a h/w assignment. Thanks in advance.
# 2  
Old 12-14-2006
#! /usr/bin/ksh

rm result1
while read line
do
gl=`echo $line | cut -d\* -f1`
obal=`echo $line | cut -d\* -f4`
echo "Opening Balance : $obal" >> result1
cat file2 |awk -F* '
{if($3 == g){totbal = totbal + $7}
{print $5 " " $6" " g " "$7}
}END{print "Total Balance : " totbal"\n" }' g="$gl" totbal="$obal" >>result1
done < file1
# 3  
Old 12-14-2006
formatting control break reports from 2 files

Aju,

Thank you very much. The output is attached based on your script. But I see just one problem. I require an output only when there is a matching GL number in the transactions file. If not that GL number should be skipped.

$ more file1
1000*Interest Income*2006*100.00
1005*Rental Income*2006*0.00

$ more file2
BR1*2006*1000*00002*01/03/2006*USD*36.65
BR5*2006*1005*00001*01/04/2006*USD*2000.00
BR1*2006*1000*00005*01/14/2006*USD*150.00

$ more result1
Opening Balance : 100.00
01/03/2006 USD 1000 36.65
01/04/2006 USD 1000 2000.00 (should not be here as it is GL 1005, but total is correct)
01/14/2006 USD 1000 150.00
Total Balance : 286.65 (total is correct)

Opening Balance : 0.00
01/03/2006 USD 1005 36.65 (should not be there as it is GL 1000)
01/04/2006 USD 1005 2000.00
01/14/2006 USD 1005 150.00 (should not be there as it is GL 1000)
Total Balance : 2000 (total is correct)

How would I modify the script. Thanks.
# 4  
Old 12-15-2006
#! /usr/bin/ksh

rm result1
while read line
do
gl=`echo $line | cut -d\* -f1`
obal=`echo $line | cut -d\* -f4`
#echo "Opening Balance : $obal" >> result1
cat file2 |awk -F* 'BEGIN{opbl=0}
{if($3 == g){
if(opbl == 0) {
print "Opening Balance :" totbal
opbl=1
}
totbal = totbal + $7
{print $5 " " $6" " g " "$7}
}
}END{print "Total Balance : " totbal"\n" }' g="$gl" totbal="$obal" >>result1
done < file1
# 5  
Old 12-15-2006
I am still getting the output in the following manner:

$ more result1
Opening Balance : 100.00
01/03/2006 USD 1000 36.65
01/14/2006 USD 1000 150.00
01/04/2006 USD 1000 2000.00 (should not be printed as the GL is different)
Total Balance : 286.65

Opening Balance : 0.00
01/03/2006 USD 1005 36.65 (should not be printed as the GL is different)
01/14/2006 USD 1005 150.00 (should not be printed as the GL is different)
01/04/2006 USD 1005 2000.00
Total Balance : 2000

It looks like it is not doing a key match or am I doing something wrong.

The following is the script used:
------------------------------
rm result1
while read line
do
gl=`echo $line | cut -d\* -f1`
obal=`echo $line | cut -d\* -f4`
#echo "Opening Balance : $obal" >> result1
cat file2 |awk -F* 'BEGIN{opbl=0}
{if($3 == g){
if(opbl == 0) {
print "Opening Balance :" totbal
opbl=1
}
totbal = totbal + $7
{print $5 " " $6" " g " "$7}
}
}END{print "Total Balance : " totbal"\n" }' g="$gl" totbal="$obal" >>result1
done < file1
# 6  
Old 12-15-2006
nawk -f aug.awk myGL.txt myTRAN.txt

aug.awk:
Code:
BEGIN {
  FS="*"
  format="%-15s %-10s %-10s %10s\n"
}

FNR == 1 { next }
FNR == NR {
   glA[$1]=$3
   glA_glname[$1]=$2
   glA_open[$1]=$4
   next
}

{
   str=sprintf(format, $5, $6, $4, $7)
   tranA[$3]= ( $3 in tranA) ? tranA[$3] str : str;
   tranA_total[$3]+=$7
}
END {
  for (i in glA) {
     printf("Year: %s GL number: %s GL name: %s\n", glA[i], i, glA_glname[i])
     printf("Opening Balance: %s\n\n", glA_open[i])

     printf(format, "Posting date", "Currency", "TransID", "Amount")
     printf("%s", tranA[i])
     printf("\nTotal %.2f\n\n", tranA_total[i] + glA_open[i])
  }
}


Last edited by vgersh99; 12-15-2006 at 04:34 PM..
# 7  
Old 12-15-2006
Thank you. As I did not have nawk, I ran the following at the HP UNIX prompt with awk. file1 is GL and file2 is transactions. It gives only the output for GL 1005. The output for GL 1000 is missing. Should I make any change to the code?

command and the output
------------------------
$ awk -f aug.awk file1 file2 > file3
$ more file3
Year: 2006 GL number: 1005 GL name: Rental Income
Opening Balance: 0.00

Posting date Currency TransID Amount
01/04/2006 USD 00001 2000.00

Total 2000.00
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Formatting The Output Files & Matching Keys

I have the following 2 output files, one contain the standard output after i decrypt the encrypted file and another keys listed from the gpg trust db, Provider File: gpg: encrypted with 2048-bit RSA key, ID 96301328, created 2014-04-29 "JKL <400@abc.com>" gpg: encrypted with 2048-bit... (2 Replies)
Discussion started by: Ariean
2 Replies

2. Shell Programming and Scripting

search for content in files. Name of files is in another file. Format as report.

Hi I have multiple files in a folder and one file which contains a list of files (one on each line). I was to search for a string only within these files and not the whole folder. I need the output to be in the form File1<tab>string instance 2<tab> string instance 2<tab>string instance 3... (6 Replies)
Discussion started by: pkabali
6 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. Shell Programming and Scripting

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: # AA.INDEX 2 11 2 239 52 (7,2) 07 MAY 11 203.1 55 # ACCOUNT 2 89561 2 ... (4 Replies)
Discussion started by: thaller
4 Replies

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

6. Shell Programming and Scripting

Reading files under a folder and formatting content of each file

I have 'n' number of files in a folder .each file in the folder "myfolder" is having the content like. COLNAME ------------ AAAAAA BBBBBB CCCCCC DDDDDD ... ... ... ZZZZZZ 26 recrod(s) selected. My request is by reading each file in "myfolder" and format each file such a way... (18 Replies)
Discussion started by: rocking77
18 Replies

7. Shell Programming and Scripting

Help needed in formatting script files

Hi, Can anyone tell me how i can convert all tab spaces inside a script to 4 spaces through another script. Also i need to find if all the quotes are matching and ended properly. Any idea whould be of great help. Many thanks! (3 Replies)
Discussion started by: justchill
3 Replies

8. Shell Programming and Scripting

formatting files

how do i add the directory name to multiple flies and pad numbers sequences /bin/bin2/bin3/bin4/bin5/foo_bar_vX/foobar ie i have a pic in the foobar folder call dsc.1 id like to change it to foobar_dsc.0001 (2 Replies)
Discussion started by: thehive
2 Replies

9. Shell Programming and Scripting

formatting and merging 2 data files

Hi, I have 2 files that I got as an output from another program. They are : File 1 ((((((CtBJa:197.0,CtBTz:197.0):85.0,CtAHr:197.0):116.0,CtDUw:197.0):176.0,CtSwe:197.0):110.0, (CtL2b:197.0,Ct4Bu:197.0):196.0):197.0,CmuNg:197.0);... (5 Replies)
Discussion started by: Lucky Ali
5 Replies

10. Shell Programming and Scripting

Formatting files

We currently use the following script to format a non formatted file to an 80 byte format (for grep purposes etc..) We need some assistance with converting the file back to a non formatted file with no carriage returns ( we want the file to be a continuous line) #!/usr/local/bin/perl ($fn,... (1 Reply)
Discussion started by: rivera
1 Replies
Login or Register to Ask a Question