Topic: using PHP to convert ACCESS database to MySQL

[micah]

I'm planning to build out a web app that works using a MySQL database.  Currently, the client maintains the data off line in an MS Access file.  They want to continue maintaining their offline file and then upload it to the site whenever they feel like it to update the site.

I realize that I could just make the sight use the Access database as the data source but future development will necessitate MySQL (not to mention the rest of the site already runs on MySQL).  So I need to a way to easily and regularly get his uploaded .mdb file to populate the MySQL database.

Thoughts on this?

The easiest thing would be for him to export his data tables into individual .CSV files but thats a little cumbersome and makes to much work for him.  Another idea is to get a software pluggin for his copy of Access that lets him output his data into SQL statements or XML or something similar -- but again, its a little cumbersome and requires extra steps for the end user who really just wants to upload his massive .mdb file at the end of the day and have the site do all the work.

Any thoughts?

So far my best idea is to write a PHP script that connects with ODBC to his Access file as the datasource then parses the data and turns around and connects to the MySQL datasource to insert/update into the tables read by the rest of the website.

 

[ober]

So far my best idea is to write a PHP script that connects with ODBC to his Access file as the datasource then parses the data and turns around and connects to the MySQL datasource to insert/update into the tables read by the rest of the website.

That's what I would do.

[Steve]

The EASIEST way is the plugin for access. You pretty much covered your choices. Using ODBC will be the easiest for HIM but bear in mind the conversion will be the server's burden to bear, and the file is only going to grow. The plugin for access is an extra thing for him, but spares some weight on the server and coding on your part.

[webwhy]

I don't know what's available in your environment, but we frequently use Microsoft Data Transformation Services to move data from departmental data sources (SQL Server, Excel) to dmz databases on linux (99% MySQL), mainly because it integrates nicely with a windows network, and it's relatively easy to do powerful things with a little bit of vba grease.

http://en.wikipedia.org/wiki/Data_Transformation_Services

It comes with the MSDE version of SQL Server 2000, which is free, but really old.  It's been replaced by SSIS in the newer versions of SQL Server.  Nevertheless, we still use it every day in several production applications.

[Steve]

I don't know what's available in your environment, but we frequently use Microsoft Data Transformation Services to move data from departmental data sources (SQL Server, Excel) to dmz databases on linux (99% MySQL), mainly because it integrates nicely with a windows network, and it's relatively easy to do powerful things with a little bit of vba grease.

http://en.wikipedia.org/wiki/Data_Transformation_Services

It comes with the MSDE version of SQL Server 2000, which is free, but really old.  It's been replaced by SSIS in the newer versions of SQL Server.  Nevertheless, we still use it every day in several production applications.

Dude, that rules

[micah]

On this theme.... which is a better method for inserting a large amount of data?

creating list of individual insert queries:

Code: [Select]

INSERT INTO table (col1,col2,etc) VALUES (val1,val2,etc);
INSERT INTO table (col1,col2,etc) VALUES (val1,val2,etc);
INSERT INTO table (col1,col2,etc) VALUES (val1,val2,etc);
... a thousand times...
OR

create a CSV flat text file like:

Code: [Select]

val1,val2,etc
val1,val2,etc
val1,val2,etc
... a thousand times...and then make one mysql call using LOAD DATA ?

is one method faster or smarter or better than the other?

[Perspective]

Bulk loading will be significantly faster than individual inserts.

[Mike]

You know you can do more then one value per INSERT right?

[micah]

you mean like:

Code: [Select]

INSERT INTO table (col1,col2,etc)
VALUES (val1,val2,etc),(val1,val2,etc),(val1,val2,etc),etc...
actually, i forgot about being able to do that... that makes more sense then the first method.  Would having this one, really long SQL statement be any better then using LOAD DATA on a file?

[Steve]

you mean like:

Code: [Select]

INSERT INTO table (col1,col2,etc)
VALUES (val1,val2,etc),(val1,val2,etc),(val1,val2,etc),etc...
actually, i forgot about being able to do that... that makes more sense then the first method.  Would having this one, really long SQL statement be any better then using LOAD DATA on a file?

Yes

[Steve]

heres a snippet for you that i have on my drive from a project

Code: [Select]

//Writes the information to the database
mysql_query("INSERT INTO fugitive (fname, mname, lname, alias, dob,
pob, race, height, weight, eye, hair,
city, state, offense, company, lka, possible_locations,
assoc, physical, photo)
VALUES ('$fname', '$mname', '$lname', '$alias', '$dob',
'$pob', '$race', '$height', '$weight', '$eye',
'$hair', '$city', '$state', '$offense', '$company',
'$lka', '$possible_locations', '$assoc', '$physical',
'$pic')");

[micah]

RoD, you're example is still only inserting one row.  I need to insert hundreds of rows.

[Perspective]

load data will definitely be fast. Multi-valued inserts may be equivalent, but that probably depends on the implementation. If the parser has to parse the full statement before performing the insert it could be slow or restricted by memory. If you're inserting lots of data you should always go for any bulk loading functionality the db provides.



edit: and individual inserts would cause index updates after each insert! slow!

[Steve]

RoD, you're example is still only inserting one row.  I need to insert hundreds of rows.

oops