Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Format row data into columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Format row data into columns
# 22  
Old 03-12-2011
Hi again GReddy Smilie,

Below is the script I could get, I'm sure it could be done in a shorter and elegant way and without temp files, but it works for me in the input sample
you've provided, I hope it works in your real files.
Code:
# 1) <+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+>
### Inserting "_" between "Job Name", "Atomic Condition" and "Dependent Job Name" and "XYZ_Atomic..." #####
### below "Atomic..." lines to handle easier in next steps, at the end saving 1st field in temp1 ##########
###########################################################################################################
sed -e 's/Job N/Job_N/;s/mic C/mic_C/;s/dent J/dent_J/;s/\(Atomic.*\)/XYZ_\1\n\1/' input | 
awk '{print $1}' > temp1
# 2) <+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+>
### deleting blank and other unwanted lines and saving it in temp 2 #######################################
sed -e 's/Job_N/Job N/;s/mic_C/mic C/;s/dent_J/dent J/;s/.*Start.*//;s/^[ \t]*//;s/^-.*//;s/^_.*$//;/^$/d;
/Condition:/,/XYZ_Atomic/d' temp1 > temp2
###########################################################################################################
# 3) <+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+>
### Set headers, merge lines in single one with "," as Field Separator ####################################
awk 'BEGIN{print "Job Name|Atomic Condition|Dependent Job Name"}
{
if ( $0~/Job Name||Atomic Condition||Dependent Job Name||.*_.*/ )
printf("%s,", $0)
else
printf(" %s\n", $0)
}' temp2 | 
###########################################################################################################
# 4) <+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+>
### Delete and parsing the continuous fields joined by "," and converting to "|" in needed cases ##########
sed -e 's/^Job Name,//g;s/,Job Name,/\n/g;s/,Atomic Condition,/|/g;s/,Dependent Job Name,/|/g;s/,$//' | 
###########################################################################################################
# 5) <+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+>
### Printing out the fields separated by "|" ##############################################################
awk -F"|" '{print $1,$2,$3}' OFS="|" > outputfile
###########################################################################################################
# 6) <+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+><+>
### Deleting temp files ###################################################################################
rm temp*
###########################################################################################################

Hope it helps,

Best regards

---------- Post updated at 02:48 AM ---------- Previous update was at 12:57 AM ----------

Well, I could get it without using temp files, in an alternation of awk and sed commands, maybe some awk expert could help me to join all awk parts in a single one, without changing too much the code in order to learn how the same code could be merged.
Code:
awk  '/^Job/,/^$/{print $1};/Atomic/,/^$/{print $1};/Dependent Job Name/,/^$/{print $1}' input | 

sed -e 's/^-.*//;s/^_.*$//;/^$/d' | 

awk 'BEGIN{print "Job Name|Atomic Condition|Dependent Job Name"}
{
if ( $0~/Job||Atomic||Dependent||.*_.*/ )
printf("%s,", $0)
else
printf(" %s\n", $0)
}' | 

sed -e 's/^Job,//g;s/,Job,/\n/g;s/,Atomic,/|/g;s/,Dependent,/|/g;s/,$//' | 

awk -F"|" '{print $1,$2,$3}' OFS="|"  > output

Regards
# 23  
Old 03-12-2011
Cgkmal,
It worked till temp2 file creation. The last one when I tried to execute, I received an error:
Code:
nawk 'BEGIN{print "Job Name|Atomic Condition|Dependent Job Name"}
{
if ( $0~/Job Name||Atomic Condition||Dependent Job Name||.*_.*/ )
printf("%s,", $0)
else
printf(" %s\n", $0)
}' temp2 | 
sed -e 's/^Job Name,//g;s/,Job Name,/\n/g;s/,Atomic Condition,/|/g;s/,Dependent Job Name,/|/g;s/,$//' | 
nawk -F"|" '{print $1,$2,$3}' OFS="|" > outputfile

Error:
Code:
nawk: illegal primary in regular expression Job Name||Atomic Condition||Dependent Job Name||.*_.* at Atomic Condition||Dependent Job Name||.*_.*
 source line number 3
 context is
        if ( $0~/Job Name||Atomic Condition||Dependent Job >>>  Name||.*_.*/ <<<  )

# 24  
Old 03-12-2011
Quote:
Originally Posted by Gangadhar Reddy
Cgkmal,
It worked till temp2 file creation. The last one when I tried to execute, I received an error:
If the script creates the temp2 file, then the problem would be with the sed part, strange because it looks the first sed part in step 1 and 2 work.
try removing the "g's" (in red) from the sed part that is after creation of temp2 file.
Code:
sed -e 's/^Job Name,//g;s/,Job Name,/\n/g;s/,Atomic Condition,/|/g;s/,Dependent Job Name,/|/g;s/,$//' | 
nawk -F"|" '{print $1,$2,$3}' OFS="|" > outputfile

I'm not sure what could be the problem, because I'm don't have solaris to try.

See what happens,

Regards
# 25  
Old 03-12-2011
cgkmal,
looking at the error it seems to be an error at the following line
if ( $0~/Job Name||Atomic Condition||Dependent Job Name||.*_.*/ )

some probs with .*_.*/ ????
# 26  
Old 03-13-2011
Hi GReddySmilie,
Quote:

Gangadhar Reddy cgkmal,
looking at the error it seems to be an error at the following line
if ( $0~/Job Name||Atomic Condition||Dependent Job Name||.*_.*/ )

some probs with .*_.*/ ????
When I tried to solve that, I discovered that part of the code is not needed at all, and the regexp you were facing problems with is not present now.

Each new version of the code is smaller than before, and now without using temp files. Meanwhile I can come up with a better code,
please try with the code below, it seems to workSmilie.

Code:
awk  '/^Job/,/^$/{print $1};/Atomic/,/^$/{print $1};/Dependent Job Name/,/^$/{print $1}' newinput | 

sed -e 's/^-.*//;s/^_.*$//;/^$/d' | 

awk 'BEGIN{print "Job Name|Atomic Condition|Dependent Job Name"}
{printf("%s,", $0)}' | 

sed -e 's/^Job,//g;s/,Job,/\n/g;s/,Atomic,/|/g;s/,Dependent,/|/g;s/,$//' | 

awk -F"|" '{print $1,$2,$3}' OFS="|" > output

Best regards
This User Gave Thanks to cgkmal For This Post:
Gangadhar Reddy
# 27  
Old 03-14-2011
Cgkmal,
I removed regexp $0~ & .*_.*/ and got the following output:
Code:
Job Name|Atomic Condition|Dependent Job Name
 Job Name||
 EDW$DAB101_Scheduler01||
 Job Name||
 EDW$DAB102_Trigger01||
 SUCCESS(DWC$DAC111_acct)||
 SUCCESS(DWC$DAC141_cust)||
 SUCCESS(DWC$DAC131_acct_cust_rel)||
 SUCCESS(DWC$DAC134_cust_classfn)||
 Dependent Job Name||
 EDW$DAC101_partnr_shr||
 Job Name||
 EDW$DAB201_Scheduler01||

And the new code just gave the following 1 line result Smilie
Code:
Job Name|Atomic Condition|Dependent Job Name

Regards
Moderator's Comments:
Mod Comment
Once AGAIN!!!
Please use code tags when posting data and code samples!
Last edited by vgersh99; 03-14-2011 at 12:01 PM.. Reason: code tags, please!
# 28  
Old 03-14-2011
Hi GReddy,

Please try exactly with this code and please show the output you receive:
Code:
nawk  'BEGIN{print "Job Name|Atomic Condition|Dependent Job Name"}
/^Job/,/^$/{print $1};/Atomic/,/^$/{print $1};/Dependent Job Name/,/^$/{print $1}' inputfile.txt | 
sed -e 's/^-.*//;s/^_.*$//;/^$/d' | awk '{printf("%s,", $0)}' | 
sed -e 's/^Job,//g;s/,Job,/\n/g;s/,Atomic,/|/g;s/,Dependent,/|/g;s/,$//' | 
awk -F"|" '{print $1,$2,$3}' OFS="|" > outputfile

with inputfile.txt you've uploaded, I receive the next output.
Code:
Job Name|Atomic Condition|Dependent Job Name
ABC$MAN101_Scheduler01||
ABC$MAN102_Trigger01|SUCCESS(PRQ$MAC111_acct),SUCCESS(PRQ$MAC141_cust),SUCCESS(PRQ$MAC131_acct_cust_rel),SUCCESS(PRQ$MAC134_cust_classfn)|ABC$MAC101_partnr_shr
ABC$MAN201_Scheduler01||
ABC$MAN202_Trigger01|SUCCESS(PRQ$MAC211_acct),SUCCESS(PRQ$MAC241_cust),SUCCESS(PRQ$MAC231_acct_cust_rel),SUCCESS(PRQ$MAC234_cust_classfn)|ABC$MAC201_partnr_shr
ABC$MAN401_P_BANK_BOX||
ABC$MAN501_R_BANK_BOX||
ABC$MAC101_partnr_shr|SUCCESS(ABC$MAN102_Trigger01)|

* Please verify your messages inbox Smilie
Best regards
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