Using awk and sed to modify a create sql script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Using awk and sed to modify a create sql script
# 1  
Old 10-23-2014
Using awk and sed to modify a create sql script

Hi,

I have a file which contains the following data
Code:
 
claim_src|clm_id,typ_id
pat_src|pat_id
prov_src|prov_id,clm_id,prov_name

The first field is table name and second field is primary keys of the table

Now I have three files which contain ddl of each table.

Code:
 
clam_src.sql
pat_src.sql
prov_src.sql

So claim_src.sql would have something like

Code:
 
create table claim_src {
clm_id double,
typ_id double,
name varchar(30)
.
.
.
}

I want my shell script to read primary keys listed in first file and change datatype of field in second file

so new claim_src.sql could be

Code:
 
create table claim_src {
clm_id bigint,
typ_id bigint,
name varchar(30)
.
.
.
}

I'm stuck in getting primary key out of first file, since number of keys vary in each table.
I'm trying to use awk...but getting messed up

My plan was to get primary key and use sed on second file.

Any help is appreciated.

---------- Post updated at 12:21 PM ---------- Previous update was at 10:21 AM ----------

Hi,
This is what I'm thinking of doing, but I'm sure that there is an efficient way

Code:
awk -F "[ |]*" '
 {
   printf $1 "|" $2 "|"
   flds=split($2, F, ",")
 printf flds
 printf "\n"
 }' file1.txt >out.txt

out.txt would be

Code:
claim_src|clm_id,typ_id|2
pat_src|pat_id|1
prov_src|prov_id,clm_id,prov_name|3

Next I would do something like this
Code:
while read LINE
do
TABLE_NAME=`echo ${LINE} |cut -d '|' -f1`
KEY_LIST=`echo ${LINE} |cut -d '|' -f2`
NUM_KEYS=`echo ${LINE} |cut -d '|' -f3`

for(i=1; i<=NUM_KEYS;i++)
do
KEY=`echo ${KEY_LIST} |cut -d ',' -f$i`


sed -i '/$KEY/c\ $KEY bigint,'   $TABLE_NAME.sql

done
done< out.txt

I'm sure there is an easier way to do things, please suggest a better way

Last edited by wahi80; 10-23-2014 at 02:44 PM.. Reason: CODE TAGS
# 2  
Old 10-23-2014
What criteria are you going to use to change the datatype of the primary keys in the ddl files...
# 3  
Old 10-23-2014
All primary keys are going to be bigint.

I also noticed my sed command is messed up

I want to replace the line only if the whole word exists

i.e clm_id double, should become clm_id bigint
but
actv_clm_id double, should remain the same

Last edited by wahi80; 10-23-2014 at 03:18 PM..
# 4  
Old 10-23-2014
What about a primary key like "prov_name" that looks like it should be a varchar...
# 5  
Old 10-24-2014
Not considering shamrock's valuable remark, give this a try:
Code:
awk     '       {n=split ($2,T,",")
                 FN=$1".sql"
                 FC=0
                 while (1 == getline < FN)
                        {FC++
                         found=0
                         for (i=1; i<=n;i++)
                                {if (FC==2)  print T[i], "bigint"
                                 found += ($0 ~ T[i])
                                }
                         if (!found) print
                        }
                }
        ' FS="|" file
create table claim_src {
clm_id bigint
typ_id bigint
name varchar(30)
.
.
.
}

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sed, awk or another bash command to modify string with the content of another file

Hello everybody, I would like modify some strings using sed or another command line with the content file. For example: - {fqdn: "server-01" , ip: "server-01"} - {fqdn: "server-02" , ip: "server-02"} - {fqdn: "server-03" , ip: "server-03"} - {fqdn: "server-04" , ip: "server-04"} My... (4 Replies)
Discussion started by: dco
4 Replies

2. UNIX for Beginners Questions & Answers

awk and sed script to create one output CSV file

Hi All , I would require your help to generate one output file after post processing of one CSV file as stated below This file is just a small cut from a big file . Big file is having 20000 lines PATTERN,pat0,pat1,pat2,pat3,pat4,pat5,pat6,pat7,pat8,pat9... (2 Replies)
Discussion started by: kshitij
2 Replies

3. Shell Programming and Scripting

Modify xml using sed or awk

Hi All, I want to modify(changing the status from "on" to "off" status of Stage-element value from the below xml file using sed or awk: File Name: global.xml <?xml version="1.0" encoding="UTF-8"?> <config> <widget> <name>HTTP-POOL</name> <attributes> ... (5 Replies)
Discussion started by: wamqemail2
5 Replies

4. Shell Programming and Scripting

Calling Pl/sql function in shell script to modify csv

I need to 1.Open a csv 2.Process the csv i.e. Modify 2 column in the csv. To modify the column the value needs to be passed to a pl/sql function and the return value should be updated For eg: If column 2 E,then E will be passed in database function which will return Employee. 3. Write a... (5 Replies)
Discussion started by: Chinky23
5 Replies

5. Shell Programming and Scripting

Modify the file with awk,sed or perl

Hi All, I need help from any of you.Would be so thankful for your help. I/P DDDD,1045,161,1557,429,1694,800,1911,1113,2460,1457,2917> 1609,3113,1869,3317,2732,3701,3727,4132,5857,5107> 9004,6496 DDDD,1125,157,1558,429,1694,800,1911,1117,2432,1444,2906>... (2 Replies)
Discussion started by: Indra2011
2 Replies

6. Shell Programming and Scripting

Modify sed script

I'm trying to take out strings from log files and add them to a csv. For example, in the directory now, there are 2 log files. I get the following results: sed -e '/custodian/b' -e '/packaged by/b' -e '/package name/b' -e '/Total Data (MB) Read/b' -e '/Begin Time/b' -e d * packaged by =... (10 Replies)
Discussion started by: chipperuga
10 Replies

7. Shell Programming and Scripting

Wrap lines with awk to create SQL script

Greetings! Some of my files list hardware errors (we test electronic components), some have none. If the file name has no errors, I still want to display a message like "No error", else I display the error from the file itself. I came up with this (with help) for myfile in `find . -name... (2 Replies)
Discussion started by: alan
2 Replies

8. Shell Programming and Scripting

Sed or Awk for modify hour in a crontab AIX

Hi, I want to modifiy the hour in the crontab AIX 5.3 for this line: Input: 00 22 * * * /outillage/script_exploit/bin/SavOffline.ksh > /dev/null 2>&1 Output: 30 20 * * * /outillage/script_exploit/bin/SavOffline.ksh > /dev/null 2>&1 With the awk or sed function through a ssh -q... (1 Reply)
Discussion started by: khalidou13
1 Replies

9. Shell Programming and Scripting

modify and use awk sed program

The following awk script creates a file b.dat. awk '{print substr($0,1,27),substr($2,index($2,"_")+1)," ",substr($0,49)}' a.dat > b.dat I need this script to be modified to also sum $3 values by distinct $1 and $2 fields. Current file W2_2009275 2 8 W2_2009275 2 7 W1_2009275 1... (3 Replies)
Discussion started by: mnnarendra
3 Replies

10. UNIX for Dummies Questions & Answers

sed modify problem in script

I am having problems with the following "sed" command only when it is issued within a bash script. #!/bin/bash cat config.xml | sed -e 's/yes/no/g' > newconfig.xml When I enter this command from the command line it works like a charm, but when run in a script as shown it "zero's out" my... (2 Replies)
Discussion started by: darthur
2 Replies
Login or Register to Ask a Question