Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Merge data in lines from same file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge data in lines from same file
# 1  
Old 03-13-2017
Merge data in lines from same file
Need help figuring out how to merge data from a file. I have a large txt file with some data that needs to be merged from separate lines into one line.

Code:
Doug.G|3/12/2011|817-555-5555|Portland
Doug.G|3/12/2011|817-555-5522|Portland
Steve.F|1/11/2007|817-555-5111|Portland
Eric.W|2/17/1999|817-555-5001|Seattle
Eric.W|2/17/1999|902-555-5999|Seattle
Eric.W|2/17/1999|718-444-5111|Seattle

I need to combine column 3 in a |#,#| format on lines that have a match on column 1.

Desired
Code:
Doug|3/12/2011|817-555-5555,817-555-5522|Portland
Steve|1/11/2007|817-555-5111|Portland
Eric|2/17/1999|817-555-5001,902-555-5999,718-444-5111|Seattle

I can awk and tell me there is a difference on each line but can not figure out how to perform the desired merged output. There are some entries where three lines as in Eric or none as Steve, if there where all dups I could sort and cut | merge on next line.
# 2  
Old 03-13-2017
Hi, try:
Code:
awk '{i=$1 FS $2} !B[$0]++{A[i]=A[i] (A[i]?",":x) $3; C[i]=$4} END{for(i in A) print i,A[i],C[i]}' FS=\| OFS=\| file


--
Code:
awk '
  {
    i=$1 FS $2                     # Set index to $1 "|" $2
  }
  !B[$0]++ {                       # Only execute if line has not occurred before. Discard duplicate lines, using independent array B 
    A[i]=A[i] (A[i]?",":x) $3      # Add $3 to the string if A[i] is not empty, the prepend a comma else prepend nothing (x) 
    C[i]=$4                        # Add the fourth field to a entry with index i in separate array C
  }
  END {
    for(i in A) print i,A[i],C[i]  # For all found indices where the entire line is no duplicate, print the index "|" concatenated field 3 "|" C[i]
  }
' FS=\| OFS=\| file

Output:
Code:
Eric.W|2/17/1999|817-555-5001,902-555-5999,718-444-5111|Seattle
Doug.G|3/12/2011|817-555-5555,817-555-5522|Portland
Steve.F|1/11/2007|817-555-5111|Portland

Last edited by Scrutinizer; 03-13-2017 at 11:33 AM..
This User Gave Thanks to Scrutinizer For This Post:
cdubu2
# 3  
Old 03-13-2017
Hello cdubu2,

Could you please try following.
Code:
awk -F"|" 'FNR==NR{A[$1]=A[$1]?A[$1] "," $3:$3;next} ($1 in A){$3=A[$1];print;delete A[$1]}' OFS="|"   Input_file  Input_file

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
cdubu2
# 4  
Old 03-13-2017
Thank you for the quick replies. These all worked on my Ubuntu machine however none work on my Solaris system, must be a syntax difference.
# 5  
Old 03-13-2017
Quote:
Originally Posted by cdubu2
Thank you for the quick replies. These all worked on my Ubuntu machine however none work on my Solaris system, must be a syntax difference.
Hello cdubu2,

On a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk, it should fly then.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
cdubu2
# 6  
Old 03-13-2017
nawk did it, thank you Thank to all the replies!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to merge the multiple data files as a single file?

Hi Experts, I have created multiple scripts and send the output to new file, getting this output to my mailbox on daily basis. I would like to send the all outputs to a single file, need to merge all file outputs on a single file. For example, Created script for df -h > df.doc grep... (7 Replies)
Discussion started by: seenuvasan1985
7 Replies

2. UNIX for Dummies Questions & Answers

Need help combining txt files w/ multiple lines into csv single cell - also need data merge

:confused:Hello -- i just joined the forums. I am a complete noob -- only about 1 week into learning how to program anything... and starting with linux. I am working in Linux terminal. I have a folder with a bunch of txt files. Each file has several lines of html code. I want to combine... (2 Replies)
Discussion started by: jetsetter
2 Replies

3. Shell Programming and Scripting

Merge the data from two servers into a single file

Hi All, Need your inputs for the below. I have 2 different servers 611 & 610, where i would be running two scripts. And would would be running one script from 611 at every 4 hours to merge the data from the 2 servers into 2 files and send a mail. so below is the code snippet for 611: ... (3 Replies)
Discussion started by: ss_ss
3 Replies

4. UNIX for Dummies Questions & Answers

Merge rows in bid data file

Dear all, Please help me ,,,, if I have input file like this A_AA960715 leucine-rich repeat-containing protein GO:0006952 defense response P A_AA960715 leucine-rich repeat-containing protein GO:0008152 metabolic process P A_AA960715 leucine-rich... (5 Replies)
Discussion started by: AAWT
5 Replies

5. Shell Programming and Scripting

Merge file lines based off of keyword

Hello Everyone, I have two files I created in a format similar to the ones found below (character position is important): File 1: 21 Cat Y N S Y Y N N FOUR LEGS TAIL WHISKERS 30 Dog N N 1 Y Y N N FOUR LEGS TAIL 33 Fish Y N 1 Y Y N N FINS 43 CAR Y N S Y Y N N WHEELS DOORS... (7 Replies)
Discussion started by: jl487
7 Replies

6. Shell Programming and Scripting

Extracting specific lines of data from a file and related lines of data based on a grep value range?

Hi, I have one file, say file 1, that has data like below where 19900107 is the date, 19900107 12 144 129 0.7380047 19900108 12 168 129 0.3149017 19900109 12 192 129 3.2766666E-02 ... (3 Replies)
Discussion started by: Wynner
3 Replies

7. Shell Programming and Scripting

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... (6 Replies)
Discussion started by: giannicello
6 Replies

8. Shell Programming and Scripting

Using Perl to Merge Multiple Lines in a File

I've hunted and hunted but nothing seems to apply to what I need. Any help will be much appreciated! My input file looks like (Unix): marker,allele1,allele2 RS1002244,1,1 RS1002244,1,3 RS1002244,3,3 RS1003719,2,2 RS1003719,2,4 RS1003719,4,4 Most markers are listed 3 times but a few... (2 Replies)
Discussion started by: Peggy White
2 Replies

9. Shell Programming and Scripting

Merge 2 lines in file

Hi All, I have a data in flat file like below. Some of the information are in second row. 111_ABCProcess ----- ----- IN 0/0 111_PQRTrimPRocess ----- ----- OI 0/0 111_ZigZagTrimProcess ----- ----- ... (1 Reply)
Discussion started by: Amit.Sagpariya
1 Replies

10. Shell Programming and Scripting

merge multiple lines from flat file

Hi, I have a tab delimited flat file like this: 189 Guide de lutilisateur sur lappel conférence à trois au moyen d'adaptateurs téléphoniques <TABLE><TBODY><TR><TD><DIV class=subheader>La fonction Appel conférence à trois </DIV></TD> \ <TD><?php print $navTree;?> vous permet de tenir un appel... (4 Replies)
Discussion started by: hnhegde
4 Replies
Login or Register to Ask a Question