Format row data into columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Format row data into columns
# 8  
Old 02-28-2011
Thanks Ravi ji Smilie I'll go through it

I was able to work out on the file I mentioned. Btwn I have one question:
There is a line that i mentioned as
command: /home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact3.sh m
For the above line this workds fine:
/command/{c=substr($2,1,index($2," ")-1)}

But its an issue if at the end of line there is no "m". Give me a solution which will work in both cases.
command: /home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact3.sh

Please help
# 9  
Old 02-28-2011
Just change the condition as below:

Code:
 
/command/{if(substr($2,index($2,".")+4)=="m" { c=substr($2,1,index($2," ")-1)} else {c=$2}}

or

Code:
/command/{if(substr($2,index($2," ")+1)=="m" { c=substr($2,1,index($2," ")-1)} else {c=$2}}

I did not tested , so please debug the code/test it by getting ".sh" position and then +2 to get "m". if it exists derive "c" differently , else $2 will be "c".
This User Gave Thanks to panyam For This Post:
# 10  
Old 02-28-2011
@ganga reddy :

I based my code on the sample you gave : if your input file is different than the sample you posted, then of course, unexpected output may come.

By the way, i noticed in your copy past of my command line , you have :

Code:
...n|alarm_if_fail"};!/^std/&&!/p$&!/perm/{printf  "%s%s",$2,(/alarm/)?RS:OFS}' OFS=\| <

So in red it looks like copy/paste or typo error you did when copying my suggestion ...

Code:
...n|alarm_if_fail"};!/^std/&&!/perm/{printf "%s%s",$2,(/alarm/)?RS:OFS}' OFS=\|

And ... as suggested by vgersh99, before running my code,
make sure you have changed awk by nawk if running it on solaris.

Last edited by ctsgnb; 02-28-2011 at 03:41 PM..
This User Gave Thanks to ctsgnb For This Post:
# 11  
Old 02-28-2011
Hi Gangadhar Reddy,

Sorry for my lateness to reply, I had not connected until now.

In order to handle when "m" appears or not, I have modify a little bit the line you mention from
Code:
/command/{c=substr($2,1,index($2," ")-1)}

to
Code:
/command/{if(index($2," ")==0) {c=$2} else {c=substr($2,1,index($2," ")-1)}}

The modified code is as follow:
Code:
awk -F": " 'BEGIN{print "insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail"}
{sub(/[ \t]+$/, "")} # New line added to remove any whitespaces/tabs at the end of each line.
{sub(/[ ]*job_type/,": job_type",$0)}
/insert_job/{a=$2}
/job_type/{b=$4}
/command/{if(index($2," ")==0) {c=$2} else {c=substr($2,1,index($2," ")-1)}}
/machine/{d=$2}
/owner/{e=$2}
/condition/{f=$2}
/description/{g=$2}
/alarm_if_fail/{h=$2;print a,b,c,d,e,f,g,h} # Modified a little bit to make it shorter than before
' OFS="|" inputfile

Below I try to explain the code, I hope it helps and I hope be clear enough Smilie.

Code:
awk -F": " 'BEGIN{print "insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail"} # We begin setting headers
{sub(/[ \t]+$/, "")} 
# To remove any whitespaces/tabs at the end of each line to avoid more if statements below

{sub(/[ ]*job_type/,": job_type",$0)} 
# Replace the string " job_type" with ": job_type" to work globally with FS=": "

#For the lines of code like: 
/STRING/{X=$i}
# All lines of this form means, "Search string "STRING", when it is found, assign value of field "i" of that line to variable "X".
 
/command/{if(index($2," ")==0) {c=$2} else {c=substr($2,1,index($2," ")-1)}} 
# Search string "command", when found analyse as follow
# The part "{if(index($2," ")==0) {c=$2}"  means, if in field 2 there are no any spaces "(index($2," ")==0)", assign value of field 2 to c,
#The part "else {c=substr($2,1,index($2," ")-1)}}" means, if there are spaces in $2, extract a new substring from $2, from the beginning up to the space position, (given by index..)
# the "-1" part is to remove the space at the end of the substring extracted from $2, resulting when is removed the "m" or any other word present.

/alarm_if_fail/{h=$2;print a,b,c,d,e,f,g,h} # The part "print a,b,c,d,e,f,g,h" prints all variables in the order showed
' OFS="|" inputfile # the part  OFS="|" at the end, sets the Output Field Separator = "|".

Regards Smilie,
This User Gave Thanks to cgkmal For This Post:
# 12  
Old 03-01-2011
Thanks Panyam & Ctsgnb.

Thanks Cgkmal for the detailed explanation Smilie

---------- Post updated at 10:21 AM ---------- Previous update was at 09:40 AM ----------

Cgkmal,
One more question. As I said i wrote script for one input which had following lines
watch_file: /home/btchproc/load_process/ftp_home/NNA_STRT.DAT
watch_file_min_size: 0

And I just wanted watch_file column in my output. But when I specified
/watch_file/{f=$2}
it showed the value of watch_file_min_size.

For now what I did is, I globally replaced the watch_file_min_size with min_size and got the desired output. But what wud be the correct way to do that.

Thanks

Last edited by Gangadhar Reddy; 03-02-2011 at 06:11 PM..
# 13  
Old 03-01-2011
Hi Gangadhar Reddy,

No problem, I learning helping others.

Quote:
Cgkmal,
One more question. As I said i wrote script for one input which had following lines
watch_file: /home/btchproc/load_process/ftp_home/NNA_STRT.DAT
watch_file_min_size: 0

And I just wanted watch_file column in my output. But when I specified
/watch_file/{f=$2}
it showed the value of watch_file_min_size.

For now what I did is, I globally replaced the watch_file_min_size with min_size and got the desired output. But what wud be the correct way to do that.
In this case is needed to match the exact word, "watch_file",

you can do it by replacing this from
Code:
/watch_file/{f=$2}

to
Code:
/\<watch_file\>/{f=$2}

* The characters  "\<" and "\>"  do  "anchor" the expression between to only match if it is on a word boundary.
Ref: Regular Expressions

Regards
# 14  
Old 03-07-2011
Need help again (This is a bit tricky)

Here is my input file:
______________________________________________________________________________
Start Dependent
Job Name Status Date Cond? Cond? Jobs?
-------- ------ --------------- ----- ---------
DWC$DAC103_acct_naked_opt INACTIVE No Yes Yes

Condition: s(DWC$DAC111_acct) and s(DWC$DAC141_cust) and
s(DWC$DAC131_acct_cust_rel) and s(DWC$DAC134_cust_classfn)


Atomic Condition Current Status T/F
---------------- -------------- ---
SUCCESS(DWC$DAF103_ACCNKTOPTSTRT.DAT) RUNNING F

Dependent Job Name Condition
------------------ ---------
RDS$DAC524_D_DWC_AC_NKDO_FACT SUCCESS(DWC$DAC103_acct_naked_opt)
WST$DAC437_R037_Uncovered_Opt SUCCESS(DWC$DAC103_acct_naked_opt)


I want the output as:
Job Name|Dependent Job Name
DWC$DAC103_acct_naked_opt|RDS$DAC524_D_DWC_AC_NKDO_FACT, WST$DAC437_R037_Uncovered_Opt


Note: The Depenedent job may be 1,2,3, etc or it may not be present too.

---------- Post updated at 04:49 PM ---------- Previous update was at 04:46 PM ----------

Quote:
Originally Posted by cgkmal
Hi Gangadhar Reddy,

No problem, I learning helping others.

In this case is needed to match the exact word, "watch_file",

you can do it by replacing this from
Code:
/watch_file/{f=$2}

to
Code:
/\<watch_file\>/{f=$2}

* The characters  "\<" and "\>"  do  "anchor" the expression between to only match if it is on a word boundary.
Ref: Regular Expressions

Regards
I have one more question - in case if the watch_file line is not present for some jobs. What happens now is the next time it get other jobs, the watch_file value is same as previous job. How can I make it void at the end of one loop so that next time if it doesn't get watch_file line there should be blank.

Last edited by Gangadhar Reddy; 03-08-2011 at 11:59 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk - script help: column to row format of data allignment?

Experts Good day, I have the following data, file1 BRAAGRP1 A2X B2X C2X D2X BRBGRP12 A3X B3X Z10 D09 BRC1GRP2 LO01 (4 Replies)
Discussion started by: rveri
4 Replies

2. Shell Programming and Scripting

Sort a the file & refine data column & row format

cat file1.txt field1 "user1": field2:"data-cde" field3:"data-pqr" field4:"data-mno" field1 "user1": field2:"data-dcb" field3:"data-mxz" field4:"data-zul" field1 "user2": field2:"data-cqz" field3:"data-xoq" field4:"data-pos" Now i need to have the date like below. i have just... (7 Replies)
Discussion started by: ckaramsetty
7 Replies

3. Shell Programming and Scripting

Splitting data from one row as multiple columns

Hi I have a file containing some data as follows: 11-17-2010:13:26 64 4 516414 1392258 11-17-2010:13:26 128 4 586868 695603 11-17-2010:13:26 256 4 474937 1642294 11-17-2010:13:32 64 4 378715 1357066 11-17-2010:13:32 128 4 597981 1684006 ... (17 Replies)
Discussion started by: annazpereira
17 Replies

4. Shell Programming and Scripting

Help converting row data to columns

I've been trying to figure this out for a while but I'm completely stumped. I have files with data in rows and I need to convert the data to columns. Each record contains four rows with a "field name: value" pair. I would like to convert it to four columns with the field names as column headers... (5 Replies)
Discussion started by: happy_ee
5 Replies

5. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies

6. UNIX for Dummies Questions & Answers

Sum of data in row format

Hi All, I have some numbers in two different files file1 4.21927E+00 4.68257E+00 5.56871E+00 3.59490E+01 7.65806E+01 1.39827E+02 and file2 5.61142E+00 6.21648E+00 7.40152E+00 4.41917E+01 8.31586E+01 1.42938E+02 I would like to get file3 which contains in each column the sum of the... (6 Replies)
Discussion started by: f_o_555
6 Replies

7. Shell Programming and Scripting

Format data to columns addind spaces

Hi all, I have a problem to format data from different database queries into one look. The input data are as follows, every line has the same number of values but a different number of characters: adata, bdata, cdata, ddata fffdata, gdata, hdata, idata jdata, kdata, ... (6 Replies)
Discussion started by: old_mike
6 Replies

8. Shell Programming and Scripting

Format - Inventory Row data into Column - Awk - Nawk

Hi All, I have the following file that has computer data for various pcs in my network... Snap of the file is as follows ******************************************************************************* Serial 123456 Computer IP Address lo0:... (1 Reply)
Discussion started by: aavam
1 Replies

9. Shell Programming and Scripting

How to insert data befor some field in a row of data depending up on values in row

Hi I need to do some thing like "find and insert before that " in a file which contains many records. This will be clear with the following example. The original data record should be some thing like this 60119827 RTMS_LOCATION_CDR INSTANT_POSITION_QUERY 1236574686123083rtmssrv7 ... (8 Replies)
Discussion started by: aemunathan
8 Replies

10. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies
Login or Register to Ask a Question