Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to parse this file and obtain a .csv or .xls
# 1  
Old 04-01-2011
how to parse this file and obtain a .csv or .xls
Hello Expert,
I have a file in the following format:
Code:
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
        APPLICATION "script.sh"
        MSGGRP "MSG"
        MSGCONDITIONS
                DESCRIPTION "Has NEW errors"
                CONDITION_ID "00000-34dc-25e0-1cfe-0a088b060000"
                CONDITION
                        OBJECT "<#>diff<*.folder>"
                        THRESHOLD 0.500000
                        RESET 0.500000
                SET
                        OBJECT "<$OPTION(area)>"
                        TEXT "There have been new failures Processing the <$OPTION(area)> 
file on Central.
                        AUTOACTION "ls <folder>" ANNOTATE
                        HELPTEXT "OPS
=========
Please call support team"
                        HELP "0d152f3c-59dc-71e0-1cfe-0a034b060000"
                DESCRIPTION "Has ANY errors"
                CONDITION_ID "d26dda06-55f2-71e0-0025-0a034b060000"
                CONDITION
                        OBJECT "<*.folder>"
                        THRESHOLD 0.500000
                        RESET 0.500000
                SET
                        OBJECT "<$OPTION(area)>"
                        TEXT "There has been a failure Processing the <$OPTION(area)> file 
                        HELPTEXT "OPS
=========
Please call out the support team"
                       others instruction
 others instruction
 HELP "0d152f3c-59dc-71e0-1cfe-0a034b060000"


I would like to transform it in .csv or .xls file.

I did the following command
1)
Code:
<more filename>| awk -F\" '{print $1","$2}'> /tmp/monitor.csv

2) But I need to show in two field also the rows without double quote:
Code:
                        THRESHOLD 0.500000
                        RESET 0.500000

3) Further (and this is more complicate to achieve) I need the part of this file after HELPTEXT and before HELP will be visualized in a unique row:
Code:
                        HELPTEXT "OPS
=========
Please call out the support team"
                       others instruction
 others instruction
 HELP "0d152f3c-59dc-71e0-1cfe-0a034b060000"


Is possible to achieve all these 3 steps with awk and in a unique script? Smilie

Thanks in advance for any help,
Ant-one
Last edited by Franklin52; 04-01-2011 at 07:13 AM.. Reason: Please use code tags
# 2  
Old 04-01-2011
Could this help you?
Code:
 perl -nle 'print $_ if /\bTHRESHOLD\b/../\bRESET\b/; print $_ if /\bHELPTEXT\b/../\bHELP\b/'  inputfile

# 3  
Old 04-01-2011
Hello,
when I do your command, I receive the follow result:
Code:
                        THRESHOLD 0.500000
                        RESET 0.500000

My desired output is in .csv:
Code:
MONITOR,NAME_TEMPLATES
        DESCRIPTION,Monitors for contents of error
        INTERVAL,1m
        MONPROG ,script.sh NAME_TEMPLATES,
... 
..
..
..
..
 (same row 2 fields)THRESHOLD, 0.500000
                        RESET, 0.500000
                        HELPTEXT ,OPS,
 (same row)         ========= Please call out the support team" others instruction others instruction

Thanks,
Ant-one
Last edited by Franklin52; 04-01-2011 at 09:05 AM.. Reason: Please use code tags
# 4  
Old 04-01-2011
Hi Ant-one, Try this,
Code:
awk -F\" '{gsub(/^ +/,"");if(/HELPTEXT/) {printf $0;flg=1;next}if(flg==1 && !/HELP/){printf $0" ";next} if(/HELP/){flg=0;print;next}if(/THRESHOLD / || /RESET /){gsub("THRESHOLD ","THRESHOLD,");gsub("RESET ","RESET,");print;next}print $1","$2}' inputfile

# 5  
Old 04-01-2011
Hi,
I received the following error:

Quote:
awk: The string THRESHOLD cannot contain a newline character.
The source line is 2.
The error context is
;next} if(/HELP/){flg=0;print;next}if(/THRESHOLD / || /RESET /){gsub("THRESHOLD >>>
<<<
syntax error The source line is 3.
awk: The statement cannot be correctly parsed.
The source line is 3.
awk: There are 2 missing } characters.
awk: There is a missing ) character.
Thanks,
Ant-one

---------- Post updated at 08:21 AM ---------- Previous update was at 07:44 AM ----------

Hi,
first of all many thanks.
It worked fine for the major part, this is the ouput of your command:

Quote:
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,
APPLICATION ,script.sh
MSGGRP ,MSG
MSGCONDITIONS,
DESCRIPTION ,Has NEW errors
CONDITION_ID ,00000-34dc-25e0-1cfe-0a088b060000
CONDITION,
OBJECT ,<#>diff<*.folder>
THRESHOLD,0.500000
RESET,0.500000
SET,
OBJECT ,<$OPTION(area)>
TEXT ,There have been new failures Processing the <$OPTION(area)>
,
file on Central.,
AUTOACTION ,ls <folder>
HELPTEXT "OPS========= Please call support team" HELP "0d152f3c-59dc-71e0-1cfe-0a034b060000"
DESCRIPTION ,Has ANY errors
CONDITION_ID ,d26dda06-55f2-71e0-0025-0a034b060000
CONDITION,
OBJECT ,<*.folder>
THRESHOLD,0.500000
RESET,0.500000
SET,
OBJECT ,<$OPTION(area)>
TEXT ,There has been a failure Processing the <$OPTION(area)> file
,
HELPTEXT "OPS========= Please call out the support team" others instruction others instruction
Is it possible to split SEVERITY Major in 2 fields, please?

Quote:
SEVERITY Major,
The text shoul be in 2 part:

TEXT ,There have been new failures Processing the <$OPTION(area)> file on Central.

Instead:

Quote:
TEXT ,There have been new failures Processing the <$OPTION(area)>
,
file on Central.,
And the last part
Quote:

HELPTEXT "OPS========= Please call support team" HELP "0d152f3c-59dc-71e0-1cfe-0a034b060000"
Should be without the part HELP "0d152f3c-59dc-71e0-1cfe-0a034b060000" as follow:


Quote:
HELPTEXT "OPS========= Please call support team"
Thanks in advance for any suggestion.

Ant-one
# 6  
Old 04-01-2011
Also try
Code:
nawk '/HELPTEXT/{x=$0;getline;do {x=x" "$0 ; getline}while ($0!~/HELP /);sub(/^ */,"",x);sub(" ",", ",x);sub(/  */," ",x);print x}NF>1{sub($1,$1",",$0)}1' infile

or
Code:
 nawk '/HELPTEXT/{x=$0;getline;do{x=x" "$0;getline}while($0!~/HELP /);sub(/ ["]OPS/,", \"OPS,",x);print x}NF>1{sub($1,$1",",$0)}1' infile

Last edited by ctsgnb; 04-01-2011 at 10:31 AM..
# 7  
Old 04-01-2011
Hi,
thanks for your suggestion but there is not nawk on my server.

I have added severity into pravin27 code:

Quote:
awk -F\" '{gsub(/^ +/,"");if(/HELPTEXT/) {printf $0;flg=1;next}if(flg==1 && !/HELP/)
{printf $0" ";next} if(/HELP/){flg=0;print;next}if(/THRESHOLD / || /RESET / || /SEVERITY /)
{gsub("THRESHOLD ","THRESHOLD,");gsub("RESET ","RESET,");gsub("SEVERITY ","SEVERITY,");print;next}print $1","$2}' monitor
Regards,
Ant-one
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

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: Jobname Type type sqlname gemd1970 sql daily tran01 gemw1971 sql weekly ... (6 Replies)
Discussion started by: vedanta
6 Replies

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

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. UNIX for Advanced & Expert Users

obtain duplicate keys in csv file

Hi, having two csv files, both sorted, by key (column1), f1 containing duplicate keys and f2 containing no duplicate keys, how can I obtain all rows from f1 with the keys listed in file2? Example: f1 is: k1,gsj01fd k2,vi982cj k2,1fjk01e k3,81kjfds k4,sd9dasi f2 is: k2 k3 and I... (3 Replies)
Discussion started by: oscarmon
3 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