Merge lines from one file if pattern matches


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge lines from one file if pattern matches
# 1  
Old 03-10-2010
Merge lines from one file if pattern matches

I have one comma separated file (a.txt) with two or more records all matching except for the last column.
I would like to merge all matching lines into one and consolidate the last column, separated by ":". Does anyone know of a way to do this easily?

I've searched the forum but most talked about two files or other types of merge...

Ex. I have this file a.txt:

Quote:
TEST1,000A,20,1,,_ABC_
TEST1,000A,20,1,,_DEF_
TEST1,000A,20,1,,_GHI_
TEST2,000B,80,,A,_GHI_
TEST2,000B,80,,A,_JKL_
I need output like so:
Quote:
TEST1,000A,20,1,,_ABC_:_DEF_:_GHI_
TEST2,000B,80,,A,_GHI_:_JKL_
Thank you.

Gianni
# 2  
Old 03-10-2010
I only have a start, with awk, hoping it will point you to a good direction:
Code:
awk -F\, 'NR==FNR{a[$1FS$2FS$3FS$4FS$5]=a[$1FS$2FS$3FS$4FS$5]":"$6;next}END{for (i in a) print i, a[i]}' data

# 3  
Old 03-10-2010
Cool. I tried it and it seems to work fine except for the first space then ':' but it gives me what I need:

Quote:
TEST2,000B,80,,A, :_GHI_:_JKL_
TEST1,000A,20,1,, :_ABC_:_DEF_:_GHI_
Thank you!
Gianni
# 4  
Old 03-10-2010
This version makes use of the fact that the input is sorted:
Code:
awk -F, '{e=$NF;$NF=""}p!=$0{if(NR>1)print s;p=s=$0}{s=s":"e}END{print s}' OFS=',' infile

Code:
TEST1,000A,20,1,,:_ABC_:_DEF_:_GHI_
TEST2,000B,80,,A,:_GHI_:_JKL_

# 5  
Old 03-10-2010
Quote:
Originally Posted by tukuyomi
I only have a start, with awk, hoping it will point you to a good direction:
Code:
awk -F\, 'NR==FNR{a[$1FS$2FS$3FS$4FS$5]=a[$1FS$2FS$3FS$4FS$5]":"$6;next}END{for (i in a) print i, a[i]}' data

No space.
Code:
awk -F \, '{a[$1FS$2FS$3FS$4FS$5]=a[$1FS$2FS$3FS$4FS$5]":"$NF } END {for (i in a) print i""a[i]}' data

# 6  
Old 03-12-2010
Awesome. Thanks everyone!
# 7  
Old 03-12-2010
One in perl,

Code:
perl -lne '/(.+,)(.+)/;$h{$1}.= ":$2"; END{ while (($k,$v)=each(%h)) { print "$k $v" ; }  }' file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

find pattern matches in consecutive lines in certain fields-awk

I have a text file with many thousands of lines, a small sample of which looks like this: InputFile:PS002,003 D -1 5 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 6 6 -1 -1 -1 -1 0 509 0 PS002,003 PSQ 0 1 7 18 1 0 -1 1 1 3 -1 -1 ... (5 Replies)
Discussion started by: jvoot
5 Replies

2. Shell Programming and Scripting

Need to merge lines based on pattern

Hi, I have a requirement to merge multiple lines based on search pattern. The search criteria is : it will search for CONSTRAINT and when it found CONSTRAINT, it will merge all lines to 1 line till it founds blank line. For Example: CREATE TABLE "AMS_DISTRIBUTOR_XREF" ( "SOURCE"... (5 Replies)
Discussion started by: satyaatcgi
5 Replies

3. UNIX for Dummies Questions & Answers

merge lines within a file that start with a similar pattern

Hello! i have a text file.. which contains the data as follows i want to merge the declarations lines pertaining to one datatype in to a single line as follows i've searched the forum for help.. but couldn't find much help.. how can i do this?? (1 Reply)
Discussion started by: a_ba
1 Replies

4. Shell Programming and Scripting

script to delete lines from a txt file if pattern matches

File 6 dbnawldb010-b office Memphis_Corp_SQL_Diff Memphis-Corp-SQL-Inc-Application-Backup 03/09/11 03:24:04 42 luigi-b IPNRemitDB Memphis_Corp_SQL_Diff Memphis-Corp-SQL-Inc-Application-Backup 03/10/11 00:41:36 6 ebs-sqldev1-b IPNTracking Memphis_Corp_SQL_Diff... (4 Replies)
Discussion started by: ajiwww
4 Replies

5. Shell Programming and Scripting

NAWK to remove lines that matches a specific pattern

Hi, I have requirement that I need to split my input file into two files based on a search pattern "abc" For eg. my input file has below content abc defgh zyx I need file 1 with abc and file2 with defgh zyx I can use grep command to acheive this. But with grep I need... (8 Replies)
Discussion started by: sbhuvana20
8 Replies

6. Shell Programming and Scripting

Merge lines if pattern matches in ksh

I have a file like this. Pls help me to solve this . (I should look for only Message : 111 and need to print the start time to end time Need to ignore other type of messages. Ex: if first message is 111 and second message is 000 or anything else then ignore the 2nd one and print start time of the... (1 Reply)
Discussion started by: mnjx
1 Replies

7. Shell Programming and Scripting

merge same pattern lines together

Hi people... I normally find with out any problem the solutions I need just by searching. But for this I'm not having any joy or jsut failing to adapt what I'ev found to work. I have applciation report that doesn't allow for manipulation at creation so I want to do some post modifcation... (2 Replies)
Discussion started by: nhatch
2 Replies

8. Shell Programming and Scripting

Merge lines in text file based on pattern

Hello, I have searched forum trying to find a solution to my problem, but could not find anything or I did not understand the examples.... I should say, I am very inexperienced with text processing. I have a text file with approx 60k lines in it. I need to merge lines based on the number... (8 Replies)
Discussion started by: Bertik
8 Replies

9. Shell Programming and Scripting

Displaying lines of a file where the second field matches a pattern

Howdy. I know this is most likely possible using sed or awk or grep, most likely a combination of them together, but how would one go about running a grep like command on a file where you only try to match your pattern to the second field in a line, space delimited? Example: You are... (3 Replies)
Discussion started by: LordJezoX
3 Replies

10. UNIX for Dummies Questions & Answers

How to select lines in unix matches a pattern at a particular position

I have huge file. I want to copy the lines which have first character as 2 or 7, and also which has fist two characters as 90. I need only these records from file. How I can acheive this. Can somebody help me..... (2 Replies)
Discussion started by: cs_banda
2 Replies
Login or Register to Ask a Question