Merging data from 2 files of different lengths?

Login or Register to Ask a Question and Join Our Community

Merging data from 2 files of different lengths?

Tags

awk, merge, shell scripts

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting Merging data from 2 files of different lengths?

09-06-2011

Registered User

3, 0

Join Date: Sep 2011

Last Activity: 10 April 2012, 3:08 AM EDT

Posts: 3

Thanks Given: 1

Thanked 0 Times in 0 Posts

Merging data from 2 files of different lengths?

Hi all,

Sorry if someone has answered something like this already, but I have a problem. I am not brilliant with "awk" but think it should be the command to use to get what I am after.

I have 2 files:

job-file (several hundred lines like):

Code:

1018003,LONG MU WAN,1113S
1018004,LONG MU WAN,1113S
1018024,HS WAGNER,1133S
1018026,ANL WARRAIN,032S
1018032,TIAN XING HE,018S
1018055,TIAN XING HE,018S
1018057,LONG MAN BILL,918S

Vessel File (several hundred lines like):

Code:

TIAN XING HE,018S,110904,1400
ANL WARRAIN,032S,110909,0635
HS WAGNER,1133S,110905,2000
LONG MU WAN,1113S,110908,1400
SANTA MONICA,004S,110819,0630
HANJIN PORT ADELAIDE,785,110901,0630
ATHENA,134S,110905,1000
CSCL SANTIAGO,0050S,110829,1100
JPO VELA,132S,110831,1430
DIAMANTIS P,783,110823,1430
ANL BINBURRA,239S,110823,1430
MSC NORA,63A,110821,0612
STX CORINTHIAKOS,0014E,110904,0630

I want an output to look like:

Code:

1018003,LONG MU WAN,1113S,110908,1400
1018004,LONG MU WAN,1113S,110908,1400
1018024,HS WAGNER,1133S,110905,2000
1018026,ANL WARRAIN,032S,110909,0635
1018032,TIAN XING HE,018S,110904,1400
1018055,TIAN XING HE,018S,110904,1400

Basically, take all 3 fields from job-file and append fields 3 and 4 from vessel-file when fields 2 and 3 in job file = fields 1 and 2 (respectively) in vessel file. If these fields don't match there is to be no output for that entry in either file.

And quite frankly I am stumped...

I am working in Korn Shell...

Any help would be hugely appreciated.

Thanks

Steve

Last edited by radoulov; 09-06-2011 at 08:29 AM.. Reason: Code tags!

sgb2301

View Public Profile for sgb2301

Find all posts by sgb2301

09-06-2011

Registered User

509, 132

Join Date: Jul 2011

Last Activity: 24 September 2019, 9:48 AM EDT

Location: Chennai, India

Posts: 509

Thanks Given: 16

Thanked 132 Times in 127 Posts

Joining Two Files.

Hi,

just go through the below link,

https://www.unix.com/unix-dummies-que...two-files.html

Cheers,

Ranga

rangarasan

View Public Profile for rangarasan

Find all posts by rangarasan

09-06-2011

Registered User

3, 0

Join Date: Sep 2011

Last Activity: 10 April 2012, 3:08 AM EDT

Posts: 3

Thanks Given: 1

Thanked 0 Times in 0 Posts

No joy

Hi,

Thanks for that - I have tried going through the example - I can get the example to do what it advertises, but cannot work out how to apply that to my files...

Thanks

Steve

sgb2301

View Public Profile for sgb2301

Find all posts by sgb2301

09-07-2011

Registered User

1,801, 116

Join Date: Oct 2003

Last Activity: 15 May 2015, 11:55 AM EDT

Location: 54.23, -4.53

Posts: 1,801

Thanks Given: 1

Thanked 116 Times in 101 Posts

Try...

Code:

$ head vessel_file job_file
==> vessel_file <==
TIAN XING HE,018S,110904,1400
ANL WARRAIN,032S,110909,0635
HS WAGNER,1133S,110905,2000
LONG MU WAN,1113S,110908,1400
SANTA MONICA,004S,110819,0630
HANJIN PORT ADELAIDE,785,110901,0630
ATHENA,134S,110905,1000
CSCL SANTIAGO,0050S,110829,1100
JPO VELA,132S,110831,1430
DIAMANTIS P,783,110823,1430

==> job_file <==
1018003,LONG MU WAN,1113S
1018004,LONG MU WAN,1113S
1018024,HS WAGNER,1133S
1018026,ANL WARRAIN,032S
1018032,TIAN XING HE,018S
1018055,TIAN XING HE,018S
1018057,LONG MAN BILL,918S

$ awk 'BEGIN{FS=OFS=","}NR==FNR{a[$1]=$3 FS $4;next}a[$2]{print $0,a[$2]}' vessel_file job_file > output_file

$ cat output_file
1018003,LONG MU WAN,1113S,110908,1400
1018004,LONG MU WAN,1113S,110908,1400
1018024,HS WAGNER,1133S,110905,2000
1018026,ANL WARRAIN,032S,110909,0635
1018032,TIAN XING HE,018S,110904,1400
1018055,TIAN XING HE,018S,110904,1400

This User Gave Thanks to Ygor For This Post:

Ygor

View Public Profile for Ygor

Find all posts by Ygor

09-07-2011

Registered User

3, 0

Join Date: Sep 2011

Last Activity: 10 April 2012, 3:08 AM EDT

Posts: 3

Thanks Given: 1

Thanked 0 Times in 0 Posts

Thank you

Thank you so much for that - it's perfect... Now I just need to work out how it works

Much appreciated Ygor...

sgb2301

View Public Profile for sgb2301

Find all posts by sgb2301

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Paste files of varying lengths

I have three files of varying lengths and different number of columns. How can I paste all three with all columns aligned? File1 ---- 123 File2 ---- 234 345 678 File3 ---- 456 789 Output should look like: 123 234 456 345 789

2. Shell Programming and Scripting

Merging data horizontally with newlines in files

Hi Everyone, I have two files file1 and file2 with these contents cat file1 AAAAA 01/03/2014 04:01:23 BBBB 01/03/2014 03:03:34 CCCcc 01/03/2014 03:03:34 cat file2 1 RED 1 HHHH 1 TTTT 1 BBBBB I tried the below...

3. Shell Programming and Scripting

Merge two files with different lengths

Hi there, I have two very long files like: file1: two fields 1 123 1 125 1 234 2 123 2 234 2 300 2 312 3 10 3 215 4 56 ...

4. Shell Programming and Scripting

Help with merging data into single line.

Hi, My input is <message> looking for a big <message>Does fit my G74 laptop. Makes the 10 pound. <message> <message>This bag is the only one I could find to fit my awesome ASUS G74S. <message> <message> Great bag my only wish is that they had put a pocket in which to store and...

5. UNIX for Dummies Questions & Answers

Merging data in a file

Hello, Firstly I just wanted to say that I'm not a programmer at all and appreciate any help you can give. I am trying to create a shellscript that reformats the file and adding up colums 5 and 6 for those sections that are continuation of the previous line(s) (signified by beginning with '*')...

6. Shell Programming and Scripting

Merging data from one file into another

Hello, I have a master database of a dictionary with the following structure: a=b (b is a Unicode string) a is the English part and b is the equivalent in a foreign language I have also another file which has a database where the /b/ part of the string has been corrected by an expert. let us...

7. Shell Programming and Scripting

formatting and merging 2 data files

Hi, I have 2 files that I got as an output from another program. They are : File 1 ((((((CtBJa:197.0,CtBTz:197.0):85.0,CtAHr:197.0):116.0,CtDUw:197.0):176.0,CtSwe:197.0):110.0, (CtL2b:197.0,Ct4Bu:197.0):196.0):197.0,CmuNg:197.0);...

8. Shell Programming and Scripting

Merging last and syslog data on time

This is on a HP-UX system. I need to merge the 2 reports, for each line in syslog I need to lookup who was logged in to the pts/# based on the time from the last.txt report. Here is what I get from sulog.log cat syslog | grep "su:" | grep "Jun 14" Jul 14 08:02:48 server1 su: - 2 ...

9. Shell Programming and Scripting

Need help for 2 data file merging

Hello Please help me to write Shell script. I want to merge 2 data files . The data files have common columns The data file A have 3 columns Host Version Numberof Failuers The data file B have also 3 coulmns Host Version NumberofFailuers . I want to merge A and B file...

10. Shell Programming and Scripting

Merging data

Hi, I have the following problem: Input: "num1","num2","num3",num4,num5,"num6" required output: "num1num2","num3",num4,num5,"num6" I need to join field 1 and field 2 together but I always end up getting: "num1""num2","num3",num4,num5,"num6" Note that not all fields have " at both...

Login or Register to Ask a Question