Home Man
Search
Today's Posts
Register

If you're not sure where to post a Unix or Linux question, post it here. All unix and Linux beginners welcome in this forum!

Add new column from another file

Tags
awk, join, solved

Login to Reply

 
Thread Tools Search this Thread
# 1  
Old 08-02-2017
Add new column from another file

Hi,

I have 2 files.

file1 contains by lines: hash:salt:id
file2 contains by lines: username:hash:salt

I would like to add a new coloumn (id) form file1 to file2.

The new file should contains: username:hash:salt:id
Note: file1 contains less rows than file2.
I tried
Code:
paste -d':' file1 file2

, but this solution not good for me.

Thank you

Last edited by freeroute; 08-02-2017 at 07:23 PM..
# 2  
Old 08-02-2017
What operating system and shell are you using?

Unless both file1 and file2 are sorted by hash and salt and there is a one-to-one correspondence between lines in those two files, there is no chance that the command:
Code:
paste -d':' file1 file2

should be expected to work (and even in that case, those two fields would appear in the output twice).

You could consider using the join utility, but you would need to preprocess both input files so the key field used for joining is a single field (not two fields). If that won't work, awk would be the obvious choice. But, before we can suggest any real solution to your problem, you need to clearly define what should happen if:
  1. there is no match in file1 for a pair of key fields found in file2, or
  2. there a more than one id field value in file1 for a single pair of key fields.
Please show us a representative pair of sample input files and the output that you want to produce from those sample inputs (each in CODE tags). (Please be sure that any cases that require special handling are included in your sample inputs.)
# 3  
Old 08-03-2017
I use Debian GNU Linux, and bash shell.

I tried this command
Code:
join  <(sort -t":" -k 1 file1.txt) <(sort -t":" -k 2 file2.txt) > file3.txt

For your question:
1.) there should be match. (if not, file3.txt should be empty)
2.) there will not be more than 1 id

file1.txt example:
Code:
e0113c5bccc164169af0bd68e5ecbd88:ca.!f+w;9[_2j^u<e+i6U=R:biscuit
5356defbc28859a7433a289a3ed7755d:0f#!LWxvi&FnHJdDn?<c{>M74W^[8;:blizzard
558f783143e78fd8df9d8006c993cf5a:v'>#\=c?BESaS^)_j9a7oDh:amigo
f0495d258b379363f560c7aaef658228:SD%!7vo1N;}L,,@]QA"_+-[J1,GBn5:tester
97d3d3227c40e10ea1b8af3f5156051c:4q1!w({=NIuwI32W28$&P["8g8;{bS:gregory
d448d9c1eda2368854a8a6b97d9732b9:6i=!YJ=>?Pza`K+LlY5eu@*ag5=&6O:griffin

file2.txt example:

Code:
Mercenary14:e0113c5bccc164169af0bd68e5ecbd88:ca.!f+w;9[_2j^u<e+i6U=R
thiago:8b8eab21b047d9ab19193d90ce0a98e7:=Ug"\^6mVwP*['fy2RGAJs2
uncleslam666:864ea0693e4b69ef8c3abe53fd71f913:k,x"{qla-iPRPR*ome*(!#v
rcmonster112:558f783143e78fd8df9d8006c993cf5a:v'>#\=c?BESaS^)_j9a7oDh

I need this format (so where the hash:salt match, should append username and id coloumns):

Code:
Mercenary14:e0113c5bccc164169af0bd68e5ecbd88:ca.!f+w;9[_2j^u<e+i6U=R:biscuit


Last edited by freeroute; 08-03-2017 at 01:56 AM..
# 4  
Old 08-03-2017
With the sample input files you provided, why is there only one line of output? Why shouldn't the output from those two input files be:
Code:
Mercenary14:e0113c5bccc164169af0bd68e5ecbd88:ca.!f+w;9[_2j^u<e+i6U=R:biscuit
rcmonster112:558f783143e78fd8df9d8006c993cf5a:v'>#\=c?BESaS^)_j9a7oDh:amigo

?

Note that the join command:
Code:
join  -1 1 -2 2 -t: -o2.1,2.2,2.3,1.3  <(sort -t":" -k 1,2 file1.txt) <(sort -t":" -k 2,3 file2.txt) > file3.txt

produces the output above from your sample input, but you ignored my note about preprocessing both input files to produce a single key field. The above command only compares the hash fields in the two files and completely ignores the salt fields when looking for matches in the two input files.

If the above output is acceptable but you need to match both the hash and salt fields, you might try using awk:
Code:
awk -F: '
NR == FNR {
	id[$1, $2] = $3
	next
}
($2, $3) in id {
	print $0, id[$2, $3]
}' OFS=: file1.txt file2.txt > file3.txt

which also produces the output shown above from your sample input files.

If someone else wants to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.
The Following User Says Thank You to Don Cragun For This Useful Post:
freeroute (08-03-2017)
# 5  
Old 08-03-2017
Code:
With the sample input files you provided, why is there only one line of output?

Sorry, I forget to post other matching output lines.

Thanks. I tried, here is the bash error message:
Code:
awk -F: 'NR == FNR {id[$1, $2] = $3 next}($2, $3) in id {print $0, id[$2, $3]}' OFS=: file1.txt file2.txt > file3.txt

awk: line 1: syntax error at or near next

Code:
join  -1 1 -2 2 -t: -o2.1,2.2,2.3,1.3  <(sort -t":" -k 1,2 file1.txt) <(sort -t":" -k 2,3 file2.txt) > file3.txt

In this command the outfile (file3.txt) is empty.
# 6  
Old 08-03-2017
Try using the script I gave you instead of randomly joining lines and introducing syntax errors!
The Following User Says Thank You to Don Cragun For This Useful Post:
freeroute (08-03-2017)
# 7  
Old 08-03-2017
Quote:
Originally Posted by Don Cragun
Try using the script I gave you instead of randomly joining lines and introducing syntax errors!
Okay. Thanks for your help. I try with no success. I have to read man pages of join and awk again...

Edit: I tried with file1.txt, file2.txt. It works, but with the original big found_2711.txt left_txt40k. files not.

found_2711.txt is ASCII
left_txt40k.txt is data

Last edited by freeroute; 08-03-2017 at 04:12 AM..
Login to Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Get maximum per column from CSV file, based on date column ejianu Shell Programming and Scripting 6 07-10-2017 05:08 AM
Solution for replacement of 4th column with 3rd column in a file using awk/sed preserving delimters khblts Shell Programming and Scripting 5 07-27-2016 03:46 PM
[Solved] Sorting a column in a file based on a column in a second file Homa Shell Programming and Scripting 6 12-14-2012 11:50 AM
Awk: Need help replacing a specific column in a file by part of a column in another file aa2601 Shell Programming and Scripting 1 07-10-2012 09:13 PM
Replace column that matches specific pattern, with column data from another file prashali Shell Programming and Scripting 7 05-15-2012 04:17 PM
comparing column of two different files and print the column from in order of 2nd file CAch Shell Programming and Scripting 14 08-24-2011 01:45 PM
Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 rydz00 Shell Programming and Scripting 7 11-09-2010 10:28 AM
creating a file using the fist column and printing second column viralnerd UNIX for Dummies Questions & Answers 5 12-16-2009 11:05 PM
Changing one column of delimited file column to fixed width column manneni prakash Shell Programming and Scripting 5 06-22-2009 05:27 AM
How to check Null values in a file column by column if columns are Not NULLs Mandab Shell Programming and Scripting 7 03-15-2008 09:57 AM


All times are GMT -4. The time now is 05:42 PM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
UNIX.COM Login
Username:
Password:  
Show Password