*.sql text file...is it possible to import into Access?

[Nathon Jones]

Hi.

I have a .sql txt file, provided as a back up of a MySQL database from our
ISP.

When I open this in Notepad, the first line begins:
DROP TABLE IF EXISTS store_category;CREATE TABLE store_category...

Which, to me, appears to be MySQL code for creating new tables, and
populating them.

What do I need to do to get the product data, held in this file, into Access
or even Excel?

Thanks for any advice offered.
Nath.

 

[John Vinson]

Hi.

I have a .sql txt file, provided as a back up of a MySQL database from our
ISP.

When I open this in Notepad, the first line begins:
DROP TABLE IF EXISTS store_category;CREATE TABLE store_category...

Which, to me, appears to be MySQL code for creating new tables, and
populating them.

What do I need to do to get the product data, held in this file, into Access
or even Excel?

Thanks for any advice offered.
Nath.

It'll be rather tedious. Access does run such queries ("DDL", Data
Definition Language) queries, but the syntax is different from MySQL,
and - most critically - it can't run them all together, but requires
that they be pulled out as individual queries. If you're using
Access2003 set Tools... Options... Tables/Queries... SQL Server
Compatible Syntax, which should reduce a lot of the dialect
differences.

Each substring terminated by a semicolon is a separate query. I'd
suggest that you make a copy of this file (KEEP the original SAFE!!)
and use a good text editor to pull off each query. Copy and paste them
into the SQL window of the Access query designer and run them. You'll
get some errors; fix them, or (as in the first instance) if there is
not table Store_Category to drop, just skip them.

This does require a pretty solid understanding of MySQL and Access
queries, and it will certainly try your patience! But I'd guess that
you'll get through it.

John W. Vinson[MVP]

 

[Guest]

Would you consider using SQL Server Express instead
of an Access/Excel file? Or MSDE/whatever from you
Office installation CD?

There is a bit of fiddling around to get it installed, but
once installed it may be easier to translate the MySQL
script into a SQL Server script.

(david)

 

['69 Camaro]

Hi, Nathon.

As has been pointed out, MySQL SQL scripts are incompatible with Jet SQL.
An alternative is to avoid manually translating the SQL script. Install
MySQL and the MyODBC 3.51 driver on a local computer, then run the SQL
script to recreate the store_category table in a new MySQL database. Open
Access and import the store_category table into Access, or link to the table
and run an append query to a table with the same structure within Access.

Download a free copy of MySQL and the driver:

http://dev.mysql.com/downloads

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Nathon Jones]

Hi John,

There are over 800 products though, which means cleaning up 800+ lines of
SQL...tedious indeed!
I tried running the SQL code in the SQL view in queries, in Access, but am
getting "Syntax Error in CREATE TABLE statement"

Here's a trimmed down version of what I was trying to run in the SQL view:

CREATE TABLE store_category (
category text NOT NULL,
cat_id int(16) NOT NULL auto_increment,
cat_father_id int(16) NOT NULL,
cat_image varchar(250) NOT NULL,
per_ship decimal(20,2) DEFAULT '0.00' NOT NULL,
item_ship decimal(20,2) DEFAULT '0.00' NOT NULL,
item_int_ship decimal(20,2) DEFAULT '0.00' NOT NULL,
per_int_ship decimal(20,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY (cat_id)
);

INSERT INTO store_category VALUES('Fruit', '52', '0', 'fruit.jpg', '0.00',
'0.00', '0.00', '0.00');
INSERT INTO store_category VALUES('Vegetables', '53', '0', 'vegetable.jpg',
'0.00', '0.00', '0.00', '0.00');

Any ideas why it would through up such an error?

Thanks for your help. Really appreciate it.
Regards
Nathon.

 

[Nathon Jones]

Hi Camaro,

I've installed both, but being new to MySQL, I'm really not sure what to do
next!

I followed the installation of MySQL on a local machine, and selected the
default installation settings. I also installed the ODBC driver although an
error appeared during installation of that.

I now have a new program/s showing in my programs list...
MySQL Administrator
MySQL System Tray Monitor
MySQL Server 5.0

What do I do next? When I click on the MySQL administrator, a dialog box
appears with "Connect to MySQL Server Instance".
There is then a Stored Connection Name, Server Host (IP address), Username
and Password.

I can't get past this! During installation I selected "annonymous user"
because I only want to use this once in order to extract the data from the
..sql dump file.

Could I ask for some more specific instruction on where I go from here?
How do I run the SQL script in MySQL to recreate the store_category table?

Really appreciate your help. Thanks.

Regards
Nath.

 

[david epsom dot com dot au]

You've got both an insert statement and a create statement there.

You won't be able to run more than one statement at a time
in the Access SQL view.

Also, the syntax in the Access SQL view is "Jet" syntax,
rather than "Ansi" syntax, unless you create and use a
database with "sql server compatible syntax". To get that,
open a database, go to Tools,Options,Tables/Queries.

In your example

int(16) should be SmallInt

int(16) auto_increment is not supported - use AutoIncrement
which will give you int(32) like this:
"cat_id AutoIncrement NOT NULL,"

Also, I think Varchar(250) will actually give you nVarChar(250)

Import all of it into a table, with one statement per record.

Update the records in the table to correct the syntax
(replace int(16) with SmallInt etc), then use a script
to run the records:

while not rs.eof
db.execute rs.sql
rs.next
wend

(david)