Re: text file to Oracle

hmuyal_at_disun22.epfl.ch (Herve Muyal) wrote:
>Hello,
>I have a text file of this form:
>
> Name=Tom
> Phone=1269-3445
> ZIP=12345
> City=New York
> ------------------
 <etc>
>I have created a table with all those fields.
>Is it possible to run a command (or a small script) to enter
>all the data (actually only the stuff written after "=")
>(INSERT INTO table......) for the entire file???
>
>Thank you in advance...
>Hervé Muyal

You did say 'script'. If you're running a flavor of *nix, you may be able to do the following:

#!/bin/sh
#!set -x
#    Assume the file name is 'foo.bar' (all lines as discribed, including
#    "---"

   rm script.sql
fi
touch script.sql
cat foo.bar|while read inrec
do

   namvar=`echo $inrec|awk -F\= '{print $2}'`    read inrec
   phovar=`echo $inrec|awk -F\= '{print $2}'`    read inrec
   zipvar=`echo $inrec|awk -F\= '{print $2}'`    read inrec
   citvar=`echo $inrec|awk -F\= '{print $2}'` #get rid of dummy "---" line

   read inrec
   echo "insert into mytable (namcol,phocol,zipcol,citcol)" >> script.sql    echo \
    "values(\'$namvar\',\'$phovar\',\'$zipvar\',\'$citvar\')">>script.sql    echo "\\" >> script.sql
done
sqlplus scott/tiger _at_script.sql
exit

This is an example to use/abuse as you see fit. The back quoting, variable substitution, etc. was done on the fly without any testing. This is meant as an example only. Received on Thu Jan 11 1996 - 00:00:00 CET