Format row data into columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Format row data into columns
# 1  
Old 02-26-2011
Format row data into columns

I have a file which looks like this:

Code:
/* ----------------- EDW$MOC139_R_NNA_BR_SUM_FACT2 ----------------- */

insert_job: EDW$MOC139_R_NNA_BR_SUM_FACT2   job_type: c
command: /home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact2.sh m
machine: edwprod02.dsm.pwj.com
#owner: btchproc
permission: gx,ge,wx,we,mx,me
condition: s(EDW$MOF139_R_NNA_BR_SUM_FACT2)
description: "Loads NNA Branch Summary tables 2"
std_out_file: >/tmp/EDW_MOC139.out
std_err_file: >/tmp/EDW_MOC139.err
alarm_if_fail: 1

/* ----------------- EDW$MOC140_R_NNA_BR_SUM_FACT3 ----------------- */

insert_job: EDW$MOC140_R_NNA_BR_SUM_FACT3   job_type: c
command: /home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact3.sh m
machine: edwprod02.dsm.pwj.com
#owner: btchproc
permission: gx,ge,wx,we,mx,me
condition: s(EDW$MOF140_R_NNA_BR_SUM_FACT3)
description: "Loads NNA Branch Summary tables 3"
std_out_file: >/tmp/EDW_MOC140.out
std_err_file: >/tmp/EDW_MOC140.err
alarm_if_fail: 1

I want the output to be like this:
Code:
insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail
EDW$MOC139_R_NNA_BR_SUM_FACT2|c|/home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact2.sh|edwprod02.dsm.pwj.com|btchproc|s(EDW$MOF139_R_NNA_BR_SUM_FACT2)|Loads NNA Branch Summary tables 2|1
EDW$MOC140_R_NNA_BR_SUM_FACT3|c|/home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact3.sh|edwprod02.dsm.pwj.com|btchproc|s(EDW$MOF140_R_NNA_BR_SUM_FACT3)|Loads NNA Branch Summary tables 3|1

Thanks for your solutions in advance.

Last edited by radoulov; 02-26-2011 at 04:35 PM.. Reason: Code tags, please!
# 2  
Old 02-26-2011
Code:
sed 's/: /:/g;s/  */ /g;/^$/d;/^\//d;/std.*file/d;/permiss/d;/^command/s/ .$//' tst |xargs -n1 | awk -F: 'BEGIN{print "insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail"};!/^std/&&!/perm/{printf "%s%s",$2,(/alarm/)?RS:OFS}' OFS=\|

---------- Post updated at 11:57 PM ---------- Previous update was at 11:53 PM ----------

I am sure there are some better way to do it but until they are posted, here is a way

Code:
[ctsgnb@shell ~/sand]$ cat tst
/* ----------------- EDW$MOC139_R_NNA_BR_SUM_FACT2 ----------------- */

insert_job: EDW$MOC139_R_NNA_BR_SUM_FACT2   job_type: c
command: /home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact2.sh m
machine: edwprod02.dsm.pwj.com
#owner: btchproc
permission: gx,ge,wx,we,mx,me
condition: s(EDW$MOF139_R_NNA_BR_SUM_FACT2)
description: "Loads NNA Branch Summary tables 2"
std_out_file: >/tmp/EDW_MOC139.out
std_err_file: >/tmp/EDW_MOC139.err
alarm_if_fail: 1

/* ----------------- EDW$MOC140_R_NNA_BR_SUM_FACT3 ----------------- */

insert_job: EDW$MOC140_R_NNA_BR_SUM_FACT3   job_type: c
command: /home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact3.sh m
machine: edwprod02.dsm.pwj.com
#owner: btchproc
permission: gx,ge,wx,we,mx,me
condition: s(EDW$MOF140_R_NNA_BR_SUM_FACT3)
description: "Loads NNA Branch Summary tables 3"
std_out_file: >/tmp/EDW_MOC140.out
std_err_file: >/tmp/EDW_MOC140.err
alarm_if_fail: 1

Code:
[ctsgnb@shell ~/sand]$ sed 's/: /:/g;s/  */ /g;/^$/d;/^\//d;/std.*file/d;/permiss/d;/^command/s/ .$//' tst |xargs -n1 | awk -F: 'BEGIN{print "insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail"};!/^std/&&!/perm/{printf "%s%s",$2,(/alarm/)?RS:OFS}' OFS=\|
insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail
EDW$MOC139_R_NNA_BR_SUM_FACT2|c|/home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact2.sh|edwprod02.dsm.pwj.com|btchproc|s(EDW$MOF139_R_NNA_BR_SUM_FACT2)|Loads NNA Branch Summary tables 2|1
EDW$MOC140_R_NNA_BR_SUM_FACT3|c|/home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact3.sh|edwprod02.dsm.pwj.com|btchproc|s(EDW$MOF140_R_NNA_BR_SUM_FACT3)|Loads NNA Branch Summary tables 3|1
[ctsgnb@shell ~/sand]$


Last edited by ctsgnb; 02-28-2011 at 03:46 PM..
This User Gave Thanks to ctsgnb For This Post:
# 3  
Old 02-27-2011
Gangadhar Reddy,
Please try with:

Code:
:~$ awk -F": " 'BEGIN{print "insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail"}
 {sub(/[ ]*job_type/,": job_type",$0)}
 /insert_job/{a=$2}
 /job_type/{b=$4}
 /command/{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}
 ' inputfile
insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail
EDW$MOC139_R_NNA_BR_SUM_FACT2|c|/home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact2.sh|edwprod02.dsm.pwj.com|btchproc|s(EDW$MOF139_R_NNA_BR_SUM_FACT2)|"Loads NNA Branch Summary tables 2"|1
EDW$MOC140_R_NNA_BR_SUM_FACT3|c|/home/btchproc/load_process/batch_files/batch_nna_brn_split_sum_fact3.sh|edwprod02.dsm.pwj.com|btchproc|s(EDW$MOF140_R_NNA_BR_SUM_FACT3)|"Loads NNA Branch Summary tables 3"|1

This User Gave Thanks to cgkmal For This Post:
# 4  
Old 02-28-2011
ctsgnb & cgkmal,
Thanks both of you for your advice & time.

I tried executing both of them but it' giving me syntax errors:
I name the inout file as "b" and here is the error:
$sed 's/ :/:/g;s/ */ /g;/^$/d;/^\//d;/std.*file/d;/permiss/d;/^command/s/ .$//' b |xargs -n1 | awk -F: 'BEGIN{print "insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail"};!/^std/&&!/p$&!/perm/{printf "%s%s",$2,(/alarm/)?RS:OFS}' OFS=\| <
awk: syntax error near line 1
awk: bailing out near line 1

$awk -F": " 'BEGIN{print "insert_job|job_type|command|machine|owner|condition|description|alarm_if_fail"}
> {sub(/[ ]*job_type/,": job_type",$0)}
> /insert_job/{a=$2}
> /job_type/{b=$4}
> /command/{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}
> ' b
awk: syntax error near line 2
awk: illegal statement near line 2

My server configuration:
$uname -a
SunOS edwprod01.dsm.pwj.com 5.8 Generic_117350-53 sun4u sparc SUNW,Netra-T12

Thanks,
Gangadhar
# 5  
Old 02-28-2011
On Solaris, use nawk instead of awk
This User Gave Thanks to vgersh99 For This Post:
# 6  
Old 02-28-2011
Thanks vgersh99. It worked now Smilie

ctsgnb, ur script worked for few column till condition and then it had some discrepancy for rest of the columns. Thanks anyways for your help. I appreciate it.

cgkmal, ur script worked brilliantly. thanks so much for ur valuable advice. Can you please explain it? I have one more following type of file which again I need to format it. I would like to try it myself first.

Also, I had read lots of UNIX books where they have just mentioned simple descriptions for AWK & SED. Do you know of any books which I can read to get good exposure to AWK & SED programming? That will help me a lot

$jr 'EDW$MOF111_R_NNA_STRT' -q
/* ----------------- EDW$MOF111_R_NNA_STRT ----------------- */
insert_job: EDW$MOF111_R_NNA_STRT job_type: f
machine: edwprod02.dsm.pwj.com
#owner: btchproc
permission: gx,ge,wx,we,mx,me
date_conditions: 1
run_calendar: EDW_NNA_Cal
start_times: "18:00"
description: "EDW - Enterprise Data Warehouse"
watch_file: /home/btchproc/load_process/ftp_home/NNA_STRT.DAT
watch_file_min_size: 0
watch_interval: 60
alarm_if_fail: 1

Love u all guys.
# 7  
Old 02-28-2011
Reddy garu,

Sed - An Introduction and Tutorial

Awk - A Tutorial and Introduction - by Bruce Barnett

I believe these tutorials are good and self explanatory.

Regards
Ravi
This User Gave Thanks to panyam For This Post:
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