Visit Our UNIX and Linux User Community


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


 
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 08: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 10: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 11: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

Previous Thread | Next Thread
Test Your Knowledge in Computers #251
Difficulty: Easy
MILNET was designed for highly classified military communications.
True or False?

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

Featured Tech Videos