Splitting record into multiple records by appending values from an input field (AWK)


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Splitting record into multiple records by appending values from an input field (AWK)
# 1  
Old 04-24-2012
Splitting record into multiple records by appending values from an input field (AWK)

Hello,

For the input file, I am trying to split those records which have multiple values seperated by '|' in the last input field, into multiple records and each record corresponds to the common input fields + one of the value from the last field.

I was trying with an example on this forum using awk: (split records into multiple records)

Though I understood that example, but has not been able to solve for my case.

I am running on Ubuntu 10.10.

Could you assist me in my problem ?

Thank you!

Input:
Code:
11686 19151 25489 45012
11686 19151 3257 29286 3209|6774|8928|20459|37185
11686 19151 39792 21367 38922 65000
11686 19151 9002 31200 41654|64999|65000|65002|65011|65012|65014|65100|65200|65500

Desired Output:
Code:
11686 19151 25489 45012
11686 19151 3257 29286 3209
11686 19151 3257 29286 6774
11686 19151 3257 29286 8928
11686 19151 3257 29286 20459
11686 19151 3257 29286 37185
11686 19151 39792 21367 38922 65000
....

# 2  
Old 04-24-2012
Hi imtiaz99,

Try with perl. It's a great tool for this kind of tasks:
Code:
$ cat infile
11686 19151 25489 45012
11686 19151 3257 29286 3209|6774|8928|20459|37185
11686 19151 39792 21367 38922 65000
11686 19151 9002 31200 41654|64999|65000|65002|65011|65012|65014|65100|65200|65500
$ perl -lane '
    if ( index( $F[ $#F ], q[|] ) > -1 ) { 
        @f = split /\|/, $F[ $#F ]; 
        for ( @f ) { 
            printf qq[%s %s\n], qq{@F[0..(@F-2)]}, $_ 
        } 
    } 
    else { 
        printf qq[%s\n], $_ 
    }
' infile
11686 19151 25489 45012
11686 19151 3257 29286 3209
11686 19151 3257 29286 6774
11686 19151 3257 29286 8928
11686 19151 3257 29286 20459
11686 19151 3257 29286 37185
11686 19151 39792 21367 38922 65000
11686 19151 9002 31200 41654
11686 19151 9002 31200 64999
11686 19151 9002 31200 65000
11686 19151 9002 31200 65002
11686 19151 9002 31200 65011
11686 19151 9002 31200 65012
11686 19151 9002 31200 65014
11686 19151 9002 31200 65100
11686 19151 9002 31200 65200
11686 19151 9002 31200 65500

This User Gave Thanks to birei For This Post:
# 3  
Old 04-24-2012
Thanks birei for your answer. I haven't worked with Perl before, but I will take your advice as I am working on data analysis and network graphs..
Quote:
If it is possible, Could you provide me some of the details from the code snippet that will help me in logic and semantics ?
Thanks !
# 4  
Old 04-24-2012
Hi

Code:
 sed -e 's/\(.*\) \(.*\)/echo "\1 "{\2}/' -e 's/|/X,/g' File | sh | sed -e 's/X /\n/g' -e 's/[{}]//g'

Guru.
# 5  
Old 04-24-2012
Using awk...
Code:
awk '{n=split($NF,a,/\|/); for(i=1;i<=n;i++){$NF=a[i]; print}}' file1 > file2

This User Gave Thanks to Ygor 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

Script for splitting file of records into multiple files

Hello I have a file of following format HDR 1234 abc qwerty abc def ghi jkl HDR 4567 xyz qwerty abc def ghi jkl HDR 890 mno qwerty abc def ghi jkl HDR 1234 abc qwerty abc def ghi jkl HDR 1234 abc qwerty abc def ghi jkl -Need to split this into multiple files based on tag... (8 Replies)
Discussion started by: wincrazy
8 Replies

2. UNIX for Beginners Questions & Answers

awk GSUB read field values from multiple text files

My program run without error. The problem I am having. The program isn't outputting field values with the column headers to file.txt. Each of the column headers in file.txt has no data. MEMSIZE SECOND SASFoundation Filename The output results in file.txt should show: ... (1 Reply)
Discussion started by: dellanicholson
1 Replies

3. Shell Programming and Scripting

Insert field values in a record using awk command

Hi Friends, Below is my input file with "|" (pipe) as filed delimiter: My Input File: HDR|F1|F2||||F6|F7 I want to inser values in the record for field 4 and field 5. Expected output HDR|F1|F2||F4|F5|F6|F7 I am able to append the string to the end of the record, but not in between the... (3 Replies)
Discussion started by: Ajay Venkatesan
3 Replies

4. Shell Programming and Scripting

Read record from the text file contain multiple separated values & assign those values to variables

I have a file containing multiple values, some of them are pipe separated which are to be read as separate values and some of them are single value all are these need to store in variables. I need to read this file which is an input to my script Config.txt file name, first path, second... (7 Replies)
Discussion started by: ketanraut
7 Replies

5. Shell Programming and Scripting

Multiple Records from 1 Record

I need to make one record to multiple records based on occurence column in the record and change the date.For example below first record has 5 ,so need to create 5 records from one and change the date to 5 months.Occurence can be any number. I am unable to come with a script.Can some one help ... (5 Replies)
Discussion started by: traininfa
5 Replies

6. Shell Programming and Scripting

SED/AWK to edit/add field values in a record

Hi Experts, I am new to shell scripting. Need some help in doing one task given by the customer. The sample record in a file is as follows: 3538,,,,,,ID,ID1,,,,,,,,,,, It needs to be the following: 3538,,353800,353800,,,ID,ID1,,,,,COLX,,,,,COLY, And i want to modify this record in... (3 Replies)
Discussion started by: sugarcane
3 Replies

7. Shell Programming and Scripting

awk + gsub to search multiple input values & replace with located string + extra text

Hi all. I have the following command that is successfully searching for any one of the strings on all lines of a file and replacing it with the instructed value. cat inputFile | awk '{gsub(/aaa|bbb|ccc|ddd/,"1234")}1' > outputFile This does in fact replace any occurrence of aaa, bbb,... (2 Replies)
Discussion started by: dazhoop
2 Replies

8. Shell Programming and Scripting

awk - splitting 1 large file into multiple based on same key records

Hello gurus, I am new to "awk" and trying to break a large file having 4 million records into several output files each having half million but at the same time I want to keep the similar key records in the same output file, not to exist accross the files. e.g. my data is like: Row_Num,... (6 Replies)
Discussion started by: kam66
6 Replies

9. Shell Programming and Scripting

Splitting input files into multiple files through AWK command

Hi, I needs to split *.txt files from single directory depends on the some mutltiple input values. i have wrote the code like below for file in *.txt do grep -i -h "value1|value2" $file > $file; done. My requirment is more input values needs to be given in grep; let us say 50... (3 Replies)
Discussion started by: arund_01
3 Replies

10. Shell Programming and Scripting

Multiple input field Separators in awk.

I saw a couple of posts here referencing how to handle more than one input field separator in awk. I figured I would share how I (just!) figured out how to turn this line in a logfile: 90000000000000000000010001 name... (4 Replies)
Discussion started by: kinksville
4 Replies
Login or Register to Ask a Question

Featured Tech Videos