schema.sh




#!/bin/ksh
###
### schema.sh
###
### Convert *.csv file into SQL table creation scripts
###
### Usage ctable.sh filename.csv
###
###
### Note:
###
### Before dumping from Excel, change all commas (,)
### to pipes (|) doing a search and replace.
### After running this script, change pipes back 
### to commas.
###
###


x=`basename $1 .csv`

cut -d, -f1 $x.csv | sort | uniq > names.tmp

for i in `cat names.tmp`
do
     echo "create table $i (" > ddl/$i.txt

     grep "^${i}," $x.csv | \
     cut -d, -f6-8 | \
     grep -v "FIELD TYPE" | \
     sed "s/^/    /1" | \
     sed "s/,/    /1" | \
     sed "s/,/(/1" | \
     sed "s/$/),/1"  | \
     sed "s/Date( )/Date/1"  | \
     sed "s/0(0)/Char(10)/1"  | \
     sed '1s/,/    primary key,/' | \
     sed '$s/,//' | \
     sed "s/|/,/1"  >> ddl/$i.txt

     echo ")" >> ddl/$i.txt
     echo "tablespace WLI_TS;" >> ddl/$i.txt
     echo "exit;" >> ddl/$i.txt

done

### Note: The following line is a kludge    
### to deal with input lines from the original file
### that do not have a data type and length
### 
###     sed "s/0(0)/Char(10)/1"  | \
###