Visit The New, Modern Unix Linux Community


Append data by looking up 2 tables for multiple files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Append data by looking up 2 tables for multiple files
# 1  
Append data by looking up 2 tables for multiple files

I want to lookup values from two different tables based on common columns and append. The trick is the column to be looked up is not fixed and varies , so it has to be detected from the header. How can I achieve this at once, for multiple data files, but lookup tables fixed.

The two lookup tables are ftable and ttable. For ftable I want cols 2 and 3 by matching 1.

For ttable, I want just col 2 based on col1.



ftable
Code:
fin,evalue,count2
abc,e1,7
xyz,e7,2


ttable
Code:
tin,count
ghi,11
fgr,22


in the input files , we must detect the column with header rid , that matches with ftable and the column header tid which matches with ttable


1st file from Loc B
Code:
mid,rid,tid,xid
1,abc,23,ghi,d
2,abc,,fgr,t

2nd File from expA
Code:
fid,mid,rid,tid,xid
001,1,abc,23,ghi,d
002,2,xyz,,ghi,t
003,3,xyz,1,fgr,34

Desired outputs
1st file from Loc B
Code:
mid,rid,tid,xid,evalue,count2,count
1,abc,23,ghi,d,e1,7,11
2,abc,,fgr,t,e1,7,22


2nd File from expA

Code:
fid,mid,rid,tid,xid,evalue,count2,count
001,1,abc,23,ghi,d,e1,7,11
002,2,xyz,,ghi,t,e7,2,11
003,3,xyz,1,fgr,34,e7,2,22

My try only for the 1st lookup
Code:
awk -F"," 'NR==FNR{f[$1]=$2","$3;next} NR==1 { (for i=1;i<=NF;i++) { if (i=="rid" ) { ftab=i } } print $0",evalue,count2" } NR!=1 && $ftab in f {print $0","f[$ftab]}' ftable  "1st file from Loc B"

# 2  
Hmmm... this is a bit confusing to parse this explanation.
Could you try it one more time with just ONE example and a desired output, pls.
I'm failing to see what keys you're using to find a match between 2 files...
This User Gave Thanks to vgersh99 For This Post:
# 3  
Sorry about the confusion.

Round 2

The keys are rid with ttable (tin or col1) , and fid with ftable (fin or col1) ...the issue is we have to detect which columns rid and tid are in the data since, the column numbers are not fixed in the data.

The keys in the lookup tables are always in column 1.

Another example,

ttable (with key in col 1)
Code:
tin,evalue,count2
rty,e8,9
wer,e9,23

ftable (with key in col 1)
Code:
fin,count
sdf,1
fds,2

Input data with key fields rid and fid as col2 and col3 respectively. However in another input rid and fid can be in colx and coly. But they will still be called rid and tid.
So when we read the header line of the input file, we can tell which column is rid and which column is tid.

Code:
mid,rid,fid
1,rty,sdf
2,rty,fds
3,wer,fds

Desired output


Code:
mid,rid,fid,evalue, count2, count
1,rty,sdf,e8,9,1
2,rty,fds,e8,9,2
3,wer,fds,e9,23,2


Please let me know if this makes better sense. Smilie

Last edited by ritakadm; 03-13-2015 at 11:05 AM..
# 4  
It is difficult to match column names "fid" with "fin" and almost impossible to match "rid" with "tin". So I discontinued that.
Given the column names match (fid=fid, rid=rid), try
Code:
awk     'FILENAME == "file1" ||
         FILENAME == "file2"    {X=$1; sub ($1 FS, ""); TR[X]=$0; next}

         FNR==1         {n=split (ID, TMP); for (i=1; i<=n; i++) IDar[TMP[i]]
                         for (n=1; n<=NF; n++) if ($n in IDar) IDCOL[n]
                        }

                        {printf "%s", $0; for (i in IDCOL) printf ",%s", TR[$i]; printf "\n"}

        ' ID="rid,fid"  FS=","  file1 file2 file4
mid,rid,fid,evalue,count2,count
1,rty,sdf,e8,9,1
2,rty,fds,e8,9,2
3,wer,fds,e9,23,2

This User Gave Thanks to RudiC For This Post:
# 5  
this is still confusing....
Looks like you're using 4 diff names: tin/fin for the first 2 tables AND rid/fid for the last table.
I must be thick-skulled today, but I don't get it.
Sorry, maybe somebody else could help you.
This User Gave Thanks to vgersh99 For This Post:
# 6  
those columns are present as different names in different files..that is the confusion...thank you, I will try to work with the solution provided.

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #514
Difficulty: Easy
As a general rule, the more a variable is used, the longer the variable name should be.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Export Oracle multiple tables to multiple csv files using UNIX shell scripting

Hello All, just wanted to export multiple tables from oracle sql using unix shell script to csv file and the below code is exporting only the first table. Can you please suggest why? or any better idea? export FILE="/abc/autom/file/geo_JOB.csv" Export= `sqlplus -s dev01/password@dEV3... (16 Replies)
Discussion started by: Hope
16 Replies

2. Programming

Python script to run multiple command and append data in output csv file

Experts, I am writing a script and able to write only small piece of code and not able to collect logic to complete this task. In input file have to look for name like like this (BGL_HSR_901_1AG_A_CR9KTR10) before sh iss neors. Record this (BGL_HSR_901_1AG_A_CR9KTR10) in csv file Now have to... (0 Replies)
Discussion started by: as7951
0 Replies

3. UNIX for Beginners Questions & Answers

Using bash script : How to Import data from a dsv file into multiple tables in mysql

HI I have a dsv file that looks like: <<BOF>> record_number|id_number|first name|last name|msisdn|network|points|card number|gender 312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M 313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M... (4 Replies)
Discussion started by: tera
4 Replies

4. Shell Programming and Scripting

Multiple files to load into different tables

multiple files to load into different tables, I have a script show below, but this script loads data from txt file into a table, but i have multiple input files(xyzload.txt,xyz1load.txt,xyz2load.txt......) in the unix folder , can we load these files in diff tables (table 1, table2... (1 Reply)
Discussion started by: nani1984
1 Replies

5. Shell Programming and Scripting

Multiple files to load into different tables,

multiple files to load into different tables, I have a script show below, but this script loads data from txt file into a table, but i have multiple input files(xyzload.txt,xyz1load.txt,xyz2load.txt......) in the unix folder , can we load these files in diff tables (table 1, table2... (0 Replies)
Discussion started by: nani1984
0 Replies

6. Shell Programming and Scripting

Read multiple files, parse data and append to a file

Hi..Can anyone suggest a simple way of achieving this. I have several files which ends with extension .vcf . I will give example with two files In the below files, we are interested in File 1: 38 107 C 3 T 6 C/T 38 241 C 4 T 5 C/T 38 247 T 4 C 5 T/C 38 259 T 3 C 6 T/C... (8 Replies)
Discussion started by: empyrean
8 Replies

7. UNIX for Dummies Questions & Answers

Using AWK: Extract data from multiple files and output to multiple new files

Hi, I'd like to process multiple files. For example: file1.txt file2.txt file3.txt Each file contains several lines of data. I want to extract a piece of data and output it to a new file. file1.txt ----> newfile1.txt file2.txt ----> newfile2.txt file3.txt ----> newfile3.txt Here is... (3 Replies)
Discussion started by: Liverpaul09
3 Replies

8. Shell Programming and Scripting

Shell script to identify the number of files and to append data

Hi I am having a question where I have to 1) Identify the number of files in a directory with a specific format and if the count is >1 we need to concatenate those two files into one file and remember that in the second file the header should not be copied. it should be form first file.... (4 Replies)
Discussion started by: pradkumar
4 Replies

9. Shell Programming and Scripting

Reading data from multiple tables from Oracle DB

Hi , I want to read the data from 9 tables in oracle DB into 9 different files in the same connection instance (session). I am able to get data from one table to one file with below code : X=`sqlplus -s user/pwd@DB <<eof select col1 from table1; EXIT; eof` echo $X>myfile Can anyone... (2 Replies)
Discussion started by: net
2 Replies

10. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies

Featured Tech Videos