[Westwales] Data Base Files

[Robert Savage]

On Saturday 02 Oct 2004 14:46, Colin Sauze wrote:

> If you wanted to import it into MySQL you could use a shell script  and
> do something like:
>
> #!/bin/sh
>
>
> if [ "$#" != "1" ] ; then
>     echo "usage: convert.sh filename"
>     echo "   filename is the name of the file to convert"
>     exit 1
> fi
>
> #setup the database
>
> #create the database
> echo "create database mydata;" > schema
> #tell mysql to use it
> echo "use mydata;" >> schema
> #create the table
> echo "create table Products (PRODUCT_ID int,DESCRIPTION varchar(255));"
>
>  >> schema
>
> #setup primary key
> echo "create INDEX PRODUCT_ID ON PRODUCTS;" >> schema
>
>
> #get number of lines in input file, wc -l returns number of lines,
> followed by a space and then filename
> #use awk to only get the number of lines
> NUM_OF_LINES=`wc -l $1 | awk '{print $1}' `
>
> #loop round processing each line one by one
> for i in `seq 1 $NUM_OF_LINES` ; do
>        #get all lines up until this line, and then just get the last one
> of those
>        LINE=`head -$i $1 | tail -1`
>        #assuming product number is first field and description is second
>        #this only works if description has no spaces in it
>        PRODUCT_NUM=`echo $LINE | awk '{print $1}'`
>        DESCRIPTION=`echo $LINE | awk '{print $2}'`
>        #write out our SQL commands into a file
>        echo "insert into Products
> values($PRODUCT_NUM,\"$DESCRIPTION\");" >> schema
> done
>
> #execute mysql with resulting commands
> mysql -uroot -ppassword < schema
>
>
>
> this will create now mean you can access your data from mysql.
> The same technique could be used to make a CSV file.

Thank you for that Colin,  I am finding it rather fascinating to play with,  
all part of this very steep learning curve.

Cheers
Bob