Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Merging two files in UNIX

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merging two files in UNIX
# 1  
Old 11-21-2013
Merging two files in UNIX
Hi Experts,
Need urgent solution for a problem.

I have two files file1 and file2. file1 is tab separated and file2 is comma separated.
I need to merge both the files into single file based on CUST_ID by retaining the headers of file1

Matching CUST_IDs should be placed one below the other in the output file.If any correspnding column is missing in file 2 it has to be replaced with 'NA';

Code:
cat file1:(tab separated)

CUST_ID	Name	Place	acc_no
1	abc	blr|chn|dlh	500|600|700
2	xyz	hyd|blr	888|999
3	mno	blr|hyd	111|222|333


cat file2:(coma separated)
CUST_ID,Name,acc_no
1,abc,500|600|700
2,xyz,111|222|333


Required output:

CUST_ID,Name,Place,acc_no
1,abc,blr|chn|dlh,500|600|700
1,abc,NA,500|600|700
2,xyz,hyd|blr,888|999
2,xyz,NA,111|222|333

# 2  
Old 11-21-2013
Quote:
Originally Posted by bharathbangalor
Hi Experts,
Need urgent solution for a problem.

I have two files file1 and file2. file1 is tab separated and file2 is comma separated.
I need to merge both the files into single file based on CUST_ID by retaining the headers of file1

Matching CUST_IDs should be placed one below the other in the output file.If any correspnding column is missing in file 2 it has to be replaced with 'NA';

Code:
cat file1:(tab separated)

CUST_ID    Name    Place    acc_no
1    abc    blr|chn|dlh    500|600|700
2    xyz    hyd|blr    888|999
3    mno    blr|hyd    111|222|333


cat file2:(coma separated)
CUST_ID,Name,acc_no
1,abc,500|600|700
2,xyz,111|222|333


Required output:

CUST_ID,Name,Place,acc_no
1,abc,blr|chn|dlh,500|600|700
1,abc,NA,500|600|700
2,xyz,hyd|blr,888|999
2,xyz,NA,111|222|333

Try:

Code:
$ awk -F'[ ,]' 'FNR==NR{gsub(/[[:space:]]/,",");A[$2]=$0;next}A[$2]{$3="NA,"$3;$0=FNR==1 ? A[$2]: A[$2] RS $0}A[$2]'  OFS=\, file1 file2

Resulting
Code:
CUST_ID,Name,Place,acc_no
1,abc,blr|chn|dlh,500|600|700
1,abc,NA,500|600|700
2,xyz,hyd|blr,888|999
2,xyz,NA,111|222|333

Last edited by Akshay Hegde; 11-21-2013 at 08:49 AM.. Reason: missed OFS
# 3  
Old 11-21-2013
Quote:
Originally Posted by Akshay Hegde
Try:

Code:
$ awk -F'[ ,]' 'FNR==NR{gsub(/[[:space:]]/,",");A[$2]=$0;next}A[$2]{$3="NA,"$3;$0=FNR==1 ? A[$2]: A[$2] RS $0}A[$2]'  OFS=\, file1 file2

Resulting
Code:
CUST_ID,Name,Place,acc_no
1,abc,blr|chn|dlh,500|600|700
1,abc,NA,500|600|700
2,xyz,hyd|blr,888|999
2,xyz,NA,111|222|333

Hi akshay ,
let me post you the actual data. PFA the data for the above specification.
Kindly help me !
files.zip (1.8 KB) 
# 4  
Old 11-21-2013
Quote:
Originally Posted by bharathbangalor
Hi akshay ,
let me post you the actual data. PFA the data for the above specification.
Kindly help me !
If you could explain relationship between sample data posted in #1 and actual data attached in #3 I will try to understand what you are going to do.
# 5  
Old 11-22-2013
Quote:
Originally Posted by Akshay Hegde
If you could explain relationship between sample data posted in #1 and actual data attached in #3 I will try to understand what you are going to do.
The tab separated file is the master file which contains all the columns and CSV file is the slave file which contains only few columns from master.

Requirement.
The final output should contain all columns(headers and its value) from master file and matching columns from slave file

Both the files have to be merged together based on CUSTSEQ . If any column is missing in slave file it has to be append as 'NA' under the header column

PFA the sample.
# 6  
Old 11-22-2013
Code:
 awk -F"[\t,]" 'NR==FNR{if (NR==1) {for (i=1;i<=NF;i++) {d[i]=$i }}
line[$1]=$0;next }
{if(FNR==1) { for (j=1;j<=NF;j++) {gsub("\"","",$j);c[j]=$j;}}
if ( line[$1] ) { print line[$1]
for ( x=1;x<i;x++ ) {
for ( y=1;y<j;y++ ) {
if (d[x] == c[y] ) { flg=1;printf $y"\t"} else { flag=0}
}
} if ( flag == 0 ) { printf "NA" } print "\n";
} }' master slave

# 7  
Old 11-22-2013
Quote:
Originally Posted by pravin27
Code:
 awk -F"[\t,]" 'NR==FNR{if (NR==1) {for (i=1;i<=NF;i++) {d[i]=$i }}
line[$1]=$0;next }
{if(FNR==1) { for (j=1;j<=NF;j++) {gsub("\"","",$j);c[j]=$j;}}
if ( line[$1] ) { print line[$1]
for ( x=1;x<i;x++ ) {
for ( y=1;y<j;y++ ) {
if (d[x] == c[y] ) { flg=1;printf $y"\t"} else { flag=0}
}
} if ( flag == 0 ) { printf "NA" } print "\n";
} }' master slave

Hi pravin,

The above code will output the headers from both the files and display values one below the other.But the output should contain headers from master file alone and there respective value from slave file.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merging Files in UNIX shell script

I have the urge to merge some files using unix shell script but I'm very new using this language and I haven't succeeded yet. The requirement is to merge the header, body and footer into one file with the name "ANY-NAME" in below example. To identify which files should be merged, I have flagged... (9 Replies)
Discussion started by: black_soul
9 Replies

2. Shell Programming and Scripting

Merging Very large CSV files in Unix

Hi, I have two very large CSV files, which I want to merge (equi-join) based on a key (column). One of the file (say F1) would have ~30 MM records and 700 columns. The other file (~f2) would have same # of records and lesser columns (say 50). I want to create an output file joining on a... (3 Replies)
Discussion started by: student_007
3 Replies

3. Shell Programming and Scripting

Help with merging 2 files into 1

::::::::: ::FileA:: ::::::::: A1-------A2--------A3---A4---A5-- ================================= AC5VXVLT-XX---------------------- B57E434--XXXX1-----MMMM-ZZZ--111- C325G20--XXXXX3----CCCC------3332 DC35S51--XXXXY1----DDDD------44X- DC35S52--XXXXY2----DDDD------44Y-... (5 Replies)
Discussion started by: lordsmiter
5 Replies

4. UNIX for Dummies Questions & Answers

Merging two files

Hi, I have two files a.txt and b.txt. a.txt 1 2 3 4 b.txt a b c d e I want to generate a file c.txt by merging these two file and the resultant file would contain c.txt 1 (4 Replies)
Discussion started by: siba.s.nayak
4 Replies

5. Shell Programming and Scripting

Help with merging files

i would like to merge two files that have the same format but have different data. i would like to create one output file that contains information from both the original files.:rolleyes: (2 Replies)
Discussion started by: joe black
2 Replies

6. Shell Programming and Scripting

Merging 2 files

Hi, I have got two files 1.txt 1111|apple| 2222|orange| 2.txt 1111|1234|000000000004356| 1111|1234|000000001111| 1111|1234|002000011112| 2222|5678|000000002222| 2222|9102|000000002222| I need to merge these two so that my out put looks like below: Search code being used should be... (4 Replies)
Discussion started by: jisha
4 Replies

7. Shell Programming and Scripting

merging two files

Friends, os: redhat enterprise linux/SCO UNIX5.0 I have two files and I would like to merge on given key value. Now I have tried with join commd but it does not supporte multiple delimiters. and if records length is not fixed. join -a1 5 -a2 1 -t -o file1 file2 > outname Can any... (7 Replies)
Discussion started by: vakharia Mahesh
7 Replies

8. UNIX for Dummies Questions & Answers

Merging files

Hi i have two files say file 1 contents are A B C D E I have file2 contents are B E F G C K I want to have new file like A B (4 Replies)
Discussion started by: ssuresh1999
4 Replies

9. Shell Programming and Scripting

merging files

Thanks in advance I have 2 files having key field in each.I would like to join both on common key.I have used join but not sucessful. The files are attached here . what i Want in the output is on the key field SLS OFFR . I have used join commd but not successful. File one ======= SNO ... (6 Replies)
Discussion started by: vakharia Mahesh
6 Replies

10. UNIX for Dummies Questions & Answers

Merging 2 .CSV files in Unix

I need a little help as I am a complete novice at scripting in unix. However, i am posed with an issue...:eek: i have two csv files in the following format@ FILE1.CSV: HEADER HEADER Header , , HEADER 001X ,,200 002X ,,300 003X ... (6 Replies)
Discussion started by: chachabronson
6 Replies
Login or Register to Ask a Question