Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Compare columns 2 files and print

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare columns 2 files and print
# 1  
Old 08-03-2011
Compare columns 2 files and print
File 1 has 16 columns so does File 2
I want to remove all records from File 2 that column 1 and column 16 match between file 1 and file 2
delimter of files is ~
# 2  
Old 08-03-2011
Can you please post some example / sample datas?
# 3  
Old 08-03-2011
file 1

123456789~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~10/15/10
234567891~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/22/11
345678912~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~04/22/14
456789123~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~11/19/12
567891234~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~01/07/11
678912345~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/07/10

file 2

123456789~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~10/15/10
123456789~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/22/11
345678912~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~04/22/14
456789123~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~11/19/12
567891234~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~01/07/11
567891234~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/07/10

desired output: column 1 for file 1 and 2 match but column2 does not therefore print record

123456789~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/22/11
567891234~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/07/10
# 4  
Old 08-03-2011
This?
Code:
~/unix.com$ awk 'NR==FNR{A[NR]=$0;next}$0!=A[FNR]' file1 file2

# 5  
Old 08-03-2011
Quote:
Originally Posted by sigh2010
File 1 has 16 columns so does File 2
I want to remove all records from File 2 that column 1 and column 16 match between file 1 and file 2
delimter of files is ~
Per original requirement:
Code:
#!/usr/bin/ksh
cat -n File1 | sed 's/~.*~//' > File1_tmp
cat -n File2 | sed 's/~.*~//' > File2_tmp
comm -13 File1_tmp File2_tmp | sed 's/ctl-v+ctl-i.*/p/' > Sedfile
sed -n -f Sedfile File2

Note: ctl-v+ctl-i means enter 2 character, control-v and control-i to get control-i (X09).

---------- Post updated at 03:19 PM ---------- Previous update was at 03:16 PM ----------

Quote:
Originally Posted by tukuyomi
This?
Code:
~/unix.com$ awk 'NR==FNR{A[NR]=$0;next}$0!=A[FNR]' file1 file2

Your solution for f1:
Code:
123456789~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~10/15/10
234567891~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/22/11
345678912~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~04/22/14
456789123~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~11/19/12
567891234~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~01/07/11
678912345~col2~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/07/10

f2:
Code:
123456789~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~10/15/10
123456789~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/22/11
345678912~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~04/22/14
456789123~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~11/19/12
567891234~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~01/07/11
567891234~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/07/10

Will output:
Code:
123456789~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~10/15/10
123456789~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/22/11
345678912~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~04/22/14
456789123~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~11/19/12
567891234~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~01/07/11
567891234~colb~col3~col4~col5~col6~col7~col8~col9~col10~col11~col12~col13~col14~col15~12/07/10

# 6  
Old 08-03-2011
Where did you get col1b from o_O?
# 7  
Old 08-03-2011
Quote:
Originally Posted by tukuyomi
Where did you get col1b o_O?
From my test.

Original post said:
Code:
File 1 has 16 columns so does File 2
I want to remove all records from File 2 that column 1 and column 16 match between file 1 and file 2 
delimter of files is ~

In other words, the only two columns that should be concerned is 1 and 16.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare 2 columns from the same file and print a value depending on the result

Hello Unix gurus, I have a file with this format (example values): label1 1 0 label2 1 0 label3 0.4 0.6 label4 0.5 0.5 label5 0.1 0.9 label6 0.9 0.1 in which: column 1 is a row label column 2 and 3 are values I would like to do a simple operation on this table and get the... (8 Replies)
Discussion started by: ksennin
8 Replies

2. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

3. Shell Programming and Scripting

[Solved] awk compare two different columns of two files and print all from both file

Hi, I want to compare two columns from file1 with another two column of file2 and print matched and unmatched column like this File1 1 rs1 abc 3 rs4 xyz 1 rs3 stu File2 1 kkk rs1 AA 10 1 aaa rs2 DD 20 1 ccc ... (2 Replies)
Discussion started by: justinjj
2 Replies

4. Shell Programming and Scripting

Compare columns of multiple files and print those unique string from File1 in an output file.

Hi, I have multiple files that each contain one column of strings: File1: 123abc 456def 789ghi File2: 123abc 456def 891jkl File3: 234mno 123abc 456def In total I have 25 of these type of file. (5 Replies)
Discussion started by: owwow14
5 Replies

5. Shell Programming and Scripting

awk compare specific columns from 2 files, print new file

Hello. I have two files. FILE1 was extracted from FILE2 and modified thanks to help from this post. Now I need to replace the extracted, modified lines into the original file (FILE2) to produce the FILE3. FILE1 1466 55.27433 14.72050 -2.52E+03 3.00E-01 1.05E+04 2.57E+04 1467 55.27433... (1 Reply)
Discussion started by: jm4smtddd
1 Replies

6. Shell Programming and Scripting

Compare selected columns of two files and print whole line with mismatch

hi! i researched about comparing two columns here and got an answer. but after examining my two files, i found out that the first columns of the two files are not unique with each other. all i want to compare is the 2nd and 3rd column. FILE 1: ABS 456 315 EBS 923 163 JYQ3 654 237 FILE 2:... (1 Reply)
Discussion started by: engr.jay
1 Replies

7. Shell Programming and Scripting

Compare two columns in two files and print the difference

one file . . importing table employee 119 . . importing table jobs 1 2nd file . . importing table employee 120 . . importing table jobs 1 and would like... (2 Replies)
Discussion started by: jhonnyrip
2 Replies

8. Shell Programming and Scripting

compare two columns of different files and print the matching second file..

Hi, I have two tab separated files; file1: S.No ddi fi cu o/l t+ t- 1 0.5 0.6 o 0.1 0.2 2 0.2 0.3 l 0.3 0.4 3 0.5 0.8 l 0.1 0.6 ... (5 Replies)
Discussion started by: vasanth.vadalur
5 Replies

9. Shell Programming and Scripting

Compare selected columns from a file and print difference

I have learned file comparison from my previous post here. Then, it is comparing the whole line. Now, i have a new problem. I have two files with 3 columns separated with a "|". What i want to do is to compare the second and third column of file 1, and the second and third column of file 2. And... (4 Replies)
Discussion started by: kingpeejay
4 Replies

10. Shell Programming and Scripting

compare columns from seven files and print the output

Hi guys, I need some help to come out with a solution . I have seven such files but I am showing only three for convenience. filea a5 20 a8 16 fileb a3 42 a7 14 filec a5 23 a3 07 The output file shoud contain the data in table form showing first field of... (7 Replies)
Discussion started by: smriti_shridhar
7 Replies
Login or Register to Ask a Question