Generate .csv/ xls file report

Shell Programming and Scripting
1

There can be thousand of .ksh in a specific directory where sql files are called from ksh.

Requirement is to loop through all the files content and generate a report like below:

Jobname        Type    type        sqlname

gemd1970     sql    daily        tran01
gemw1971    sql    weekly        tran05
..

sample two file content are as below:

filename: gemd1970.ksh
content of gemd1970.ksh:

#!/bin/ksh
JOBNAME=gemd1970 
./prod/batch/appusr/scripts/var
# print time of execetc
$DIRPATH/scripts/runsql.ksh tran01
# success / failure message

filename: gemd1971.ksh
content of gemd1971.ksh:

#!/bin/ksh
JOBNAME=gemw1971 
./prod/batch/appusr/scripts/var
# print time of execetc
$DIRPATH/scripts/runsql.ksh tran05 1
# success / failure message

How to achieve this?

logic to determine daily /weekly/monthly is derived after the 3 rd letter in the jobname. e.g, gemw1971, here w denotes weekly

2

Like so:

awk '
NR == 1         {print "Jobname \tType\ttype\tsqlname"
                 TYPE["d"] = "daily"
                 TYPE["w"] = "weekly"
                 next
                }


/^JOBNAME/      {split ($0, T, "=")
                 NAME   = T[2]
                 TMPTYP = TYPE[substr(T[2], 4, 1)]
                }
/^\$DIRPATH/    {gsub (/^.*\/|\..*$/, _, $1)
                 print NAME OFS $1 OFS TMPTYP OFS $2
                }
' OFS="\t" *.ksh
Jobname      Type      type     sqlname
gemd1970     runsql    daily    tran01
gemw1971     runsql    weekly   tran05
1 Like
3

Can it be done using grep? thanks

4

No. grep prints matching lines or patterns - it does not rearrange nor combine lines. And it doesn't print headers.

1 Like
5

Can the output have all filenames, even if nothing found with awk

6

Hello Vedanta,

Could you please try following and let me know if this helps you.

awk -F'[ =/.]' 'BEGIN{print "Jobname \tType\ttype\tsqlname";array["d"]="Daily";array["w"]="Weekly";array["m"]="Monthly"} /JOBNAME/{printf("%s\t",$2);VAL=substr($2,4,1);next} /^\$DIRPATH/{printf("%s\t%s\t%s\n",$3,array[VAL],$5)}'  *.ksh

EDIT: Adding a non-one liner form of solution too now.

awk -F'[ =/.]' 'BEGIN{
                        print "Jobname \tType\ttype\tsqlname";
                        array["d"]="Daily";
                        array["w"]="Weekly";
                        array["m"]="Monthly"
                     }
                /JOBNAME/{
                                printf("%s\t",$2);
                                VAL=substr($2,4,1);
                                next
                         }
               /^\$DIRPATH/{
                                printf("%s\t%s\t%s\n",$3,array[VAL],$5)
                           }
               '    *.ksh

EDIT2:

Could you please post expected sample output in CODE-TAGS please. As it is not clear.

Thanks,
R. Singh

1 Like
7

How should that look like?