Sponsored Content
Top Forums Shell Programming and Scripting Append data by looking up 2 tables for multiple files Post 302938220 by ritakadm on Friday 13th of March 2015 09:17:33 AM
Old 03-13-2015
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"

 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
Devel::Refcount(3pm)					User Contributed Perl Documentation				      Devel::Refcount(3pm)

NAME
"Devel::Refcount" - obtain the REFCNT value of a referent SYNOPSIS
use Devel::Refcount qw( refcount ); my $anon = []; print "Anon ARRAY $anon has " . refcount($anon) . " reference "; my $otherref = $anon; print "Anon ARRAY $anon now has " . refcount($anon) . " references "; DESCRIPTION
This module provides a single function which obtains the reference count of the object being pointed to by the passed reference value. FUNCTIONS
$count = refcount($ref) Returns the reference count of the object being pointed to by $ref. COMPARISON WITH SvREFCNT This function differs from "Devel::Peek::SvREFCNT" in that SvREFCNT() gives the reference count of the SV object itself that it is passed, whereas refcount() gives the count of the object being pointed to. This allows it to give the count of any referent (i.e. ARRAY, HASH, CODE, GLOB and Regexp types) as well. Consider the following example program: use Devel::Peek qw( SvREFCNT ); use Devel::Refcount qw( refcount ); sub printcount { my $name = shift; printf "%30s has SvREFCNT=%d, refcount=%d ", $name, SvREFCNT($_[0]), refcount($_[0]); } my $var = []; printcount 'Initially, $var', $var; my $othervar = $var; printcount 'Before CODE ref, $var', $var; printcount '$othervar', $othervar; my $code = sub { undef $var }; printcount 'After CODE ref, $var', $var; printcount '$othervar', $othervar; This produces the output Initially, $var has SvREFCNT=1, refcount=1 Before CODE ref, $var has SvREFCNT=1, refcount=2 $othervar has SvREFCNT=1, refcount=2 After CODE ref, $var has SvREFCNT=2, refcount=2 $othervar has SvREFCNT=1, refcount=2 Here, we see that SvREFCNT() counts the number of references to the SV object passed in as the scalar value - the $var or $othervar respectively, whereas refcount() counts the number of reference values that point to the referent object - the anonymous ARRAY in this case. Before the CODE reference is constructed, both $var and $othervar have SvREFCNT() of 1, as they exist only in the current lexical pad. The anonymous ARRAY has a refcount() of 2, because both $var and $othervar store a reference to it. After the CODE reference is constructed, the $var variable now has an SvREFCNT() of 2, because it also appears in the lexical pad for the new anonymous CODE block. PURE-PERL FALLBACK An XS implementation of this function is provided, and is used by default. If the XS library cannot be loaded, a fallback implementation in pure perl using the "B" module is used instead. This will behave identically, but is much slower. Rate pp xs pp 225985/s -- -66% xs 669570/s 196% -- SEE ALSO
o Test::Refcount - assert reference counts on objects AUTHOR
Paul Evans <leonerd@leonerd.org.uk> perl v5.14.2 2011-11-15 Devel::Refcount(3pm)
All times are GMT -4. The time now is 10:06 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy