[Westwales] Data Base Files

[Colin Sauze]

Robert Savage wrote:

>Hi Chaps,
>
>Can one of you very knowledgeable guys tell me if it's possible to import a 
>text (.txt) file into something and turn it into a data base file (.db 
>or .dbf)?  It's a list of component part numbers followed by a description, 
>in other words two columns basically and I want to be able to search the file 
>by part number or description.   It's a big list so retyping it into a 
>database program would be hard work unless I can find a natty little typist 
>who is prepared to work for next to nothing (that was FOR not IN)  :-)
>  
>
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.



>I've tried MS Works but there is no way of importing .txt files,  neither 
>could I get anywhere in Open Office.  It may be something that is very simple 
>to do but I have little experience of database programs.
>
>Any help greatly appreciated.
>
>Regards
>
>Bob Savage.
>
>_______________________________________________
>Westwales mailing list
>#wwlug on irc.freenode.net
>Westwales at mailman.lug.org.uk
>http://mailman.lug.org.uk/mailman/listinfo/westwales
>
>  
>