Re: Import MDB to MySQL

I used the following script to scope up mdb data and dump it in mysql,
but first I export the tables manually to mysql using ODBC from windows to
get the bare bones table, then empty it, this ensures the csv`s I export
will match the tables exactly on reimport. I need to refresh these tables
alot to keep the data upto date and this script does that nicely

extract

echo "Bristol & West ASB<br>";
$mysqlcomm="mdb-export -d , '/public/B&W ASB/BAW-FPM ASB (Master).mdb'
'Branch Details' > /var/www/html/surveyfb/bawbrand.csv";
$dump=shell_exec("$mysqlcomm");

//empty table
if (!($result = @ mysql_query ("DELETE FROM surveysfb.b_wbrand",
$connection))) showerror();

//Set Mysql Table Date field back to text for import (MDB dates are in the
wrong format)
if (!($result = @ mysql_query ("ALTER TABLE surveysfb.b_whsaasb CHANGE
`Audit_Date` `Audit_Date` VARCHAR(19)  DEFAULT '1900-01-01 00:00:00'",
$connection))) showerror();

//import csv
if (!($result = @ mysql_query (
"LOAD DATA LOCAL INFILE '/var/www/html/surveyfb/bawbrand.csv' INTO TABLE
surveysfb.b_wbrand FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(Branch_ID,Code,Name,Region,Address,Postcode,Short_Postcode,Manager,Title,Co
ntact,Comment,Telephone_Number,Tel_Comment,Fax_Number,Fax_Comment,Telecom_Ty
pe_ID,Telecom_Cont_ID,Telecom_Lines,Fax_Lines,EMail,MFT,MPT,FFT,FPT,Del_Mark
,BArea,New,Off,OffFloor,OffLift,fpmr,fpmc,fpmarea,BAreaM,Alloc,RAT)",
$connection))) showerror();

//set date to sql format
if (!($result = @ mysql_query ("UPDATE surveysfb.b_whsaasb SET Audit_Date =
CONCAT(IF(MID(Audit_date,11,1)='
',MID(Audit_Date,7,4),IF(MID(Audit_Date,7,1)='9',CONCAT('19',MID(Audit_Date,
7,2)),
CONCAT('20',MID(Audit_Date,7,2)))),'-',LEFT(Audit_Date,2),'-',MID(Audit_Date
,4,2),' 00:00:00')", $connection))) showerror();
//set text field back to datetime
if (!($result = @ mysql_query ("ALTER TABLE surveysfb.b_whsaasb CHANGE
`Audit_Date` `Audit_Date` DATETIME DEFAULT '1900-01-01 00:00:00'",
$connection))) showerror();

I dump about 5 large databases and reimport them.

All the indexes are already set up on the bare bones tables that you ODBC
export.

I did all this because I couldn`t get MDB ODBC to work with anyone but
root!!

Jonathan Hartley
Formula Project Management Ltd
Merchants House
Peckover Street
Little Germany
Bradford BD1 5BD
Tel:- 01274 739990
Fax:- 01274 395557
Mobile:- 07711 376322
E-Mail:- mail@xxxxxxxxxxxxxxx <mailto:mail@xxxxxxxxxxxxxxx>




> -----Original Message-----
> From: mdbtools-dev-admin@xxxxxxxxxxxxxxxxxxxxx
> [mailto:mdbtools-dev-admin@xxxxxxxxxxxxxxxxxxxxx]On Behalf Of
> mdbtools-dev-request@xxxxxxxxxxxxxxxxxxxxx
> Sent: 31 October 2003 04:17
> To: mdbtools-dev@xxxxxxxxxxxxxxxxxxxxx
> Subject: mdbtools-dev digest, Vol 1 #301 - 4 msgs
>
>
> Send mdbtools-dev mailing list submissions to
>       mdbtools-dev@xxxxxxxxxxxxxxxxxxxxx
>
> To subscribe or unsubscribe via the World Wide Web, visit
>       https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
> or, via email, send a message with subject or body 'help' to
>       mdbtools-dev-request@xxxxxxxxxxxxxxxxxxxxx
>
> You can reach the person managing the list at
>       mdbtools-dev-admin@xxxxxxxxxxxxxxxxxxxxx
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of mdbtools-dev digest..."
>
>
> Today's Topics:
>
>    1. Import MDB to MySQL (Jurgen at Marketcom)
>    2. Re: Import MDB to MySQL (Jim Shea)
>    3. Re: Import MDB to MySQL (Matt)
>    4. Re: Import MDB to MySQL (Jurgen at Marketcom)
>
> --__--__--
>
> Message: 1
> Date: Thu, 30 Oct 2003 15:38:59 +1100
> To: mdbtools-dev@xxxxxxxxxxxxxxxxxxxxx
> From: Jurgen at Marketcom <jurgen@xxxxxxxxxxxxxxxx>
> Organization: Marketcom
> Subject: [mdb-dev] Import MDB to MySQL
>
> Hi,
>
> Has anyone already solved this problem? I'm looking to simply scoop all
> the data from an MDB file and dump it into a MySQL database (or
> whatever SQL, really, I just want to get this stuff into a real
> database so I can do something useful with it :-) ).
>
> Thanks in advance!
>
> ......jurgen
>
> ---------------------------------------------------------------
> Jurgen Schaub                           jurgen@xxxxxxxxxxxxxxxx
> Marketcom Pty Ltd                  http://www.marketcom.com.au/
> Melbourne                                          03 9510 1544
> ---------------------------------------------------------------
>
>
> --__--__--
>
> Message: 2
> Date: Thu, 30 Oct 2003 07:09:58 -0800
> To: mdbtools-dev@xxxxxxxxxxxxxxxxxxxxx
> From: Jim Shea <jshea@xxxxxxxxxxxx>
> Subject: Re: [mdb-dev] Import MDB to MySQL
>
> At 10/29/2003 08:38 PM, Jurgen at Marketcom wrote:
>
> >Hi,
> >
> >Has anyone already solved this problem? I'm looking to simply scoop all
> >the data from an MDB file and dump it into a MySQL database (or
> >whatever SQL, really, I just want to get this stuff into a real
> >database so I can do something useful with it :-) ).
>
> I haven't been successful yet in a RH 7.3 environment. I'm trying
> to do the
> same thing (MDB -> MySQL via Perl). I have data in the MDB format
> that has
> been exported from Microsoft Project. Unfortunately every table and field
> contains underscores. Because it's an export from another commercial
> program I have no control over the object names.
>
> I can read the simplest MDB's I've created (without underscores) for
> testing but get segmentation faults on the real data. Putting single or
> double quotes around the table and field names as previously suggested
> doesn't help.
>
> I'm running version 0.5. My SA is trying to build with the available
> patches and from CVS but is having problems. Getting MDB Tools running
> would be a lifesaver for me (I wouldn't have to run a Windows 2K server
> anymore).
>
> Jim
>
>
>
> --__--__--
>
> Message: 3
> Subject: Re: [mdb-dev] Import MDB to MySQL
> From: Matt <matt@xxxxxxxx>
> To: mdbtools-dev@xxxxxxxxxxxxxxxxxxxxx
> Date: 30 Oct 2003 16:45:18 +0000
>
> I'm working on something kinda similar for a small web host who is just
> migrating to a linux box (RH7.3) from windows. Some of the more brain
> dead users want to continue uploading access dbs using frontpage or ftp.
> Initially I was hoping to use mdb-tools odbc drivers in combo with
> SunONE ASP to make everything dead simple, but no can do.
>
> But it sounds like I'm having a bit more luck than you. I've given up
> trying to automate the table creation itself - that'll be a one-shot
> thing so I'm resigned to doing it by hand. What I want is for users to
> continue to publish databases, and for the actual data import to happen
> automatically.
>
> I'm writing a perl daemon to do it: basically it creates a fifo in the
> spot where the access db would normally be, with the same name. The
> daemon then listens on the other side, pipes the incoming file to a temp
> file, then launches mdb-export to make a csv, then uses mysqlimport to
> plug that data into tables.
>
> There's ugly stuff with line-breaks inside columns and two-digit years
> in date fields, but think I got that sorted. The problems really fly if
> there's stuff in a table that mdb-tools just can't deal with, but so far
> so good.
>
> The code still isn't complete, but drop me an email off list if you'd
> like to steal it.
>
> > Putting single or
> > double quotes around the table and field names as previously suggested
> > doesn't help.
>
> Back ticks are your friends :)
>
> Matt
>
>
>
>
> --__--__--
>
> Message: 4
> Date: Fri, 31 Oct 2003 10:09:54 +1100
> From: Jurgen at Marketcom <jurgen@xxxxxxxxxxxxxxxx>
> Subject: Re: [mdb-dev] Import MDB to MySQL
> To: David Mansfield <mdbtools@xxxxxxxxxxxxx>
> Cc: mdbtools-dev@xxxxxxxxxxxxxxxxxxxxx
> Organization: Marketcom
>
> Hi all,
>
> On Thu, 30 Oct 2003 10:48:27 -0500 (EST), David Mansfield wrote:
> > I've attached some files that accomplish the goal with an
> Oracle database
> > as the destination. YMMV.  The scripts are GPLed.  You will
> need oracle,
> > the oracle utilities (sqlldr), perl, perl DBI and DBD::Oracle
> modules, and
> > quite possibly some good luck.  It does work reliably here though.
> > [...]
>
> At first glance, this looks fantastic. Thanks very much for sharing
> this. I should have been more clear in my original message - my
> objective is to dump MDBs into MySQL automatically, triggered from a
> PHP (or whatever) script. Looks like your stuff, or some version of it,
> would work great. I'll share back any changes I make in case they're
> useful for anyone else.
>
> I won't be able to do much with this for the next few days (other stuff
> to do) but will report back on successes etc.
>
> Thanks again, happy weekend everyone.
>
> .....jurgen
>
> ---------------------------------------------------------------
> Jurgen Schaub                           jurgen@xxxxxxxxxxxxxxxx
> Marketcom Pty Ltd                  http://www.marketcom.com.au/
> Melbourne                                          03 9510 1544
> ---------------------------------------------------------------
>
>
>
> --__--__--
>
> _______________________________________________
> mdbtools-dev mailing list
> mdbtools-dev@xxxxxxxxxxxxxxxxxxxxx
> https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
>
>
> End of mdbtools-dev Digest
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003



-------------------------------------------------------
This SF.net email is sponsored by: SF.net Giveback Program.
Does SourceForge.net help you be more productive?  Does it
help you create better code?   SHARE THE LOVE, and help us help
YOU!  Click Here: http://sourceforge.net/donate/