Help!

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

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions RSS
Next:  "Like" criteria for update query  
Author Message
Nathon Jones
External


Since: Feb 04, 2006
Posts: 7



PostPosted: Sat Feb 04, 2006 9:27 pm    Post subject: *.sql text file...is it possible to import into Access?
Archived from groups: microsoft>public>access (more info?)

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.
Back to top
John Vinson
External


Since: Sep 17, 2003
Posts: 9558



PostPosted: Sat Feb 04, 2006 9:27 pm    Post subject: Re: *.sql text file...is it possible to import into Access? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

On Sat, 4 Feb 2006 16:27:30 -0000, "Nathon Jones"
wrote:

>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]
Back to top
'69 Camaro
External


Since: Aug 22, 2004
Posts: 1563



PostPosted: Sun Feb 05, 2006 2:31 pm    Post subject: Re: *.sql text file...is it possible to import into Access? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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" wrote in message

> 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.
>
Back to top
david
External


Since: Jun 22, 2005
Posts: 311



PostPosted: Sun Feb 05, 2006 2:52 pm    Post subject: Re: *.sql text file...is it possible to import into Access? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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)



"Nathon Jones" wrote in message

> 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.
>
>
Back to top
Nathon Jones
External


Since: Feb 04, 2006
Posts: 7



PostPosted: Mon Feb 06, 2006 3:22 pm    Post subject: Re: *.sql text file...is it possible to import into Access? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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.

"John Vinson" wrote in message

> On Sat, 4 Feb 2006 16:27:30 -0000, "Nathon Jones"
> wrote:
>
>>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]
Back to top
Nathon Jones
External


Since: Feb 04, 2006
Posts: 7



PostPosted: Mon Feb 06, 2006 3:45 pm    Post subject: Re: *.sql text file...is it possible to import into Access? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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.

"'69 Camaro" wrote in
message
> 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" wrote in message
>
>> 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.
>>
>
>
Back to top
david epsom dot com dot a
External


Since: Sep 21, 2003
Posts: 649



PostPosted: Tue Feb 07, 2006 6:12 pm    Post subject: Re: *.sql text file...is it possible to import into Access? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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)


"Nathon Jones" wrote in message

> 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.
>
> "John Vinson" wrote in message
>
>> On Sat, 4 Feb 2006 16:27:30 -0000, "Nathon Jones"
>> wrote:
>>
>>>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]
>
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Discussions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum