Hi wahi80,
Assuming that /mapr/datalake/optum/optuminsight/onepay_dev/fs_dev/raw/pklist/op/part-m-00000 is an empty file, and desc.txt and pk.txt contain the sample input shown in post #1 in this thread, Chubler_XL's code produces the output:
Code:
Employee|id|t1.id=t2.id
Contact|name,phone,country|TRIM(t1.name)=TRIM(t2.name) AND t1.phone=t2.phone AND TRIM(t1.country)=TRIM(t2.country)
Which differs from the output you said you wanted:
Code:
Employee | t1.id=s.id
Contact| TRIM(t1.name)=TRIM(s.name) AND t1.phone=t2.phone AND TRIM(t1.country)=TRIM(s.country)
in the spots marked in red.
I have no idea whether you want two | separated fields in lines in your output (as shown in the output you said you wanted) or three (as created by the code you were using). And, I have no idea why you sometimes have s.field_name and sometimes have t2.field_name in your sample output. And, I have no idea why you seem to want inconsistent field separators in your input and output (sometimes |, sometimes | , and sometimes | ).
The following comes closer to providing the output you said you want, but:
it assumes that there should just be two input files (desc.txt and pk.txt),
it always uses just | as the output field separator,
it always produces two output fields (as shown in the output you said you wanted), and
it always uses the table name specified by the awk variable D (instead of sometimes using the string s).
Code:
awk -F "[ |]*" '
BEGIN { fmt[0] = "%s%s.%s=%s.%s"
fmt[1] = "%sTRIM(%s.%s)=TRIM(%s.%s)"
}
FNR == NR {
if($3 == "string")
trim[$1, $2]
next
}
FNR > 1 {
printf("%s|", $1)
flds = split($2, F, ",")
for(i = 1; i <= flds; i++)
printf(fmt[(($1, F[i]) in trim)], (i > 1) ? " AND " : "", S,
F[i], D, F[i])
print ""
}' S=t1 D=t2 desc.txt pk.txt
which produces the output:
Code:
Employee|t1.id=t2.id
Contact|TRIM(t1.name)=TRIM(t2.name) AND t1.phone=t2.phone AND TRIM(t1.country)=TRIM(t2.country)
when given the sample input you provided in post #1.
As always, if someone wants to try this code on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.
I hope this helps,
Don
This User Gave Thanks to Don Cragun For This Post:
Considering input sample in desc.txt , field seperator is set to F "[ |]*".
Code:
T[$1"."$2]=$3
$3 will be empty "".
Here , i is assigned from for loop and F[0] inner array gets value from split function .
Note string is unset and it is empty .
Code:
T[$1"."F[i+1]]=="string"
Quote:
FNR==NR would process desc.txt FNR>1 would process pk.txt
FNR - Current input record read in current file. When awk start processing a new input file, it resets FNR to zero. NR - Number of current input record from beginning of the script started. FNR==NR is equal to one ,only once ie when reading the first input file.
It is next command in first block {} which makes awk not to read the second pattern (FNR>1) and its statement block ({}).
Hence first input file ie desc.txt is completely processed (here 3 lines).
This statement first concatenates the first and second field with a dot separator, then uses this string as an index for array T and assigns the third field's value to it. Reading all lines from "desc.txt":
Linux Gods,
I am simply attempting to parse SQL statements from a PDF doc in creating a base SQL script at a later time but for the life of me, am having a tough time extracting this data.This exact string worked perfectly a couple of months ago and now it doesnt. Below is an example of the data... (4 Replies)
Greetings Experts,
I have an excel file and I am unable to read it directly into awk (contains , " etc); So, I cleansed and copied the data into notepad.
I need to generate a script that generates the SQL.
Requirement:
1. Filter and select only the data that has the "mapping" as "direct"... (4 Replies)
Hi, This is my first post.
I have a korn shell script which outputs a select statment to a file. There is only one column and one row which contains a record count of the select statement.
The select statement looks something like this:
SELECT COUNT(some_field) AS "count_value"
... (2 Replies)
for example, I have a text file in random content inside, maybe something like this.
234234
54654
123134
467456
24234234
7867867
23424
568567if I run this command
cat "filename.txt" | sort -n | grep "^467456$" -A 1 -B 1the result is
234234
467456
568567is it possible to do this command... (2 Replies)
Hi UNIX Gurus,
I want to use extract the where clause of a SQL present in a file. Please suggest me how can I do it.
Select * from emp where emp_id>10;
cat <file_name> | grep -i "where" returns whole SQL.
how can I extract only "where emp_id>10;"
Thanks in advance (4 Replies)
Hi - I have a word GTTCAGAGTTCTACAGTCCGACGAT
I need to extract all the possible "chunks" of 7 or above letter "words" from this.
SO, my out put should be
GTTCAGA
TTCAGAG
TCAGAGT
CAGAGTTCT
TCCGACGAT
CAGTCCGACG
etc.
How can I do that with awk or any other language? I have no... (2 Replies)
All,
I am pretty new to Unix Environment. I am not sure if my requirement can be accomplished in Unix. I did try searching this forum and others but could not get an answer. Requirement is explained below:
I have a set of files in a folder.
file1_unload
file2_unload
file3_unload... (7 Replies)
Hi,
I'm not an expert in awk but i need a simple script to do this:
I'd like to AutoCrop PDF files.
I found 2 simple script that combined together could help me to automatize :)
The first utiliti is "pdfinfo" that it gives the MediaBox and TrimBox values from the pdf.
The pdfinfo output... (8 Replies)
I have input file with below content:
Person:
Name: Firstname1 lastname1
Address: 111, Straat
City : Hilversum
Person:
Name : Fistname2 lastname2
Address: 222, street
Cit: Bussum
Person:
Name : Firstname2 lastname3
Address: 333, station straat
City: Amsterdam
I need... (6 Replies)
Hi all,
I have a doubt in SQL Loader.
We have SEQUENCE function in SQL Loader or can create Sequence in Oracle database for generating a number sequence for
a column while loading data using SQL Loader into table or multiple tables.
My requirment is this.
For the first run in SQL... (2 Replies)
10-17-2016
