Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Converting Single Column into Multiple rows, but with strings to specific tab column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Converting Single Column into Multiple rows, but with strings to specific tab column
# 1  
Old 07-29-2013
Converting Single Column into Multiple rows, but with strings to specific tab column
Dear fellows, I need your help.

I'm trying to write a script to convert a single column into multiple rows.
But it need to recognize the beginning of the string and set it to its specific Column number.
Each Line (loop) begins with digit [0-9] (RANGE).

At this moment it's kind of working, but it is skipping a line each time [in red] (double loop I think).

Would ready appreciate your help. fine tuning this one or simpler version.
This one is an example of the real file.

Thanks



x.txt
Code:
20201
OBB-9
BO-11
VER-2
CBAA-80
CBAZA-70
20205
OBB-10
BO-11
VER-2
CBAA-88
CBAZA-66
20209
OBB-8
BO-11
VER-2
MVER-2
PER-2
CBAA-44
CBAZA-25
20210
OBB-8
BO-11
VER-2
MVER-2
PER-2
CBAA-41
CBAZA-22
202111
OBB-9
BO-11
VER-2
ALP-11
CBAA-88
CBAZA-66


output.txt
Code:
Range	OBB	BO	VER	MVER	PER	CBAA	CBAZA	ALP
20201	OBB-9	BO-11	VER-2	             CBAA-44	CBAZA-25
20205	OBB-10	BO-11	VER-2		         CBAA-88  CBAZA-66
20209 	OBB-8	BO-11 	VER-2 	MVER-2 	PER-2 	CBAA-44 CBAZA-25 
20210 	OBB-8 	BO-11 	VER-2 	MVER-2 	PER-2 	CBAA-41 CBAZA-22  
20211 	OBB-9 	BO-11 	VER-2  		         CBAA-88 CBAZA-66 ALP-11



awk.awk
Code:
BEGIN {
# Print the Headers
printf "%s", "RANGE\t"
printf "%s", "BBO\t"
printf "%s", "BO\t"
printf "%s", "VER\t"
printf "%s", "MVER\t"
printf "%s", "PER\t"
printf "%s", "CBAA\t"
printf "%s", "CBAZA\t"
printf "%s", "ALP\r\n"

}

# Loop every line with digit [0-9]
$1 ~ /^[0-9]/ {

# For every loop clean empty column value
vRANGE = ""
vOBB = ""
vBO = ""
vVER = ""
vMVER = ""
vPER = ""
vCBAA = ""
vCBAZA = ""
vALP = ""

#set vRANGE to current RANGE 
vRANGE = $1

# set column counter to 1
i = 1
# interate to columns with max of 20
while (i <= 20) {
	# read columns value for current RANGE
	getline
	
	# if value is a number than we have hit a new RANGE, stop processing
	if ( $1 ~ /^[0-9]/ ) {
		# kick column counter to column max
		i = 20
	} else {
		# increase column counter
		i++
	}
	
	
	# set specific column values
	
		#vOBB
		if ( $1 ~ /^OBB-/ ) {
			vOBB = $1
		}
		#vBO 
		if ( $1 ~ /^BO-/ ) {
			vBO = $1
		}
		#vVER
		if ( $1 ~ /^VER-/ ) {
			vVER = $1
		}
		#vMVER
		if ( $1 ~ /^MVER-/ ) {
			vMVER = $1
		
		##vPER
		if ( $1 ~ /^PER-/ ) {
			vPER = $1
		}
		##CBAA
		if ( $1 ~ /^CBAA-/ ) {
			vCBAA = $1
		}
		#vCBAZA
		if ( $1 ~ /^CBAZA-/ ) {
			vCBAZA = $1
		}
		#vALP
		if ( $1 ~ /^ALP-/ ) {
			vALP = $1
		}

}

# Display all the columns for the current RANGE
printf "%s\t", vRANGE
printf "%s\t", vOBB
printf "%s\t", vBO
printf "%s\t", vVER
printf "%s\t", vMVER
printf "%s\t", vPER
printf "%s\t", vCBAA
printf "%s\t", vCBAZA
printf "%s\t", vALP
printf "\r\n"

}

Last edited by AK47; 07-29-2013 at 01:38 PM..
# 2  
Old 07-29-2013
Code:
20201
OBB-9
BO-11
VER-2
CBAA-80
CBAZA-70

How does this become:
Code:
20201	OBB-9	BO-11	VER-2	MVER-2	PER-2	CBAA-44	CBAZA-25

I do not see MVER-2 PER-2 for 20201
# 3  
Old 07-29-2013
ohh sorry you're right. bad copy paste

Code:
Range	OBB	BO	VER	MVER	PER	CBAA	CBAZA	 ALP
20201	OBB-9	BO-11   VER-2	                CBAA-44	CBAZA-25

---------- Post updated at 02:21 PM ---------- Previous update was at 12:38 PM ----------

no body ? SmilieSmilie
# 4  
Old 07-29-2013
Try this if it fits your needs:
Code:
awk     'function printall (RES) {
                         for (i=1; i<=8; i++) printf "%s\t", RES[i]; printf "%s\n", RES[9]
                        }
         NR==1          {split ("RANGE BBO BO VER MVER PER CBAA CBAZA ALP", RES)}

         $0==$0+0       {printall (RES)
                         delete RES
                         RES[1]=$0}
         /^OBB-/        {RES[2]=$1}
         /^BO-/         {RES[3]=$1}
         /^VER-/        {RES[4]=$1}
         /^MVER-/       {RES[5]=$1}
         /^PER-/        {RES[6]=$1}
         /^CBAA-/       {RES[7]=$1}
         /^CBAZA-/      {RES[8]=$1}
         /^ALP-/        {RES[9]=$1}
         END            {printall (RES)}
        ' file
RANGE   BBO     BO      VER     MVER    PER     CBAA    CBAZA   ALP
20201   OBB-9   BO-11   VER-2                   CBAA-80 CBAZA-70
20205   OBB-10  BO-11   VER-2                   CBAA-88 CBAZA-66
20209   OBB-8   BO-11   VER-2   MVER-2  PER-2   CBAA-44 CBAZA-25
20210   OBB-8   BO-11   VER-2   MVER-2  PER-2   CBAA-41 CBAZA-22
202111  OBB-9   BO-11   VER-2                   CBAA-88 CBAZA-66        ALP-11

This User Gave Thanks to RudiC For This Post:
AK47
# 5  
Old 07-29-2013
Tnx.
But I'm receiving this error Smilie


Code:
[~/scripts/test]$ awk -f format.awk
awk: format.awk:1: awk     'function printall (RES) {
awk: format.awk:1:         ^ invalid char ''' in expression
awk: format.awk:1: awk     'function printall (RES) {
awk: format.awk:1:         ^ syntax error

# 6  
Old 07-29-2013
try also:
Code:
awk '
BEGIN {
   cols="RANGE,OBB,BO,VER,MVER,PER,CBAA,CBAZA,ALP";
   cc=split(cols, oc, ",");
   for (j=1; j<=cc; j++) printf oc[j] (j<cc ? "\t":"\n");
}
{if ($1 ~ /^[0-9]+$/) {
   if (d[oc[1]]) for (j=1; j<=cc; j++) { printf d[oc[j]] (j<cc ? "\t":"\n"); delete d[oc[j]] ; }
   d[oc[1]]=$1;
 } else {
   ln=$0; sub("[-].*", "", ln); d[ln]=$0;
 }
}
END { for (j=1; j<=cc; j++) printf d[oc[j]] (j<cc ? "\t":"\n"); }
' infile

Last edited by rdrtx1; 07-30-2013 at 05:41 PM.. Reason: corrected clearing stored values.
This User Gave Thanks to rdrtx1 For This Post:
AK47
# 7  
Old 07-30-2013
RudiC & rdrtx1 @ Thank man...really helpful!! There both working Smilie Hardcore sripting SmilieSmilieSmilie.

---------- Post updated at 03:53 PM ---------- Previous update was at 08:31 AM ----------

Quote:
Originally Posted by rdrtx1
try also:
Code:
awk '
BEGIN {
   cols="RANGE,OBB,BO,VER,MVER,PER,CBAA,CBAZA,ALP";
   cc=split(cols, oc, ",");
   for (j=1; j<=cc; j++) printf oc[j] (j<cc ? "\t":"\n");
}
{if ($1 ~ /^[0-9]+$/) {
   if (d[oc[1]]) for (j=1; j<=cc; j++) printf d[oc[j]] (j<cc ? "\t":"\n");
   d[oc[1]]=$1;
 } else {
   ln=$0; sub("[-].*", "", ln); d[ln]=$0;
 }
}
END { for (j=1; j<=cc; j++) printf d[oc[j]] (j<cc ? "\t":"\n"); }
' infile

@ rdrtx1 : there's a small issue. It doesn't clear the column variable after every loop.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Converting Single Column into Multiple rows

Hi .. anyone can you help me ? i need to convert text below into multiple columns interface; GigabitEthernet0/0/0/0 description; TRUNK_PE-D2-JT2-VPN_Gi0/0/0/0_TO_ME4-A-JKT-JT_4/1/1_1G mtu 9212 negotiation auto interface; GigabitEthernet0/0/0/0.11 description; tes encapsulation;... (1 Reply)
Discussion started by: mad3linux
1 Replies

2. Shell Programming and Scripting

Converting a single row to multiple rows

Hi, I want to convert a single row values to multiple rows, but the no. of rows are not fixed. For example, I have a row as below abc-def-lmn-mno-xyz out put should be get abc get def get lmn get xyz (4 Replies)
Discussion started by: Suneel Mekala
4 Replies

3. Shell Programming and Scripting

Convert single column into multiple rows

Convert Single column to multiple rows file a.txt contains data like below Server=abc Run=1 Tables=10 Sessions=16 Time=380 Jobs=5 Server=abc Run=2 Tables=15 Sessions=16 Time=400 Jobs=5 Server=abc Run=3 Tables=20 Sessions=16 Time=450 (5 Replies)
Discussion started by: sol_nov
5 Replies

4. Shell Programming and Scripting

Transpose multiple rows (with a mix of space and enter) to a single column

How to change the uploaded weekly file data to the following format? New Well_Id,Old Well_Id,District,Thana,Date,Data,R.L,WellType,Lati.,Longi. BAG001,PT006,BARGUNA,AMTALI,1/2/1978,1.81,2.29,Piezometer,220825,901430 BAG001,PT006,BARGUNA,AMTALI,1/9/1978,1.87,2.29,Piezometer,220825,901430... (3 Replies)
Discussion started by: sara.nowreen
3 Replies

5. UNIX for Dummies Questions & Answers

[SOLVED] splitting a single column(with spaces) into multiple rows

Hi All, My requisite is to split a single column of phonemes seperated by spaces into multiple rows. my input file is: a dh u th a qn ch A v U r k my o/p should be like: adhu a dh u (3 Replies)
Discussion started by: girlofgenuine
3 Replies

6. Shell Programming and Scripting

How to merge multiple rows into single row if first column matches ?

Hi, Can anyone suggest quick way to get desired output? Sample input file content: A 12 9 A -0.3 2.3 B 1.0 -4 C 34 1000 C -111 900 C 99 0.09 Output required: A 12 9 -0.3 2.3 B 1.0 -4 C 34 1000 -111 900 99 0.09 Thanks (3 Replies)
Discussion started by: cbm_000
3 Replies

7. UNIX for Dummies Questions & Answers

Converting column to rows for every 3 lines in the column

Hi gurus! Please help me with this one. I have an file with the following contents: a b c d e f g h i j I would like to make to transform it to look like this as my output file: a,b,c d,e,f (4 Replies)
Discussion started by: kokoro
4 Replies

8. Shell Programming and Scripting

Converting rows to column

i have output of script as below name,roll_no,01-05-12,02-05-12,03-05-12 sam,12,24,24,24 jon,145,24,24,22 van,29,24,22,24 i want to convert these into columns as output is not fixed please tell me how to convert 1st row in to 1st columns likewise,as many rows are there are to be converted... (4 Replies)
Discussion started by: sagar_1986
4 Replies

9. Shell Programming and Scripting

Single column into multiple rows

Hi all, I need to convert this file having just one column into two column file current file: a 15 b 21 c 34 d 48 e 10 wanted: a 15 b 21 c 34 (15 Replies)
Discussion started by: prachiagra
15 Replies

10. Shell Programming and Scripting

Converting Single Column into Multiple rows

i have single column which is starting with same string(many number of rows) i have to convert each into a single row.how can i do that? laknar std mes 23 55 laknar isd phone no address amount 99 I have to convert above like below. laknar|std|mes|23|55 laknar|isd|phone... (3 Replies)
Discussion started by: laknar
3 Replies
Login or Register to Ask a Question