Merge files based on the column value


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge files based on the column value
# 1  
Old 01-09-2013
Question Merge files based on the column value

Hi Friends,

I have a file

Code:
 
file1.txt
 
1|ABC|3|jul|dhj
2|NHU|4|kil|eu
3|hjd|34|hfd|43

Code:
 
 
file2.txt
 
1||3|KING|dhj
2|NHU||k|
3|hjd|34|hd|43

i want to merge file1.txt file2.txt based on the column null values in file2.txif there are any nulls in column values ,
i have check the correspoding value in file1.txt and create the file3.txt

expected output

Code:
 
file3.txt

1|ABC|3|KING|dhj
2|NHU||k|eu
3|hjd|34|hd|43

Plz help
# 2  
Old 01-09-2013
What have you tried so far, what was your idea?
This User Gave Thanks to Scrutinizer For This Post:
# 3  
Old 01-09-2013
@Scrutinizer : Thanks for the reply. so far i have joined the file1.txt and file2.txt by using the first column in 2 files.

Code:
 
join -t"|" -11 -21 file1.txt file2.txt
1|ABC|3|jul|dhj||3|KING|dhj
2|NHU|4|kil|eu|NHU||k|
3|hjd|34|hfd|43|hjd|34|hd|43

next i want to compare if each column for null , but struck up how to do.
Plz help.
# 4  
Old 01-09-2013
Code:
OK, have a try with pseudo-multidimensional arrays in awk:
awk 'NR==FNR{for(i=2; i<=NF; i++) A[$1,i]=$i; next} {for(i=2; i<=NF; i++) if($i=="") $i=A[$1,i]}1' FS=\| OFS=\| file1 file2

This User Gave Thanks to Scrutinizer For This Post:
# 5  
Old 01-09-2013
@Scrutinizer : Thank you so much for the reply. would you mind explaining the code please?
# 6  
Old 01-09-2013
OK:

Code:
awk '
  NR==FNR{                                        # while reading the first file
    for(i=2; i<=NF; i++) A[$1,i]=$i               # record every field and tie it to the value of the first field..
    next                                          # when processing the first file then go on to read the next record
  }
  {                                               # when processing the second file
    for(i=2; i<=NF; i++) if($i=="") $i=A[$1,i]    # if a field number is empty, then look up the value of the first field and the relevant field number use this
  }
  1                                               # print the record...
' FS=\| OFS=\| file1 file2

This User Gave Thanks to Scrutinizer For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Linux

Merge two files based on matching criteria

Hi, I am trying to merge two csv files based on matching criteria: File description is as below : Key_File : 000|ÇÞ|Key_HF|ÇÞ|Key_FName 001|ÇÞ|Key_11|ÇÞ|Sort_Key22|ÇÞ|Key_31 002|ÇÞ|Key_12|ÇÞ|Sort_Key23|ÇÞ|Key_32 003|ÇÞ|Key_13|ÇÞ|Sort_Key24|ÇÞ|Key_33 050|ÇÞ|Key_15|ÇÞ|Sort_Key25|ÇÞ|Key_34... (3 Replies)
Discussion started by: PK29
3 Replies

2. Shell Programming and Scripting

awk - Merge two files based on one key

Hi, I am struggling with the an awk command to merge two files based on a common key. I want to append the value from File2 ($2) onto the end of File1 where $1 from each file matches - If no match then nothing is apended File1 COL1|COL2|COL3|COL4|COL5|COL6|COL7... (3 Replies)
Discussion started by: Ads89
3 Replies

3. UNIX for Dummies Questions & Answers

How to merge two tables based on a matched column?

Hi, Please excuse me , i have searched unix forum, i am unable to find what i expect , my query is , i have 2 files of same structure and having 1 similar field/column , i need to merge 2 tables/files based on the one matched field/column (that is field 1), file 1:... (5 Replies)
Discussion started by: karthikram
5 Replies

4. Shell Programming and Scripting

Merge files based on columns

011111123444 1234 1 20000 011111123444 1235 1 30000 011111123446 1234 3 40000 011111123447 1234 4 50000 011111123448 1234 3 50000 File2: 011111123444,Rsttponrfgtrgtrkrfrgtrgrer 011111123446,Rsttponrfgtrgtr 011111123447,Rsttponrfgtrguii 011111123448,Rsttponrfgtrgtjiiu I have 2 files... (4 Replies)
Discussion started by: vinus
4 Replies

5. Shell Programming and Scripting

Help with merge two file based on similar column content

Input file 1: A1BG A1BG A1BG A1CF A1CF BCAS BCAS A2LD1 A2M A2M HAT . . Input file 2: A1BG All A1CF TEMP (5 Replies)
Discussion started by: perl_beginner
5 Replies

6. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

7. Shell Programming and Scripting

merge two two txt files into one file based on one column

Hi, I have file1.txt and file2.txt and would like to create file3.txt based on one column in UNIX Eg: file1.txt 17328756,0000786623.pdf,0000786623 20115537,0000793892.pdf,0000793892 file2.txt 12521_74_4.zip,0000786623.pdf 12521_15_5.zip,0000793892.pdf Desired Output ... (5 Replies)
Discussion started by: techmoris
5 Replies

8. Shell Programming and Scripting

Merge Two Files based on First column

Hi, I need to join two files based on first column of both files.If first column of first file matches with the first column of second file, then the lines should be merged together and go for next line to check. It is something like: File one: 110001 abc efd 110002 fgh dfg 110003 ... (10 Replies)
Discussion started by: apjneeraj
10 Replies

9. Shell Programming and Scripting

merge rows based on a common column

Hi guys, Please guide me if you have a solution to this problem. I have tried paste -s but it's not giving the desired output. I have a file with the following content- A123 box1 B345 bat2 C431 my_id A123 service C431 box1 A123 my_id I need two different outputs- OUTPUT1 A123... (6 Replies)
Discussion started by: smriti_shridhar
6 Replies

10. Shell Programming and Scripting

Merge files based on key

Hi Friends, Can any one help me with merging these file based on two columns : File1: A|123|99|SAMS B|456|95|GEORGE D|789|85|HOVARD File2: S|123|99|NANcY|6357 S|123|99|GREGRO|83748 A|456|95|HARRY|827|somers S|456|95|ANTONY|546841|RUDOLPH|7263 B|456|95|SMITH|827|BOISE STATE|834... (3 Replies)
Discussion started by: sbasetty
3 Replies
Login or Register to Ask a Question