Importing data from 'MS Access' .mdb files

[M.Ganesh]

Hi All,

I am looking for tools/methods to read(if possible also write) data from
.mdb files which are copied into my linux box with python. I require
this for coercing existing legacy data into MySQL with some change in
the data structure. The ultimate aim is to replace an application
developed in VB with django

Thanks in advance
Regards Ganesh

[Jeffrey Johnson]

Use access on your Windows PC, Get the ODBC drivers for MySQL, map the
tables and copy the data in by hand.

Jeff

[bacccr]

also, you can export your data from access to xml (within vb) file and
then load it into your mysql db with python. there is a lot of
options, select the way you familiar with.

On 9 июн, 04:30, "Jeffrey Johnson" <ortel...@gmail.com> wrote:
> Use access on your Windows PC, Get the ODBC drivers for MySQL, map the
> tables and copy the data in by hand.
>
> Jeff
>

[Julien]

The MySQL migration toolkit has worked quite well for me:
http://www.mysql.com/products/tools/migration-toolkit/

[chefsmart]

You may follow these steps:

1. install the MySQL ODBC connector (currently version 5.1)
2. create an empty database
3. create a DSN pointing to your newly created empty database
4. open your .mdb file in Access
5. select your table in Access and go to File->Export...
6. In the "save as type", choose ODBC databases(), give a name for the
table in mysql, and then choose your DSN created in step 3 above.

That's all.

[Garrett Garcia]

Ganesh,

I just had to tackle the same problem in a django project.  I'm sure this isn't the best solution but it works if you don't have access to a Windows machine.  Download and install the mdb-tools package:  http://mdbtools.sourceforge.net/  In my app I run the mdb-export program from python and save the output to csv files.  I then use python's csv libraries to read in and manipulate the data and save it in the correct format using the django models API.

-Garrett

[M.Ganesh]

Hi Jeffery, bacccr, Julien, chefsmart & Garrett,

Thanks for all your responses. All the suggested methods are suitable
for one time migration. Actually I am planning to migrate from VB to
django in phases. So I may have to update data many times. i.e first
export a few tables (doing some re-arrangement). After it settles down,
export few more tables, as well as the new data entered in the phase I
tables through the VB app. Basically one-time-export-of-tables will not
work for me. So, if only I can access 'ms access' tables through python,
I can write a few functions to check if the data has already reached
MySQL table, if not add it. The actual .mdb files are in a Windows box,
so if the setup will require a Windows box it is okay, but it will be
convenient if I can copy those .mdb files to my linux box, do the
initial developement and testing and finally point the functions to the
original .mdb files.

In short I am looking for a way to read(/write) tables in a .mdb file,
through python.

Any pointers?

Thanks for your time once again

Ganesh

[Peter Herndon]

Two possibilities come to mind. First, is the Python ADODBAPI module:
http://sourceforge.net/projects/adodbapi

Second, as an *extremely* useful tool for this sort of thing, may I
suggest Navicat for MySQL on Windows? The Windows version allows you
to import .mdb files and do data transfers directly into MySQL, with
some mapping. It may not afford you the entire power of Python for
manipulating the data, but it will give you a very nice leg up on the
process. Not free, but not too expensive, and there is an evaluation
version so you can see what it will do for you.

If you need to script data transfers and manipulations for multiple
runs, then the ADODBAPI module will work well. For figuring out what
you need to do, Navicat will be a big help, and if your manipulations
are not extensive, will possibly be entirely sufficient. Plus, it
makes a really nice MySQL admin tool (that being its primary purpose).

2008/6/9 M.Ganesh <ganesh...@gmail.com>:

[M.Ganesh]

Peter Herndon wrote:
> Two possibilities come to mind. First, is the Python ADODBAPI module:
> http://sourceforge.net/projects/adodbapi
>
> Second, as an *extremely* useful tool for this sort of thing, may I
> suggest Navicat for MySQL on Windows? The Windows version allows you
> to import .mdb files and do data transfers directly into MySQL, with
> some mapping. It may not afford you the entire power of Python for
> manipulating the data, but it will give you a very nice leg up on the
> process. Not free, but not too expensive, and there is an evaluation
> version so you can see what it will do for you.
>
> If you need to script data transfers and manipulations for multiple
> runs, then the ADODBAPI module will work well. For figuring out what
> you need to do, Navicat will be a big help, and if your manipulations
> are not extensive, will possibly be entirely sufficient. Plus, it
> makes a really nice MySQL admin tool (that being its primary purpose).
>
>

Thanks Peter, I'll have a look.

Thanks for the pointers

Regards Ganesh