Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Using awk and grep for sql generation
Top Forums Shell Programming and Scripting Using awk and grep for sql generation Post 302983825 by wahi80 on Monday 17th of October 2016 03:24:46 PM
Old 10-17-2016
Using awk and grep for sql generation
Hi,

I have a file pk.txt which has table related data in following format


Code:
TableName | PK
Employee | id
Contact|name,phone,country

I have another file desc.txt which lists datatype of each field like this:
Code:
Table|Field|Type
Employee|id|int
Contact|name|string
Contact|country|string
Contact|phone|bigint

My Output should be

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)

Note: TRIM should be applied only to string fields

I'm able to get the output without the TRIMs by using code below, not sure how to incorporate the TRIM logic.

Code:
awk -F "[ |]*" '
NR>1{
  printf $1 "|" $2 "|"
  flds=split($2, F, ",")
  for(i=0;i<flds;i++)
     printf "%s%s.%s=%s.%s", i?" AND ":"", S, F[i+1], D, F[i+1]
  printf "\n"
}' S=t1 D=t2  pk.txt

I'm guessing I have to grep each fld element and check if it is string, but Im unable to tie the whole output in one line.

Thanks


Moderator's Comments:
Mod Comment Please don't modify posts after people have answered referencing it, pulling the rug from under their feet!
Last edited by RudiC; 10-18-2016 at 05:24 AM.. Reason: Wrong code pasted
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

SQL Loader Auto Number Generation

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)
Discussion started by: vinoth_kumar
2 Replies

2. Shell Programming and Scripting

awk- report generation from input file

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)
Discussion started by: McLan
6 Replies

3. Shell Programming and Scripting

Dynamic command line generation with awk

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)
Discussion started by: gbagagli
8 Replies

4. Shell Programming and Scripting

Report Generation with Grep

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)
Discussion started by: bharath.gct
7 Replies

5. Shell Programming and Scripting

Random word generation with AWK

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)
Discussion started by: polsum
2 Replies

6. Shell Programming and Scripting

How to grep the where clause of a SQL?

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)
Discussion started by: ustechie
4 Replies

7. Programming

[ask]SQL command act like sort and grep

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)
Discussion started by: 14th
2 Replies

8. UNIX for Dummies Questions & Answers

Grep SQL output file for greater than number.

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)
Discussion started by: MurdocUK
2 Replies

9. Shell Programming and Scripting

awk concatenation issue - SQL generation

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)
Discussion started by: chill3chee
4 Replies

10. UNIX for Beginners Questions & Answers

Finding The Complete SQL statement Using PDFGREP Or Grep

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)
Discussion started by: metallica1973
4 Replies

LEARN ABOUT CENTOS

locale::country

Locale::Country(3)					User Contributed Perl Documentation					Locale::Country(3)

NAME

       Locale::Country - standard codes for country identification

SYNOPSIS

	  use Locale::Country;

	  $country = code2country('jp' [,CODESET]);	   # $country gets 'Japan'
	  $code    = country2code('Norway' [,CODESET]);    # $code gets 'no'

	  @codes   = all_country_codes( [CODESET]);
	  @names   = all_country_names();

	  # semi-private routines
	  Locale::Country::alias_code('uk' => 'gb');
	  Locale::Country::rename_country('gb' => 'Great Britain');

DESCRIPTION

       The "Locale::Country" module provides access to several code sets that can be used for identifying countries, such as those defined in ISO
       3166-1.

       Most of the routines take an optional additional argument which specifies the code set to use. If not specified, the default ISO 3166-1
       two-letter codes will be used.

SUPPORTED CODE SETS

       There are several different code sets you can use for identifying countries. A code set may be specified using either a name, or a constant
       that is automatically exported by this module.

       For example, the two are equivalent:

	  $country = code2country('jp','alpha-2');
	  $country = code2country('jp',LOCALE_CODE_ALPHA_2);

       The codesets currently supported are:

       alpha-2, LOCALE_CODE_ALPHA_2
	   This is the set of two-letter (lowercase) codes from ISO 3166-1, such as 'tv' for Tuvalu.

	   This is the default code set.

       alpha-3, LOCALE_CODE_ALPHA_3
	   This is the set of three-letter (lowercase) codes from ISO 3166-1, such as 'brb' for Barbados. These codes are actually defined and
	   maintained by the U.N. Statistics division.

       numeric, LOCALE_CODE_NUMERIC
	   This is the set of three-digit numeric codes from ISO 3166-1, such as 064 for Bhutan. These codes are actually defined and maintained
	   by the U.N. Statistics division.

	   If a 2-digit code is entered, it is converted to 3 digits by prepending a 0.

       fips-10, LOCALE_CODE_FIPS
	   The FIPS 10 data are two-letter (uppercase) codes assigned by the National Geospatial-Intelligence Agency.

	   NOTE: The FIPS-10 document is being withdrawn.  It was deprecated in 2008, and is being updated now only until all the agencies that
	   use it have switched to something else.

	   I will continue to support the FIPS-10 codeset as long as it is available, but at the point it is no longer available, support will be
	   withdrawn immediately.  If an official end-of-life date is announced, I will include a notice here.	Otherwise, support for the codeset
	   will be discontinued when the document is withdrawn.

	   You are encouraged to no longer use the FIPS-10 codeset.

       dom, LOCALE_CODE_DOM
	   The IANA is responsible for delegating management of the top level country domains.	The country domains are the two-letter (lowercase)
	   codes from ISO 3166 with a few other additions.

ROUTINES

       code2country ( CODE [,CODESET] )
       country2code ( NAME [,CODESET] )
       country_code2code ( CODE ,CODESET ,CODESET2 )
       all_country_codes ( [CODESET] )
       all_country_names ( [CODESET] )
       Locale::Country::rename_country	( CODE ,NEW_NAME [,CODESET] )
       Locale::Country::add_country  ( CODE ,NAME [,CODESET] )
       Locale::Country::delete_country	( CODE [,CODESET] )
       Locale::Country::add_country_alias  ( NAME ,NEW_NAME )
       Locale::Country::delete_country_alias  ( NAME )
       Locale::Country::rename_country_code  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Country::add_country_code_alias	( CODE ,NEW_CODE [,CODESET] )
       Locale::Country::delete_country_code_alias  ( CODE [,CODESET] )
	   These routines are all documented in the Locale::Codes::API man page.

       alias_code ( ALIAS, CODE [,CODESET] )
	   Version 2.07 included 2 functions for modifying the internal data: rename_country and alias_code. Both of these could be used only to
	   modify the internal data for country codes.

	   As of 3.10, the internal data for all types of codes can be modified.

	   The alias_code function is preserved for backwards compatibility, but the following two are identical:

	      alias_code(ALIAS,CODE [,CODESET]);
	      rename_country_code(CODE,ALIAS [,CODESET]);

	   and the latter should be used for consistency.

	   The alias_code function is deprecated and will be removed at some point in the future.

	   Note: this function was previously called _alias_code, but the leading underscore has been dropped. The old name was supported for all
	   2.X releases, but has been dropped as of 3.00.

SEE ALSO

       Locale::Codes
	   The Locale-Codes distribution.

       Locale::Codes::API
	   The list of functions supported by this module.

       Locale::SubCountry
	   ISO codes for country sub-divisions (states, counties, provinces, etc), as defined in ISO 3166-2.  This module is not part of the
	   Locale-Codes distribution, but is available from CPAN in CPAN/modules/by-module/Locale/

       http://www.iso.org/iso/country_codes
	   Official home page for the ISO 3166 maintenance agency.

	   Unfortunately, they do not make the actual ISO available for free, so I cannot check the alpha-3 and numerical codes here.

       http://www.iso.org/iso/list-en1-semic-3.txt
       http://www.iso.org/iso/home/standards/country_codes/iso-3166-1_decoding_table.htm
	   The source of ISO 3166-1 two-letter codes used by this module.

       http://unstats.un.org/unsd/methods/m49/m49alpha.htm
	   The source of the official ISO 3166-1 three-letter codes and three-digit codes.

	   For some reason, this table is incomplete! Several countries are missing from it, and I cannot find them anywhere on the UN site.  I
	   get as much of the data from here as I can.

       http://earth-info.nga.mil/gns/html/gazetteers2.html
	   The official list of the FIPS 10 codes.

       http://www.iana.org/domains/
	   Official source of the top-level domain names.

       https://www.cia.gov/library/publications/the-world-factbook/appendix/print_appendix-d.html
	   The World Factbook maintained by the CIA is a potential source of the data.	Unfortunately, it adds/preserves non-standard codes, so it
	   is no longer used as a source of data.

       http://www.statoids.com/wab.html
	   Another unofficial source of data. Currently, it is not used to get data, but the notes and explanatory material were very useful for
	   understanding discrepancies between the sources.

AUTHOR

       See Locale::Codes for full author history.

       Currently maintained by Sullivan Beck (sbeck@cpan.org).

COPYRIGHT

	  Copyright (c) 1997-2001 Canon Research Centre Europe (CRE).
	  Copyright (c) 2001-2010 Neil Bowers
	  Copyright (c) 2010-2013 Sullivan Beck

       This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.16.3							    2013-06-03							Locale::Country(3)
All times are GMT -4. The time now is 08:33 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy