[KLUG Members] Database import help

[Tony Gettig]

Hey there,

You might be able to do this with a mail merge in your favorite office
application suite. I've done this with M$-Office, but would like to try
it again with Star Office. 

Here's how I did it with M$, but there must be a way to do it with Star
or Open Office:

1) Open the CSV in the spreadsheet app and save it in native format.

2) Create a mail merge document in your word processing. Point your
document to the spreadsheet as the data for your merge. I can't remember
exactly what the output page format is called, something like "catalog"
maybe?, but you don't want a new page for each record. 

3) Construct the single line of your merge document with the SQL to
create your records in the database. For the "VALUES" part of your SQL
line, insert the merge data fields. Example:

insert into students (id, name, number, gradyear) values
($id,'$name','$number','$gradyear');

Though you may be inserting several thousand rows, you still only have
to write this line of SQL once. Be careful though. Check your
apostrophes and syntax or it ain't gonna work! To be sure, you should
try importing this into a test database first.

4) Perform the merge to a new document and save the resulting file as a
text file, such as "students.sql".

Now you can import this massive SQL file from the command line:

mysql database-name < students.sql

Worked great for me. YMMV. I was able to pick the fields I wanted and
loaded about as many records as you did using this process. Good luck!

Tony

On Thu, 2003-02-27 at 15:14, Andy wrote:
> Not sure if this can be done programmatically or not
> but here goes the question...
> 
> I have a text file that has Student Test Scores in it
> and I need to Import them into a SQL Database. The
> problem is the text file is a comma delimited file in
> a different format than the table the information
> needs to be imported to. Also the primary key is
> different as well (file has the student ID and the
> table has an internal linking number not the ID). 
> 
> So not knowing any programming (currently) is there a
> way of looking at a seperate table in the database to
> get the internal link field to come over and then
> create a new txt file or table in the database that
> has the new internal link number and the tests scores
> so that I can then import into the proper table.
> 
> I can always retype the text file... but that will be
> a pain for 5000 records.
> 
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - forms, calculators, tips, more
> http://taxes.yahoo.com/
> _______________________________________________
> Members mailing list
> Members@kalamazoolinux.org
> 
-- 
Tony Gettig
http://www.VoiceoversNow.com