Generate .csv/ xls file report


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Generate .csv/ xls file report
# 1  
Old 05-04-2017
Generate .csv/ xls file report

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:
Code:
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:

Code:
#!/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:

Code:
#!/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

Last edited by Corona688; 05-04-2017 at 05:45 PM..
# 2  
Old 05-04-2017
Like so:
Code:
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

This User Gave Thanks to RudiC For This Post:
# 3  
Old 05-05-2017
Can it be done using grep? thanks
# 4  
Old 05-05-2017
No. grep prints matching lines or patterns - it does not rearrange nor combine lines. And it doesn't print headers.
This User Gave Thanks to RudiC For This Post:
# 5  
Old 05-08-2017
Can the output have all filenames, even if nothing found with awk
# 6  
Old 05-08-2017
Hello Vedanta,

Could you please try following and let me know if this helps you.
Code:
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.
Code:
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:
Quote:
Can the output have all filenames, even if nothing found with awk
Could you please post expected sample output in CODE-TAGS please. As it is not clear.

Thanks,
R. Singh

Last edited by RavinderSingh13; 05-08-2017 at 04:57 PM..
This User Gave Thanks to RavinderSingh13 For This Post:
# 7  
Old 05-08-2017
Quote:
Originally Posted by vedanta
Can the output have all filenames, even if nothing found with awk
How should that look like?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Perl script to Convert XLSX or XLS files to CSV file

Hi All, I've got in a situation where I need to convert .xlsx or .xls formatted files into simple text file or .csv file. I've found many options but doing this using PERL script is the best way I believe.I'm in AIX box. Perl code should have 2 params while running. i.e perl... (1 Reply)
Discussion started by: manab86
1 Replies

2. Shell Programming and Scripting

how to parse this file and obtain a .csv or .xls

Hello Expert, I have a file in the following format: SYNTAX_VERSION 5 MONITOR "NAME_TEMPLATES" DESCRIPTION "Monitors for contents of error " INTERVAL "1m" MONPROG "script.sh NAME_TEMPLATES" MAXTHRESHOLD GEN_BELOW_RESET SEVERITY Major ... (17 Replies)
Discussion started by: Ant-one
17 Replies

3. Shell Programming and Scripting

How to convert a xls file to csv?

Hi, My requirement is to convert the xls to csv file with utf-8 conversion. Is there any way please suggest me. Thanks, Raja (4 Replies)
Discussion started by: cnraja
4 Replies

4. UNIX for Advanced & Expert Users

Converting .csv file into .xls file and send it to inbox

Hi All, I wrote a script to extract data from Oracle DB and place it in a text file , and I have coverted .txt file into comma seperated .csv file and I sent it to my mail box . I can get .xls file in my inbox.I am getting all data in same column and in different rows , without column... (1 Reply)
Discussion started by: krthkmuthu
1 Replies

5. Shell Programming and Scripting

converting xls file to txt file and xls to csv

I need to convert an excel file into a text file and an excel file into a CSV file.. any code to do that is appreciated thanks (6 Replies)
Discussion started by: bandar007
6 Replies

6. UNIX for Dummies Questions & Answers

Unix script to convert .csv file to.xls format

I have a .csv file in Unix box i need a UNIX script to convert the.csv files to.xls format. Its very urgent please help me. (1 Reply)
Discussion started by: moon_friend
1 Replies

7. Shell Programming and Scripting

Convert a csv file to an xls format

Hi, I have a file coming in xxx.txt(csv format) i do some work on it and i need to send out as a .xls format. Is there any way there is some code i can use in my script to convert this? I'm struggling on this. Thanks (11 Replies)
Discussion started by: Pablo_beezo
11 Replies

8. Shell Programming and Scripting

Generate csv file

I have a file which has some thousand records in the following format File: input.txt -> <option value="14333">VISWANADH VELAMURI</option> <option value="17020">VISWANADHA RAMA KRISHNA</option> I want to generate a csv file from the above file as follows File: output.txt -> ... (4 Replies)
Discussion started by: rahulrathod
4 Replies

9. Shell Programming and Scripting

From xls to csv file

Can we convert an xls file into csv format in Unix Thanks Suresh (1 Reply)
Discussion started by: sureshg_sampat
1 Replies

10. Shell Programming and Scripting

copying the csv file into different worksheets of xls file

Hi, I have a script which will generate three csv files. i want to copy the contents of these csv files into a .XLS file but in different worksheets. Can a this be done in the same script? :confused: Can Perl come to my help in coping the csv files into different worksheets of .XLS file ?... (0 Replies)
Discussion started by: nimish
0 Replies
Login or Register to Ask a Question