Parse key-value pair into separate rows


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Parse key-value pair into separate rows
# 1  
Old 11-25-2012
Parse key-value pair into separate rows

Hi,

I'm getting key-value pairs in a string as follows -

Code:
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747,9700005405717924,9700005405733788|unidentified,unidentified,unidentified

I need output as follows -

Code:
row1:
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747|unidentified

row2:
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405717924|unidentified

row3:
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405733788|unidentified

There are more columns but I have pasted only two.

Any help is greatly appreciated.

Thanks,
Suyog

Last edited by Scrutinizer; 11-25-2012 at 03:56 PM.. Reason: code tags
# 2  
Old 11-25-2012
Will this work for you?
Code:
awk -F'[\|,]' ' { printf "%s|%s|%s|%s\n%s|%s|%s|%s\n%s|%s|%s|%s\n",$1,$2,$3,$6,$1,$2,$4,$7,$1,$2,$5,$8 }' infile

# 3  
Old 11-25-2012
That works thanks.

but, is there a way to generalize it ? there's no fixed number of key-value pairs.
# 4  
Old 11-25-2012
Generalized:-
Code:
awk -F'[\|,]' ' { f=3; s=(NF/2)+2; while(s<=NF) { printf "%s|%s|%s|%s\n",$1,$2,$f,$s; f++;s++; }  } ' infile


Last edited by Yoda; 11-25-2012 at 05:42 PM..
This User Gave Thanks to Yoda For This Post:
# 5  
Old 11-26-2012
Thanks Bipin.

I'm building on it to meet my requirement. I will keep you posted thanks.
# 6  
Old 12-01-2012
Code:
$ cat t1
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747,9700005405717924,9700005405733788|unidentified,unidentified,unidentified
0000xm7zcNDIkP888vRqGvZZZZZZZZZZZZZZZ||9700005405552747,9700005405717924,9700005405733788|unidentified,unidentified,unidentified
0000xm7zcNDIkP888vRqGvZZZZZZZZZZZZZZZ||9700005405717924,9700005405733788|unidentified,unidentified,unidentified
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747,9700005405717924,9700005405733788|unidentified,unidentified,unidentified
$ awk -F"|" '{a=$1
m=split($(NF-1),V,",")
n=split($NF,T,",")
if(n!=m){print "ERROR-File[" FILENAME "] -line[" NR "] : Number of field inconsistent";exit}
else{i=0
while(++i<=m){print "row"(++d)":" RS a FS FS V[i] FS T[i]}
}}' t1
row1:
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747|unidentified
row2:
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405717924|unidentified
row3:
0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405733788|unidentified
row4:
0000xm7zcNDIkP888vRqGvZZZZZZZZZZZZZZZ||9700005405552747|unidentified
row5:
0000xm7zcNDIkP888vRqGvZZZZZZZZZZZZZZZ||9700005405717924|unidentified
row6:
0000xm7zcNDIkP888vRqGvZZZZZZZZZZZZZZZ||9700005405733788|unidentified
ERROR-File[t1] -line[3] : Number of field inconsistent
$

If you don't want to exit when encountering an inconsistency in the number of field you can just replace the "exit" with "next" to skip the erroneous line.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert rows to columns based on key and count

Team, I am having requirement to convert rows to columns Input is: key ,count, id1, pulse1, id2, pulse2 ,id3, pulse3 12, 2 , 14 , 56 , 15, 65 13, 3, 12, 32, 14, 23, 18, 54 22, 1 , 32, 42 Expected Out put: key, id,pulse 12, 14, 56 12, 15, 65 13 ,12, 32 13, 14 ,23 13, 18 ,54 22 ,32,... (3 Replies)
Discussion started by: syam1406
3 Replies

2. Shell Programming and Scripting

How to separate rows of data into another column?

I have data such as below where the value in second field is the same as that in the row after. 123456,22222,John,0,xyz 234567,22222,John1,1,cde 43212,3333,Jean,3,pip 84324,3333,Abel,2,cat I'd like to rearrange the output like below to put such records beside each other and separated with... (5 Replies)
Discussion started by: james2009
5 Replies

3. Shell Programming and Scripting

How to parse parts of 1 column into two separate columns?

I have a shell script that is currently transferring a csv file from a server into a Teradata database table. One of the 30 or so columns is called "destination_url". In that URL there are parameters, and it is possible for those parameters to be repeated because of referring companies copying... (3 Replies)
Discussion started by: craigwg
3 Replies

4. Shell Programming and Scripting

Line up Print outs from Separate Rows

A text file has two logs of same event & both logs have to be correlated into a single line before same can be processed further RAW RunningLog Date Month Year Time Event 9 JUN 2013 1932 2 10 JAN 2014 1000 4 Duration Closed ... (10 Replies)
Discussion started by: newageBATMAN
10 Replies

5. UNIX for Advanced & Expert Users

Parse (delimited string) key-value pairs in a column into separate lines

Hi experts, e.g. i/p data looks like 0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747,9700005405717924,9700005405733788|unidentified,unidentified,unidentified|| o/p data should like - row1: 0000xm7zcNDIkP888vRqGv93xA7:176n00qql||9700005405552747|unidentified ... (1 Reply)
Discussion started by: sumoka
1 Replies

6. Shell Programming and Scripting

Bash script to create rsa key pair

Hello all, I am setting up a cluster of Mac Pro's which need to be able to talk to a master computer, traffic between the nodes and the master needs to take place without a ssh key. I need a script that will create a security key, save it to the default place, enter the password as no password.... (2 Replies)
Discussion started by: sdl27789
2 Replies

7. UNIX for Dummies Questions & Answers

How to separate a single column file into files of the same size (i.e. number of rows)?

I have a text file with 1,000,000 rows (It is a single column text file of numbers). I would like to separate the text file into 100 files of equal size (i.e. number of rows). The first file will contain the first 10,000 rows, the second row will contain the second 10,000 rows (rows 10,001-20,000)... (2 Replies)
Discussion started by: evelibertine
2 Replies

8. Shell Programming and Scripting

Parse a File ColumnWise & Delete the Rows

Hi , I have a CSV file that has around 50K records in it. I have to delete all the duplicate rows from the file except one, depending upon data in column4. Lets say there are 3 rows for 'column4data' in the file. I have to retain only that line which has the smallest date value in column2.... (3 Replies)
Discussion started by: Sheel
3 Replies

9. UNIX for Dummies Questions & Answers

forming duplicate rows based on value of a key

if the key (A or B or ...others) has 4 in its 3rd column the 1st A row has to form 4 dupicates along with the all the values of A in 4th column (2.9, 3.8, 4.2) . Hope I explain the question clearly. Cheers Ruby input "A" 1 4 2.9 "A" 2 5 ... (7 Replies)
Discussion started by: ruby_sgp
7 Replies

10. Shell Programming and Scripting

automating RSA key pair generation

I want to automate the process of generating RSA keys. I want to remotley login to a linux machine from a windows maching without having to enter a password. For this I need to generate the RSA key pair. but I want to do this procedure on alot of linux machines. For which I was looking to automate... (2 Replies)
Discussion started by: lassimanji
2 Replies
Login or Register to Ask a Question