Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Joining Three Files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Joining Three Files
# 15  
Old 08-12-2011
i need to pull the three columns from the second file if they have a matching record in second file or space has to be provided for each of the columns from second file which does not have matching record.
Yes you are right there should 4 commas (,).
Order in Mac_trans.txt does not matter untill all the records comes out in the output.
Meanwhile am also trying . This is what i am trying but could not give the output (req).
Code:
nawk 'NR==FNR { a[$1]=$2$3$4$5 } NR>FNR { k=$4; if (k in a) print k,a[k]; else print $0",,,"}' mac_discount.txt mac_trans.txt

# 16  
Old 08-12-2011
Code:
nawk -F, '
NR == FNR { a[$1]=$2 "," $3 "," $4 "," $5 } 
NR != FNR && $4 in a { print  $0 "," a[$4]}
NR != FNR && !($4 in a) { print $0 ",,,," }
' mac_discount.txt mac_trans.txt

This User Gave Thanks to yazu For This Post:
mac4rfree
# 17  
Old 08-12-2011
You my man.. now i see where i went wrong.
But can we add one more key column to it??
4th and 5th column in file 1 has to match with 1st and 2nd column in file 2
Will this work????
Code:
nawk -F, '
NR == FNR { a[$1","$2]="," $3 "," $4 "," $5 } 
NR != FNR && $4","$5 in a { print  $0 "," a[$4]}
NR != FNR && !($4","$5 in a) { print $0 ",,,," }
' mac_discount.txt mac_trans.txt

---------- Post updated at 07:13 PM ---------- Previous update was at 07:09 PM ----------

This is the set of files i am working on and the output. Only issue am seeing is am getting a space instead of 2nd column from the 2nd file.

Code:
$ cat mac_discount.txt
1030064,8959,1,12380005080811105527,Customer
620500,1238,1,12380005080811102840,Customer
620528,1238,1,12380005080811142338,Customer
620535,1238,1,12380005080811142752,Customer
620543,1238,1,12380005080811144409,Customer
620545,1238,1,12380005080811144809,Customer
634952,1238,1,12380005080811102548,Customer
TransDiscountGroupTransactionID,TransDiscountGroupStoreID,SequenceID,AlternateCustomerID,DiscountGroupID
[pkdprd1@test-dwbi-app2:/import/volsap/dev]
$ cat mac_trans.txt
StoreCENTER,625_1030061_830 AM,3485e17a-0baa-46a9-9edf-07a0858c4188,1030061,8956,1,625,MBROWNE,3,000000011603
StoreCENTER,625_1030062_833 AM,992b6947-a404-4e10-8e35-44dd85ccd5ee,1030062,8957,1,625,MBROWNE,3,000000011603
StoreCENTER,625_1030063_838 AM,aa742016-0b68-4155-bc60-ca44f3dd504b,1030063,8958,1,625,MBROWNE,3,000000011603
StoreCENTER,625_1030064_839 AM,898189a4-7e5e-4341-9492-d6b72549c9bc,1030064,8959,1,625,MBROWNE,3,000000011603
StoreCENTER,625_1030065_841 AM,01fc6fa4-6f40-47ce-ab76-fad4ddd88d6b,1030065,8960,1,625,MBROWNE,3,000000011603
StoreCENTER,625_1030066_843 AM,6ddef2a7-8e11-4076-91fa-8cf3a1d8e3e0,1030066,8961,1,625,MBROWNE,3,000000011603
StoreCENTER,625_1030067_844 AM,7d7ae219-70b7-4b8e-a282-a5a3a4cb2074,1030067,8962,1,625,MBROWNE,3,000000011603
[pkdprd1@test-dwbi-app2:/import/volsap/dev]
$ nawk -F, '
NR == FNR { a[$1","$2]="," $3 "," $4 "," $5 }
NR != FNR && $4","$5 in a { print  $0 "," a[$4","$5]}
NR != FNR && !($4","$5 in a) { print $0 ",,,," }
' mac_discount.txt mac_trans.txt
StoreCENTER,625_1030061_830 AM,3485e17a-0baa-46a9-9edf-07a0858c4188,1030061,8956,1,625,MBROWNE,3,000000011603,,,,
StoreCENTER,625_1030062_833 AM,992b6947-a404-4e10-8e35-44dd85ccd5ee,1030062,8957,1,625,MBROWNE,3,000000011603,,,,
StoreCENTER,625_1030063_838 AM,aa742016-0b68-4155-bc60-ca44f3dd504b,1030063,8958,1,625,MBROWNE,3,000000011603,,,,
StoreCENTER,625_1030064_839 AM,898189a4-7e5e-4341-9492-d6b72549c9bc,1030064,8959,1,625,MBROWNE,3,000000011603,,1,12380005080811105527,Customer
StoreCENTER,625_1030065_841 AM,01fc6fa4-6f40-47ce-ab76-fad4ddd88d6b,1030065,8960,1,625,MBROWNE,3,000000011603,,,,
StoreCENTER,625_1030066_843 AM,6ddef2a7-8e11-4076-91fa-8cf3a1d8e3e0,1030066,8961,1,625,MBROWNE,3,000000011603,,,,
StoreCENTER,625_1030067_844 AM,7d7ae219-70b7-4b8e-a282-a5a3a4cb2074,1030067,8962,1,625,MBROWNE,3,000000011603,,,,

---------- Post updated at 07:21 PM ---------- Previous update was at 07:13 PM ----------

THanks Yazu for your help.. Finally this command is doing what i need. Thought of posting it here.

Code:
nawk -F, '
NR == FNR { a[$1","$2]=$3 "," $4 "," $5 }
NR != FNR && $4","$5 in a { print  $0 "," a[$4","$5]}
NR != FNR && !($4","$5 in a) { print $0 ",,,," }
' mac_discount.txt mac_trans.txt

mac_discount.txt - Lookup file
mac_trans.txt - parent file(left table)
# 18  
Old 08-12-2011
Just in case you are not aware, your output's unmatched records still have more fields than their matched counterparts.

Regards,
Alister
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Please help me in joining two files

I have two files with the below contents : sampleoutput3.txt 20150202;hostname1 20150223;hostname2 20150716;hostname3 sampleoutput1.txt hostname;packages_out_of_date;errata_out_of_date; hostname1;11;0; hostnamea;12;0; hostnameb;11;0; hostnamec;95;38; hostnamed;440;358;... (2 Replies)
Discussion started by: rahul2662
2 Replies

2. Shell Programming and Scripting

Joining 2 Files

File "A" (column names: Nickname Number GB) Nickname Number GB PROD_DB0034 100A 16 ASMIL1B_DATA_003 100B 16 PSPROD_0000 1014 36 PSPROD_0001 100D 223 ..... File "B" (column names: TYPE DEVICE NUMBER SIZE) TYPE DEVICE NUMBER SIZE 1750500 hdisk2 100A 16384 1750500 hdisk3 ... (4 Replies)
Discussion started by: Daniel Gate
4 Replies

3. Shell Programming and Scripting

Help with joining files and adding headers to files

Hi, I have about 20 tab delimited text files that have non sequential numbering such as: UCD2.summary.txt UCD45.summary.txt UCD56.summery.txt The first column of each file has the same number of lines and content. The next 2 column have data points: i.e UCD2.summary.txt: a 8.9 ... (8 Replies)
Discussion started by: rrdavis
8 Replies

4. Shell Programming and Scripting

Joining two files into one

Hi experts, I'm quite newbie here!! I have two seperate files. Contents of file like below File 1: 6213019212001 8063737 File:2 15703784 I want to join these two files into one where content will be File 3: 6213019212001 8063737 15703784 Regards, Ray Seilden (1 Reply)
Discussion started by: RayanS
1 Replies

5. UNIX for Dummies Questions & Answers

Joining two files

I have two comma separated files. I want to join those filesa nd put the result in separate file. smaple data are: file1: A1,1,100 A2,1,200 B1,2,100 B2,2,200 file2 1,50 1,25 1,25 1,100 1,100 2,50 2,50 (10 Replies)
Discussion started by: pandeesh
10 Replies

6. Shell Programming and Scripting

joining two or more files

i have three files file a has contents 123 234 238 file b has contents 189 567 567 and file c has contents qwe ert ery (1 Reply)
Discussion started by: tomjones
1 Replies

7. Shell Programming and Scripting

Joining files

Hi, Whats the unix function to join multiple files? is it cat? so I have multiple files in the same format and I want to join then by row eg. FILE1 1 3 1 3 1 3 1 3 FILE2 2 4 2 4 2 4 (1 Reply)
Discussion started by: kylle345
1 Replies

8. Shell Programming and Scripting

Help with joining two files

Greetings, all. I've got a project that requires I join two data files together, then do some processing and output. Everything must be done in a shell script, using standard unix tools. The files look like the following: File_1 Layout: Acct#,Subacct#,Descrip Sample: ... (3 Replies)
Discussion started by: rjlohman
3 Replies

9. UNIX for Dummies Questions & Answers

joining files

Hi, Could anyone help me ? I'm trying to join two files, but no common field are on them. So I think on generate \000\ sequence to add for each line on both files, so then will be able to join these files. Any idea? Thanks in advance, (2 Replies)
Discussion started by: Manu
2 Replies

10. UNIX for Dummies Questions & Answers

joining 2 files

Hi, I have two files that I need to find difference between. Do I use diff or join? If join, how do I use it? thanks, webtekie (1 Reply)
Discussion started by: webtekie
1 Replies
Login or Register to Ask a Question