[Solved] Need help formatting a file

Shell Programming and Scripting
1

I have a report similar to the below:

^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

"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

2

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

"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

Try

awk -vq='"' '/Proc Date/{d=$4}
/PROC UNIT *:/{u=$NF}
/^"[^".]+"/{print q d q "," q u q "," $0}' file
1 Like