Shell Programming and Scripting

Hi,
Can someone let me know how I can acheive the following.

I have ~ delimited file and I need to convert into something like SQL insert statements.

SrcFile :
1~sjdsdj~asasas~
2~aaaaa~qwqwqwq~qwq
.....

I tried

Code:

AWK -F"~" '{print "INSERT INTO XX VALUES("$1 " ,\' "$2" \' , \' "$3 }' SrcFile

However AWK always treat '(single quote) as differently & i guess since single quote appear more than once. Any idea how i can make awk to treat single quote as text character or is this possible in SED?



'{print "INSERT INTO XX VALUES("$1 " ,\' "$2" \' , \' "$3 }'
|------------------------------------|xxxxxxx|--|xxxxxx|

sqlldr will handle this for you - it looks like you're using Oracle.

Code:

OPTIONS (DIRECT=false)
LOAD DATA
INFILE 'myfile'
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY '~'
TRAILING NULLCOLS
(FLD1, FLD2, FLD3)

a ctl file like this one will do what you show.

Hi Jim,
Sure, this will work, Infact my stop gap approach is this. However we are looking to acheive this in shell scripting, as we gonna apply this to disparate databases.

thx

Code:

nawk -F"~" -v q="'" '{print "INSERT INTO XX VALUES("$1 " ," q "$2" q "," q $3 }' SrcFile

you'll have to figure out the ballancing of the double-quotes inside the 'print' - I think I got it right, but......