Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

[Solved] Need help formatting a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting [Solved] Need help formatting a file
# 1  
Old 08-22-2012
[Solved] Need help formatting a file
I have a report similar to the below:
Code:
^L"0.1","Run Date  : 19/11/10                      Navneet Bank, N.A.                                                            PAGE NO : 1"
"0.2",Proc Date : 19/11/10                       GLOBAL A/C SYSTEM                                                                    INDDE10
"0.3",                                                MADH CASH BACKFEED DETAILS                                                      PROC UNIT : 273

"0.4","PORTFOLIO ID","OBLIGATION NUMBER","SEQUENCE NUMBER","LENDER TYPE","UCN NUMBER","OBLIGOR NUMBER","CUSTOMER NAME","ADDR STREET 1","ADDR STREET 2   ","ADDR STREET 3   ","ADDR
 STREET 4   ","CITY NAME       ","STATE           ","POSTAL/ZIP CODE ","COUNTRY CODE    ","LOCAL CUSTOMER NUMBER","GTS REFERENCE #","GES FACILITY ID","G/L UNIT","COST CENTER (EAC
)","LOCAL COST CENTER","PRODUCT GROUP","PRODUCT TYPE","CASH CURRENCY CODE","GROSS PRINCIPAL AMOUNT","NET PRINCIPAL AMOUNT","CASH EFFECTIVE DATE","CASH MATURITY DATE","INTEREST RA
TE TYPE","BASE RATE .","RESVE RATE ","SPREAD RATE","COST/FUNDS","YEAR BASIS (ACCRUAL BASIS)","BALANCE CATEGORY","ACCRUAL CATEGORY","RATE CALCULATION METHOD","INTEREST START DATE"
,"INTEREST END DATE","SCHEDULE CURRENCY CODE","ITD ACCRUED/CHARGED","ITD PAID AMOUNT","MTD ACCRUED/CHARGED","INTEREST TO DUE DATE","COST OF FUNDS TO DUE DATE","ITD COST OF FUNDS"
,"MTD COST OF FUNDS","DAILY ACCRUAL AMOUNT","DAILY ACCRUAL COF AMOUNT","NON-PERFORMANCE INDICATOR","NON-PERFORMANCE DATE","PAST DUE INDICATOR","PRINCIPAL AMOUNT PAST DUE","INTERE
ST AMOUNT PAST DUE","PRINCIPAL PAST DUE DATE","INTEREST PAST DUE DATE","NUM DAYS PAST DUE (PRINCIPAL)","NUM DAYS PAST DUE (INTEREST)","NL NUMBER","PROC UNIT (PU)","REPORTING UNIT
 (RU)","RATE INDEX","ITD EARNED TO GENERAL LEDGER","MTD GL EARNINGS","CURRENT DISCOUNT",
"        1","SA","541783836","1","600","210202412000","41123852","HSBC BANK PLC","CAN DOLLY","LONDON","E14 5HQ"," "," "," "," ","GBR","0007426646","GGGF-700006","198348009","RIYADH","119937","000041","TR","347","USD","                  1.00","                  1.00","11/19/2010","12/02/2010","LIB","     1.5000000","     0.0000000","     1.0000000","   1.5000000","5","INT","INT01","A","11/19/2010","12/02/2010","USD","                  0.00","                  0.00","                  0.00","                  0.00","         0.00","                  0.00","                  0.00","                  0.00","                  0.00","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","273","00000"," ","                  0.00","                  0.00","                  0.00",
"        2","SA","541783854","1","600","7593304000","41123868","EMIRATES NBD BANK PJSC","MELLISA","DUBAI","UNITED ARAB EMIRATE"," "," "," "," ","ARE","0002730836","GGGF-700005","198438610","RIYADH","119937","000041","TR","347","USD","                  1.00","                  1.00","11/19/2010","12/02/2010","LIB","     1.5000000","     0.0000000","     1.0000000","     1.5000000","5","INT","INT01","A","11/19/2010","12/02/2010","USD","                  0.00","                  0.00","                  0.00","                  0.00","                  0.00","                  0.00","                  0.00","                  0.00","                  0.00","N",""," ","                  0.00","   0.00","01/01/1900","01/01/1900","0","0"," ","273","00000"," ","                  0.00","                  0.00","                  0.00",
                                                          * * * END OF REPORT * * *
^L"0.1","Run Date  : 17/11/10                      Navneet Bank, N.A.                                                            PAGE NO : 1"
"0.2",Proc Date : 17/11/10                       GLOBAL A/C SYSTEM                                                                    INDDE10
"0.3",                                                MADH CASH BACKFEED DETAILS                                                      PROC UNIT : 273

"0.4","PORTFOLIO ID","OBLIGATION NUMBER","SEQUENCE NUMBER","LENDER TYPE","UCN NUMBER","OBLIGOR NUMBER","CUSTOMER NAME","ADDR STREET 1","ADDR STREET 2   ","ADDR STREET 3   ","ADDR
 STREET 4   ","CITY NAME       ","STATE           ","POSTAL/ZIP CODE ","COUNTRY CODE    ","LOCAL CUSTOMER NUMBER","GTS REFERENCE #","GES FACILITY ID","G/L UNIT","COST CENTER (EAC
)","LOCAL COST CENTER","PRODUCT GROUP","PRODUCT TYPE","CASH CURRENCY CODE","GROSS PRINCIPAL AMOUNT","NET PRINCIPAL AMOUNT","CASH EFFECTIVE DATE","CASH MATURITY DATE","INTEREST RA
TE TYPE","BASE RATE .","RESVE RATE ","SPREAD RATE","COST/FUNDS","YEAR BASIS (ACCRUAL BASIS)","BALANCE CATEGORY","ACCRUAL CATEGORY","RATE CALCULATION METHOD","INTEREST START DATE"
,"INTEREST END DATE","SCHEDULE CURRENCY CODE","ITD ACCRUED/CHARGED","ITD PAID AMOUNT","MTD ACCRUED/CHARGED","INTEREST TO DUE DATE","COST OF FUNDS TO DUE DATE","ITD COST OF FUNDS"
,"MTD COST OF FUNDS","DAILY ACCRUAL AMOUNT","DAILY ACCRUAL COF AMOUNT","NON-PERFORMANCE INDICATOR","NON-PERFORMANCE DATE","PAST DUE INDICATOR","PRINCIPAL AMOUNT PAST DUE","INTERE
ST AMOUNT PAST DUE","PRINCIPAL PAST DUE DATE","INTEREST PAST DUE DATE","NUM DAYS PAST DUE (PRINCIPAL)","NUM DAYS PAST DUE (INTEREST)","NL NUMBER","PROC UNIT (PU)","REPORTING UNIT
 (RU)","RATE INDEX","ITD EARNED TO GENERAL LEDGER","MTD GL EARNINGS","CURRENT DISCOUNT",

                                                          * * * END OF REPORT * * *
^L"0.1","Run Date  : 18/11/10                      Navneet Bank, N.A.                                                            PAGE NO : 1"
"0.2",Proc Date : 18/11/10                       GLOBAL A/C SYSTEM                                                                    INDDE10
"0.3",                                                MADH CASH BACKFEED DETAILS                                                      PROC UNIT : 273
 
"0.4","PORTFOLIO ID","OBLIGATION NUMBER","SEQUENCE NUMBER","LENDER TYPE","UCN NUMBER","OBLIGOR NUMBER","CUSTOMER NAME","ADDR STREET 1","ADDR STREET 2   ","ADDR STREET 3   ","ADDR
 STREET 4   ","CITY NAME       ","STATE           ","POSTAL/ZIP CODE ","COUNTRY CODE    ","LOCAL CUSTOMER NUMBER","GTS REFERENCE #","GES FACILITY ID","G/L UNIT","COST CENTER (EAC
)","LOCAL COST CENTER","PRODUCT GROUP","PRODUCT TYPE","CASH CURRENCY CODE","GROSS PRINCIPAL AMOUNT","NET PRINCIPAL AMOUNT","CASH EFFECTIVE DATE","CASH MATURITY DATE","INTEREST RA
TE TYPE","BASE RATE .","RESVE RATE ","SPREAD RATE","COST/FUNDS","YEAR BASIS (ACCRUAL BASIS)","BALANCE CATEGORY","ACCRUAL CATEGORY","RATE CALCULATION METHOD","INTEREST START DATE"
,"INTEREST END DATE","SCHEDULE CURRENCY CODE","ITD ACCRUED/CHARGED","ITD PAID AMOUNT","MTD ACCRUED/CHARGED","INTEREST TO DUE DATE","COST OF FUNDS TO DUE DATE","ITD COST OF FUNDS"
,"MTD COST OF FUNDS","DAILY ACCRUAL AMOUNT","DAILY ACCRUAL COF AMOUNT","NON-PERFORMANCE INDICATOR","NON-PERFORMANCE DATE","PAST DUE INDICATOR","PRINCIPAL AMOUNT PAST DUE","INTERE
ST AMOUNT PAST DUE","PRINCIPAL PAST DUE DATE","INTEREST PAST DUE DATE","NUM DAYS PAST DUE (PRINCIPAL)","NUM DAYS PAST DUE (INTEREST)","NL NUMBER","PROC UNIT (PU)","REPORTING UNIT
 (RU)","RATE INDEX","ITD EARNED TO GENERAL LEDGER","MTD GL EARNINGS","CURRENT DISCOUNT",
"     1","EU","521323651","1","600","687735621000","10711539","MANDA EXCHANGE BK"," "," "," "," ","BROOKLYN"," "," ","KOR","5000106400","ITIR-343514-0003","100029984","NAS","038830","038830","TR","345","USD","             45,186.90","             45,186.90","05/09/2012","11/05/2012","FIX","     0.7284000","     0.0000000","     1.3000000","     0.7284000","5","INT","INT01","A","05/09/2012","11/05/2012","USD","                 40.74","                  0.00","                 40.74","                458.28","                164.57","                 14.63","                 14.63","                  2.55","                  0.91","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                 40.74","                 40.74","                  0.00",
"     2","EU","521323703","1","600","687735670000","60033444","WOORI BANK","#203 HOEHYON DONG 1-GA"," "," "," ","CHUNG-GU"," ","SOUL100792","KOR","5000156512","ITIR-391912-0007","100030027","NAS","038830","038830","TR","345","USD","                860.10","                860.10","05/09/2012","08/28/2012","FIX","     0.5651000","     0.0000000","     1.1000000","     0.5651000","5","INT","INT01","A","05/09/2012","08/28/2012","USD","                  0.64","                  0.00","                  0.64","                  4.41","                  1.50","                  0.22","                  0.22","                  0.04","                  0.01","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                  0.64","                  0.64","                  0.00",
"     3","EU","521323735","1","600","31435000","60031456","INDUSTRIAL BANK OF MANDA"," "," "," "," ","MANDA"," "," ","KOR","5000128297","ITIR-315058-0009","500149320","NAS","038830","038830","TR","345","USD","             57,180.00","             57,180.00","05/09/2012","11/05/2012","FIX","     0.7284000","     0.0000000","     1.1000000","     0.7284000","5","INT","INT01","A","05/09/2012","11/05/2012","USD","                 46.47","                  0.00","                 46.47","                522.74","                208.24","                 18.51","                 18.51","                  2.90","                  1.16","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                 46.47","                 46.47","                  0.00",

               * * * END OF REPORT * * *

Wat i'm looking for is a target file with formatted data
Code:
"1","19/11/10","273","     1","SA","541783836","1","600","210202412000","41123852","HSBC BANK PLC","CAN DOLLY","LONDON","E14 5HQ"," "," "," "," ","GBR","0007426646","GGGF-700006","198348009","RIYADH","119937","000041","TR","347","USD","                  1.00","                  1.00","11/19/2010","12/02/2010","LIB","     1.5000000","     0.0000000","     1.0000000","   1.5000000","5","INT","INT01","A","11/19/2010","12/02/2010","USD","                  0.00","                  0.00","                  0.00","                  0.00","         0.00","                  0.00","                  0.00","                  0.00","                  0.00","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","273","00000"," ","                  0.00","                  0.00","                  0.00",
"1","19/11/10","273","     2","SA","541783854","1","600","7593304000","41123868","EMIRATES NBD BANK PJSC","MELLISA","DUBAI","UNITED ARAB EMIRATE"," "," "," "," ","ARE","0002730836","GGGF-700005","198438610","RIYADH","119937","000041","TR","347
"2","18/11/10","273","     1","EU","521323651","1","600","687735621000","10711539","MANDA EXCHANGE BK"," "," "," "," ","BROOKLYN"," "," ","KOR","5000106400","ITIR-343514-0003","100029984","NAS","038830","038830","TR","345","USD","             45,186.90","             45,186.90","05/09/2012","11/05/2012","FIX","     0.7284000","     0.0000000","     1.3000000","     0.7284000","5","INT","INT01","A","05/09/2012","11/05/2012","USD","                 40.74","                  0.00","                 40.74","                458.28","                164.57","                 14.63","                 14.63","                  2.55","                  0.91","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                 40.74","                 40.74","                  0.00",
"2","18/11/10","273","     2","EU","521323703","1","600","687735670000","60033444","WOORI BANK","#203 HOEHYON DONG 1-GA"," "," "," ","CHUNG-GU"," ","SOUL100792","KOR","5000156512","ITIR-391912-0007","100030027","NAS","038830","038830","TR","345","USD","                860.10","                860.10","05/09/2012","08/28/2012","FIX","     0.5651000","     0.0000000","     1.1000000","     0.5651000","5","INT","INT01","A","05/09/2012","08/28/2012","USD","                  0.64","                  0.00","                  0.64","                  4.41","                  1.50","                  0.22","                  0.22","                  0.04","                  0.01","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                  0.64","                  0.64","                  0.00",
"2","18/11/10","273","     3","EU","521323735","1","600","31435000","60031456","INDUSTRIAL BANK OF MANDA"," "," "," "," ","MANDA"," "," ","KOR","5000128297","ITIR-315058-0009","500149320","NAS","038830","038830","TR","345","USD","             57,180.00","             57,180.00","05/09/2012","11/05/2012","FIX","     0.7284000","     0.0000000","     1.1000000","     0.7284000","5","INT","INT01","A","05/09/2012","11/05/2012","USD","                 46.47","                  0.00","                 46.47","                522.74","                208.24","                 18.51","                 18.51","                  2.90","                  1.16","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                 46.47","                 46.47","                  0.00",

The target file just has the main content without header & footer of the report but a sequence , proc_date & proc_unit appened before it. Can someone please help me achieving this thru unix? Thanks in advance.

You can find the source file in attachment. Below the source file i've mentioned what the target file should look like
file.txt (13.2 KB) 
# 2  
Old 08-22-2012
Between, I don't need the sequence appended (ignore that) .. just the Proc Date : 19/11/10 & PROC UNIT : 273.

In the report, there might be multiple records for a particular date or may be nothing at all.

The example which i gave:
For 19/11/10, we have 2 records.
For 17/11/10, we have 0 records.
For 19/11/10, we have 3 records.

Similarly we can have hundreds of records for a particular day/report.

The target file needed now would be
Code:
"19/11/10","273","     1","SA","541783836","1","600","210202412000","41123852","HSBC BANK PLC","CAN DOLLY","LONDON","E14 5HQ"," "," "," "," ","GBR","0007426646","GGGF-700006","198348009","RIYADH","119937","000041","TR","347","USD","                  1.00","                  1.00","11/19/2010","12/02/2010","LIB","     1.5000000","     0.0000000","     1.0000000","   1.5000000","5","INT","INT01","A","11/19/2010","12/02/2010","USD","                  0.00","                  0.00","                  0.00","                  0.00","         0.00","                  0.00","                  0.00","                  0.00","                  0.00","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","273","00000"," ","                  0.00","                  0.00","                  0.00",
"19/11/10","273","     2","SA","541783854","1","600","7593304000","41123868","EMIRATES NBD BANK PJSC","MELLISA","DUBAI","UNITED ARAB EMIRATE"," "," "," "," ","ARE","0002730836","GGGF-700005","198438610","RIYADH","119937","000041","TR","347
"18/11/10","273","     1","EU","521323651","1","600","687735621000","10711539","MANDA EXCHANGE BK"," "," "," "," ","BROOKLYN"," "," ","KOR","5000106400","ITIR-343514-0003","100029984","NAS","038830","038830","TR","345","USD","             45,186.90","             45,186.90","05/09/2012","11/05/2012","FIX","     0.7284000","     0.0000000","     1.3000000","     0.7284000","5","INT","INT01","A","05/09/2012","11/05/2012","USD","                 40.74","                  0.00","                 40.74","                458.28","                164.57","                 14.63","                 14.63","                  2.55","                  0.91","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                 40.74","                 40.74","                  0.00",
"18/11/10","273","     2","EU","521323703","1","600","687735670000","60033444","WOORI BANK","#203 HOEHYON DONG 1-GA"," "," "," ","CHUNG-GU"," ","SOUL100792","KOR","5000156512","ITIR-391912-0007","100030027","NAS","038830","038830","TR","345","USD","                860.10","                860.10","05/09/2012","08/28/2012","FIX","     0.5651000","     0.0000000","     1.1000000","     0.5651000","5","INT","INT01","A","05/09/2012","08/28/2012","USD","                  0.64","                  0.00","                  0.64","                  4.41","                  1.50","                  0.22","                  0.22","                  0.04","                  0.01","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                  0.64","                  0.64","                  0.00",
"18/11/10","273","     3","EU","521323735","1","600","31435000","60031456","INDUSTRIAL BANK OF MANDA"," "," "," "," ","MANDA"," "," ","KOR","5000128297","ITIR-315058-0009","500149320","NAS","038830","038830","TR","345","USD","             57,180.00","             57,180.00","05/09/2012","11/05/2012","FIX","     0.7284000","     0.0000000","     1.1000000","     0.7284000","5","INT","INT01","A","05/09/2012","11/05/2012","USD","                 46.47","                  0.00","                 46.47","                522.74","                208.24","                 18.51","                 18.51","                  2.90","                  1.16","N",""," ","                  0.00","                  0.00","01/01/1900","01/01/1900","0","0"," ","CNY","00000"," ","                 46.47","                 46.47","                  0.00",

Thanks,
Gangadhar
# 3  
Old 08-22-2012
Try
Code:
awk -vq='"' '/Proc Date/{d=$4}
/PROC UNIT *:/{u=$NF}
/^"[^".]+"/{print q d q "," q u q "," $0}' file

This User Gave Thanks to elixir_sinari For This Post:
Gangadhar Reddy
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

[Solved] Formatting the text file

Hi All, I ahve requirement where I want to put the text file in into proper format. I am wondering how can i achieve that:- Host/Alias Name IP Address Resolved sinuiy01.infra.go2uti.com 10.240.8.158 N sinuid20.devtst.go2uti.com 10.240.8.230 N sinuid21.devtst.go2uti.com... (6 Replies)
Discussion started by: sharsour
6 Replies

2. UNIX for Dummies Questions & Answers

Formatting data in a raw file by using another mapping file

Hi All, i have a requirement where i need to format the input RAW file ( which is CSV) by using another mapping file(also CSV file). basically i am getting feed file with dynamic headers by using mapping file (in that target field is mapped with source filed) i have to convert the raw file into... (6 Replies)
Discussion started by: ravi4informatic
6 Replies

3. Shell Programming and Scripting

[Solved] Sending a HTML email from cli loses formatting.

Hi, I have a .sh file, to email a report of our backups from a linux machine. It looks like this (minus a few bits): echo "HELO $host.$domain" sleep 1 echo "mail from: vdrreport@$domain" sleep 1 echo "rcpt to:$mailto" sleep 1 echo "data" sleep 1 echo "subject: $host VDR-Report... (2 Replies)
Discussion started by: cognito
2 Replies

4. Shell Programming and Scripting

Formatting file data to another file (control character related)

I have to write a program to read data from files and then format into another file. However, I face a strange problem related to control character that I can't understand and solve. The source file is compose of many lines with such format: T_NAME|P_NAME|P_CODE|DOCUMENT_PATH|REG_DATE ... (3 Replies)
Discussion started by: hk6279
3 Replies

5. Shell Programming and Scripting

File formatting

Hi, I have a file which contains data in this format # User@Host: abc @ Id: 0000000 # Query_time: 0.000070 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=00000000; SELECT @@version, @@version_comment; # User@Host: abcd @ Id: 00000000 # Query_time: 0.000228 ... (6 Replies)
Discussion started by: arijitsaha
6 Replies

6. Shell Programming and Scripting

Solved: Text formatting

What's the best way to format the text file shown below the way in which COlA ColB ColC ColD ---- ---- ---- ---- 70 923 AAA 5864 70 testing AAA 13062 PPPPPP test AAA 142 (0 Replies)
Discussion started by: joe228
0 Replies

7. Shell Programming and Scripting

[solved] merging two files and writing to another file- solved

i have two files as file1: 1 2 3 file2: a b c and the output should be: file3: 1~a 2~b 3~c (1 Reply)
Discussion started by: mlpathir
1 Replies

8. Shell Programming and Scripting

Formatting a file....

Hi, I have written a script which directs output of vmstat to a file... The file consists of 15 lines in all..... Through scripting I would like to re-format the file excluding first 4 lines... I donot wish to use vi editor and manually delete lines but i want this process to be... (2 Replies)
Discussion started by: EmbedUX
2 Replies

9. UNIX for Dummies Questions & Answers

Formatting a file.

I want to format a file to limit record length = 100, in each line. Any idea how i can do this? (1 Reply)
Discussion started by: abhilasha
1 Replies

10. Shell Programming and Scripting

Formatting a file

Hi All, I have been trying to format a file using sed. I can't seem to get the syntax right. I want to append the next line delemited by a comma or a comma and double quotes. Here is an example of the file I'm tring to format: Before formatting: 00324 03A0312 BRI-u24 0000324 01 H-12... (4 Replies)
Discussion started by: cstovall
4 Replies
Login or Register to Ask a Question