Visit Our UNIX and Linux User Community


Awk/sed problem to write Db insertion statement


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk/sed problem to write Db insertion statement
# 1  
Old 08-25-2013
Awk/sed problem to write Db insertion statement

Hi There,

I am trying to load data from a csv file into a DB during our DB migration phase. I am successfully able export all data into a .csv file but those have to rewritten in terms insert statement which will allow for further population of same data in different DB

My exiting csv record format

Code:
10013, 23-AUG-2013,02:02:03, 1111111, 22222222

The expected output format

Code:
INSERT INTO TABLE_NAME(PARTNER_ID,CHARGE_DATE,CURRENT_DAILY_LIMIT,CURRENT_MONTHLY_LIMIT) VALUES ( '10013', TO_DATE('23-AUG-2013,02:02:03','DD-MON-YYYY,HH24:MI:SS'),'1111111','22222222');

I am trying to use awk and it's using a variable definition in it. But no luck till now. The main challenge I am facing while introducing the single quote. awk -F : variable="'" is not working.

No restriction that conversion has to be performed in single awk statement.

Any help, much appreciated!!

Regards,
Bhaskar
# 2  
Old 08-25-2013
Did you try SQLload or similar?
# 3  
Old 08-25-2013
Here is one way to do it:
Code:
awk -v sq="'" -F", *" '{printf ("INSERT INTO TABLE_NAME(PARTNER_ID,CHARGE_DATE,CURRENT_DAILY_LIMIT,CURRENT_MONTHLY_LIMIT) VALUES ( %s, TO_DATE(%s,%sDD-MON-YYYY,HH24:MI:SS%s),%s,%s);\n", sq $1 sq, sq $2 "," $3 sq, sq, sq, sq $4 sq, sq $5 sq)}' input

# 4  
Old 08-25-2013
Another option is to use a dblink between the two databases.
# 5  
Old 08-26-2013
Hi Don,

Thanks for your effort and posting.

I have tried


Code:
[wlapp@~/scripts] $ line="10015, 23-AUG-2013,02:03:46, 1111111, 22222222"
[wlapp@~/scripts] $ echo $line
10015, 23-AUG-2013,02:03:46, 1111111, 22222222
[wlapp@~/scripts] $ awk -v sq="'" -F", *" '{printf ("INSERT INTO TABLE_NAME(PARTNER_ID,CHARGE_DATE,CURRENT_DAILY_LIMIT,CURRENT_MONTHLY_LIMIT) VALUES ( %s, TO_DATE(%s,%sDD-MON-YYYY,HH24:MI:SS%s),%s,%s);\n", sq $1 sq, sq $2 "," $3 sq, sq, sq, sq $4 sq, sq $5 sq)}' $line
awk: syntax error near line 1
awk: bailing out near line 1

Can you please further suggest? is there any other way?

Actually db link and is not going to work as these DBs are in two different environments.

Effectively, what we are trying to do is fetch the data from few tables. exporting them into .csv files. Those .csv files will be used populate DB in another environment. Our target is convert those .csv files into INSERT statements using shell scripts and then run sqlplus to load the data.

Cheers,
Bhaskar
# 6  
Old 08-26-2013
Quote:
Originally Posted by bhaskar_m
Hi Don,

Thanks for your effort and posting.

I have tried


Code:
[wlapp@~/scripts] $ line="10015, 23-AUG-2013,02:03:46, 1111111, 22222222"
[wlapp@~/scripts] $ echo $line
10015, 23-AUG-2013,02:03:46, 1111111, 22222222
[wlapp@~/scripts] $ awk -v sq="'" -F", *" '{printf ("INSERT INTO TABLE_NAME(PARTNER_ID,CHARGE_DATE,CURRENT_DAILY_LIMIT,CURRENT_MONTHLY_LIMIT) VALUES ( %s, TO_DATE(%s,%sDD-MON-YYYY,HH24:MI:SS%s),%s,%s);\n", sq $1 sq, sq $2 "," $3 sq, sq, sq, sq $4 sq, sq $5 sq)}' $line
awk: syntax error near line 1
awk: bailing out near line 1

Can you please further suggest? is there any other way?

Actually db link and is not going to work as these DBs are in two different environments.

Effectively, what we are trying to do is fetch the data from few tables. exporting them into .csv files. Those .csv files will be used populate DB in another environment. Our target is convert those .csv files into INSERT statements using shell scripts and then run sqlplus to load the data.

Cheers,
Bhaskar
The script I gave you had file (representing the name of a file containing the data you wanted to process). You changed file to $line (a variable containing a single input line instead of the name of a file containing that input line) that awk interpreted as four file names: "10015,", "23-AUG-2013,02:03:46,", "1111111,", and "22222222".

To feed that line to awk instead of having awk read it from a file, try:
Code:
$ line="10015, 23-AUG-2013,02:03:46, 1111111, 22222222"
$ echo $line
10015, 23-AUG-2013,02:03:46, 1111111, 22222222
$ printf '%s\n' "$line" | awk -v sq="'" -F', *' '{printf ("INSERT INTO TABLE_NAME(PARTNER_ID,CHARGE_DATE,CURRENT_DAILY_LIMIT,CURRENT_MONTHLY_LIMIT) VALUES ( %s, TO_DATE(%s,%sDD-MON-YYYY,HH24:MI:SS%s),%s,%s);\n", sq $1 sq, sq $2 "," $3 sq, sq, sq, sq $4 sq, sq $5 sq)}'

And, if you're running on a Solaris/SunOS system, use /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk instead of awk.
# 7  
Old 08-27-2013
Hi Don,

Once again thanks a lot for your response.

Yes finally it worked, I was using wrong version of awk, this one
Code:
/usr/xpg4/bin/awk

worked.

Cheers,
Bhaskar

Previous Thread | Next Thread
Test Your Knowledge in Computers #338
Difficulty: Easy
telnet is considered safer than ssh for most remote login applications.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk statement piped inside sed

Hello folks, I have multiple occurrences of the pattern: ).: where is any digit, in various text context but the pattern is unique as this regex. And I need to turn this decimal fraction into an integer (corresponding percent value: the range of 0-100). What I'm doing is: cat... (1 Reply)
Discussion started by: roussine
1 Replies

2. Shell Programming and Scripting

sed within awk statement

input | Jan 8 2018 11:28PM| 24 | 75 | 51 | 1 | 1.600| | Jan 8 2018 12:01PM| 52 | 823 | 21 | 6 | 2.675| desired output Jan-8-2018-11:28PM 24 75 51 1 1.600 Jan-8-2018-12:01PM 52 823 21 6 2.675 Dear friends, I have input file , as shown above and... (10 Replies)
Discussion started by: sagar_1986
10 Replies

3. Shell Programming and Scripting

Convert Update statement into Insert statement in UNIX using awk, sed....

Hi folks, I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex. I have a bunch of update statements with all columns in a file which I need to convert into insert statements. UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies

4. Shell Programming and Scripting

[BASH] Problem with a sed -n statement

Hey all, So I've been banging my head against this for a few hours (:wall:) and I can't see whats wrong with it, each part seems to work fine on its own when entered at command line, but then it falls down when pulled together. I'm writing a script to translate fractional atomic coordinates... (3 Replies)
Discussion started by: EdinburghLad
3 Replies

5. Shell Programming and Scripting

Use awk/sed/grep with goto statement!

Hi, I have an array with characters and I am looking for specific character in that array and if those specific character not found than I use goto statment which is define somehwhere in the script. My code is: set a = (A B C D E F) @ i = 0 while ($i <= ${#a}) if ($a != "F" || $a != "D")... (3 Replies)
Discussion started by: dixits
3 Replies

6. Shell Programming and Scripting

Text file insertion via sed

I have 800+ html files that need to have a javascript added to them in the head. I can do the looping, setting filenames as variables, etc. but I cannot figure out how to insert my javascript file into the html. My javascript is in a file named jsinsert.txt It basically has this format:... (4 Replies)
Discussion started by: Trapper
4 Replies

7. Shell Programming and Scripting

Need help in using sed/awk for line insertion in xml

Hello, I have two text files (txt1 and txt2). txt1 contains many lines with a single number in each line. txt2 (xml format) contains information about the numbers given in txt1. I need to insert one line in txt2 within the scope of each number taken from txt1. Sample problem: txt1: 12 23... (1 Reply)
Discussion started by: shekhar2010us
1 Replies

8. Shell Programming and Scripting

AWK script problem insertion of code

Hi , I am having two files like this FILE1 #################### input SI_TESTONLY_R_00; input CE0_SE_INPUT_TESTONLY; input CE0_TCLK_TESTONLY; input SI_JTGCLOCKDR_JTAG_R_00; input CE0_TCLK_JTGCLOCKDR_JTAG; input CE0_SE_INPUT_JTGCLOCKDR_JTAG; output SO_TESTONLY_R_00; output... (2 Replies)
Discussion started by: jaita
2 Replies

9. Shell Programming and Scripting

insertion of text from sed script

Hi , This is my first thread ; facing some issues withmy sed script I need to insert the code from my log file which is between two keywords. content is like this ........ log ############################ log1 log2 231 "Ban" "tom" and the line one of the cross line friend... (2 Replies)
Discussion started by: shalini_008
2 Replies

10. Shell Programming and Scripting

Problem with awk and if statement

Hi, I have a task where i need to compare columns of two files. First file is $REG_InputFileName: "UPDATE","1010021126","1-01-01","USD" "UPDATE","1013000101","1-01-01","THB" "UPDATE","1013010107","1-01-01","THB" "UPDATE","1110011122","1-01-01","USD"... (5 Replies)
Discussion started by: manmeet
5 Replies

Featured Tech Videos