Conversion of rows to columns using awk based om column value


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Conversion of rows to columns using awk based om column value
# 1  
Old 04-29-2016
Conversion of rows to columns using awk based om column value

HI,
My Input file data is

Code:
dn:adcfgeneral
id:13343
Name:xxxxxx
Password:iutyerwuitywue wpuwt
tuiytruityrutyrwtyrwp
dn:cdferwjyyyy[/COLOR]
id:3875
Name:yyyy
Password :hgfdsjkfhdsfkdlshf
dshfkldshfdklsfh
interset:uiuiufj

My output should be

Code:
dn:adcfgeneral|id:13343|Name:xxxxxx|Password:iutyerwuitywue wpuwttuiytruityrutyrwtyrwp
dn:cdferwjyyyy|id:3875|Name:yyyy|Password :hgfdsjkfhdsfkdlshfdshfkldshfdklsfh|interset:uiuiufj

The data between dn should converted to rows
Moderator's Comments:
Mod Comment Please use CODE tags to display sample input, sample output, and code segments as required by forum rules.

Last edited by Don Cragun; 04-29-2016 at 03:51 PM.. Reason: Add CODE tags.
# 2  
Old 04-29-2016
I'm not sure why you have a closing COLOR tag in your input file nor why it shouldn't be copied to your output file, but ignoring that, the following seems to do what you want:
Code:
awk '
/^dn:/ {printf("%s%s", (NR > 1) ? "\n" : "", $0)
	next
}
{	printf("%s%s", (sub(/:/, "&")) ? "|" : "", $0)
}
END {	print ""
}' file

which, if file contains your sample input, produces the output:
Code:
dn:adcfgeneral|id:13343|Name:xxxxxx|Password:iutyerwuitywue wpuwttuiytruityrutyrwtyrwp
dn:cdferwjyyyy[/COLOR]|id:3875|Name:yyyy|Password :hgfdsjkfhdsfkdlshfdshfkldshfdklsfh|interset:uiuiufj

As always, if you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.
This User Gave Thanks to Don Cragun For This Post:
# 3  
Old 04-29-2016
Hello dineshaila,

Welcome to forums, please use code tags for codes/commands/Inputs which you are using into your posts as per forum rules. Coming onto your question, couldn't understand by which logic string [/COLOR] has been removed from output shown by you. If this is typo then following may help you in same.
Code:
awk '($0 ~ /^dn:/ && A){print A;A="";} {A=A?A OFS $0:$0} END{print A}' OFS="|"   Input_file

Output will be as follows.
Code:
dn:adcfgeneral|id:13343|Name:xxxxxx|Password:iutyerwuitywue wpuwt|tuiytruityrutyrwtyrwp
dn:cdferwjyyyy[/COLOR]|id:3875|Name:yyyy|Password :hgfdsjkfhdsfkdlshf|dshfkldshfdklsfh|interset:uiuiufj

If above output doesn't meet your requirement or you have any additional information to your requirement, please do show us sample Input_file with expected sample output too. Hope this helps you.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 4  
Old 04-29-2016
Hi Don/Ravinder,

Thanks for the reply.
Could you please expalin how the code works
# 5  
Old 04-29-2016
Are these added comments enough to explain what my script does, or do you still not understand how something works?
Code:
awk '
/^dn:/ {# For input lines starting with "dn:":
	# if this is not the 1st input line, add a newline to previous output;
	# then, in either case, add the current input line (without the
	# terminating newline character) as the start of a new output line.
	printf("%s%s", (NR > 1) ? "\n" : "", $0)
	# Skip to next input line.
	next
}
{	# For other input lines:
	# If the line contains a colon, add "|" as a field separator and add the
	# current input line (withut the terminating newline character) to the
	# current output line; otherwise, just add the current input line
	# (without the terminating newline character) to the current output
	# line.
	printf("%s%s", (sub(/:/, "&")) ? "|" : "", $0)
}
END {	# When we hit EOF on the input file, add a trailing newline to the last
	# output line.
	print ""
}' file

# 6  
Old 04-29-2016
Quote:
Originally Posted by dineshaila
Hi Don/Ravinder,
Thanks for the reply.
Could you please expalin how the code works
Hello dineshaila,

Following may help you in same.
Code:
awk '($0 ~ /^dn:/ && A){        ######## Checking here if any line is getting started with string "dn:" AND variable A is NOT NULL values in it. Where variable A is the one where I am storing all the lines values in it.
print A;                        ######## print the value of variable A then.
A="";}                          ######## Nullify the variable A's value now.
{A=A?A OFS $0:$0}               ######## Here each line I am appending the value of A with the current line's value in it.
END{print A}'                   ######## END is a awk's default section where we will perform the tasks which want to perform at last, so I am printing variable A's value here too.
OFS="|" Input_file              ######## Setting up output field separator's value to | here as per your requirement. Mentioning the Input_file name too.

Thanks,
R. Singh
# 7  
Old 04-29-2016
Thanks for the clear response
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk split columns after matching on rows and summing the last column

input: chr1 1 2 3 chr1 1 2 4 chr1 2 4 5 chr2 3 6 9 chr2 3 6 10 Code: awk '{a+=$4}END{for (i in a) print i,a}' input Output: chr112 7 chr236 19 chr124 5 Desired output: chr1 1 2 7 chr2 3 6 19 chr1 2 4 5 (1 Reply)
Discussion started by: jacobs.smith
1 Replies

2. Solaris

Rows to column conversion in Solaris

i am using the command pkginfo -l | /usr/xpg4/bin/grep -e 'VENDOR' -e 'NAME' -e 'VERSION' >> /tmp/test1.txt in order to get name,vendor and version of the applications installed on Solaris machine.The output stored in test1.txt in as shown below: NAME: Solaris Zones (Usr) VERSION: ... (4 Replies)
Discussion started by: omkar.jadhav
4 Replies

3. Shell Programming and Scripting

Convert rows to columns based on condition

I have a file some thing like this: GN Name=YWHAB; RC TISSUE=Keratinocyte; RC TISSUE=Thymus; CC -!- FUNCTION: Adapter protein implicated in the regulation of a large CC spectrum of both general and specialized signaling pathways GN Name=YWHAE; RC TISSUE=Liver; RC ... (13 Replies)
Discussion started by: raj_k
13 Replies

4. UNIX for Dummies Questions & Answers

Columns to rows conversion in unix

Hi All, My requirement is to convert the rows to columns, please help me how to do in one command. Ex: source file is having data like ABC,XYZ,123,987,KKK,XXX,666 Need output like ABC XYZ 987 KKK XXX 666 Regards, Pavan. (3 Replies)
Discussion started by: madsongtel
3 Replies

5. Shell Programming and Scripting

Please Help!!!! Awk for summing columns based on selected column value

a,b,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,bb,cc,dd,ee,ff,gg,hh,ii a thru ii are digits and strings.... The awk needed....if coloumn 9 == i (coloumn 9 is string ), output the sum of x's(coloumn 22 ) in all records and sum of y's (coloumn 23 ) in all records in a file (records.txt).... (6 Replies)
Discussion started by: BrownBob
6 Replies

6. UNIX for Dummies Questions & Answers

merging rows into new file based on rows and first column

I have 2 files, file01= 7 columns, row unknown (but few) file02= 7 columns, row unknown (but many) now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there e.g. file 01 James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies

7. Shell Programming and Scripting

Skipping rows based on columns

Hi, suppose I have the following file and certain rows have missing columns, how do i skip these rows and create an output file which has all the columns in it E/N Ko_exp %err Ko_calc %err diff diff- diff+ 0.95 ======== ======= ==== ======= ==== ===== ===== =====... (12 Replies)
Discussion started by: ramky79
12 Replies

8. UNIX for Dummies Questions & Answers

conversion and transpositions of columns and rows

Hi, I am wondering how to do this more efficiently than I am currently doing it: I have these 3 columns 3315 E14 1 397 E14 2 321 E14 3 27 E14 4 7 E14 5 2 E14 6 But I like them to be displayed like this: 1 2 ... (5 Replies)
Discussion started by: danieladna
5 Replies

9. Shell Programming and Scripting

Arrange output based on rows into columns

Hi All, I would like to ask help on how can i achieve below output. Inputfile: Oct11,apa1-daily,01:25:01 Oct11,apa2-daily,01:45:23 Oct12,apa1-daily,02:30:11 Oct12,apa2-daily,01:55:01 Oct13,apa1-off,01:43:34 Oct13,apa2-off,01:22:04 Desired output: Clients ... (3 Replies)
Discussion started by: mars101
3 Replies

10. Shell Programming and Scripting

awk to select rows based on condition on column

I have got a file like this 003ABC00281020091005000100042.810001 ... (8 Replies)
Discussion started by: Maruti
8 Replies
Login or Register to Ask a Question