Anything available that can read Microsoft .MDB files from Python?

[John Nagle]

I'm looking for something that can read .MDB files, the format
Microsoft Access uses, from Python. I need to do this on
Linux, without using Microsoft tools. I just need to read
the files once, so I can load the tables into another database.
Speed isn't an issue, and I don't need to access the file in
database fashion. The files are too big to read into RAM all at once,
though.

I tried "MDBtools", but the only (last) release was
a pre-release three years ago, and I've encountered
many build problems trying to make it work on Fedora
Core 6.

John Nagle

 

[Diez B. Roggisch]

I'm looking for something that can read .MDB files, the format
Microsoft Access uses, from Python. I need to do this on
Linux, without using Microsoft tools. I just need to read
the files once, so I can load the tables into another database.
Speed isn't an issue, and I don't need to access the file in
database fashion. The files are too big to read into RAM all at once,
though.

I tried "MDBtools", but the only (last) release was
a pre-release three years ago, and I've encountered
many build problems trying to make it work on Fedora
Core 6.

I was just gonna suggest it. Under ubuntu, it works easy as cake, and
converts the mdb nicely to some CSV-files.

I think you should really try and make it work.

Diez

 

[John Nagle]

I was just gonna suggest it. Under ubuntu, it works easy as cake, and
converts the mdb nicely to some CSV-files.

I think you should really try and make it work.

Diez

What MDBtools did you install? The RPM, a checkout from CVS,
or the downloadable distribution? They're all different.

John Nagle

 

[kyosohma]

What MDBtools did you install? The RPM, a checkout from CVS,
or the downloadable distribution? They're all different.

John Nagle

I've read .MDB files using ODBC. I don't know how big your files are,
but I had a file with almost 3000 rows and I was able to fetch it in
1-2 seconds. If you want to give it whirl, you just need to create an
ODBC connection and then do the following:

myconn = odbc.odbc('mydb_connection_name')
mycursor = myconn.cursor()
mycursor.execute('SELECT * FROM db_name')
mydata = mycursor.fetchall()
mycursor.close()
myconn.close()

for each in mydata:
# do something like pull the info from the variable "mydata" and
send it into the other database


It is fast. But you may like what the others have said more.

Mike

 

[Terry Reedy]

| I've read .MDB files using ODBC. I don't know how big your files are,
| but I had a file with almost 3000 rows and I was able to fetch it in
| 1-2 seconds. If you want to give it whirl, you just need to create an
| ODBC connection and then do the following:

I'll just mention that OpenOffice.org Base can make ODBC connections and
output in several formats. Don't know if their ODBC works with .MDB.

tjr

 

[Steve Holden]

| I've read .MDB files using ODBC. I don't know how big your files are,
| but I had a file with almost 3000 rows and I was able to fetch it in
| 1-2 seconds. If you want to give it whirl, you just need to create an
| ODBC connection and then do the following:

I'll just mention that OpenOffice.org Base can make ODBC connections and
output in several formats. Don't know if their ODBC works with .MDB.

I seem to remember I actually managed to open an MDB file with the Open
Office database component (but that was on Windows, so I can't guarantee
it will work on Linux).

regards
Steve

 

[John Nagle]

....
I've read .MDB files using ODBC. I don't know how big your files are,
but I had a file with almost 3000 rows and I was able to fetch it in
1-2 seconds. If you want to give it whirl, you just need to create an
ODBC connection ....

But that was on Windows, right? ODBC is just a connection
mechanism. You have to have a database of the right type to
which you can connect. On Windows, there's Jet, the engine
behind Microsoft Access, but what do you connect to on Linux?

John Nagle

 

[John Nagle]

I seem to remember I actually managed to open an MDB file with the Open
Office database component (but that was on Windows, so I can't guarantee
it will work on Linux).

That works fine on Windows, because OpenOffice can use Jet, the Access
engine. Don't know if it would work on Linux. Probably not.

I need to read about 10,000,000 records, and running them through an
OpenOffice spreadsheet probably isn't a good approach. There are quite
a few tools for dealing with small Microsoft Access databases in memory, but
they don't scale.

MDBtools would be the right answer, if it worked. I have some questions
out on the MDBtools forum, trying to work around some build problems.
MDBtools hasn't had a release in three years, and it won't build with
Fedora Core 6.

John Nagle

 

[Dennis Lee Bieber]

That works fine on Windows, because OpenOffice can use Jet, the Access
engine. Don't know if it would work on Linux. Probably not.

I need to read about 10,000,000 records, and running them through an
OpenOffice spreadsheet probably isn't a good approach. There are quite
a few tools for dealing with small Microsoft Access databases in memory, but
they don't scale.

MDBtools would be the right answer, if it worked. I have some questions
out on the MDBtools forum, trying to work around some build problems.
MDBtools hasn't had a release in three years, and it won't build with
Fedora Core 6.

How often will you be doing this effort? For a one-time, it might be
faster just to stuff the MDB on a minimal Windows box with JET, define
an ODBC connection in Windows, and use whatever ODBC is available on
Linux to connect over a network to the Windows box...
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Diez B. Roggisch]

What MDBtools did you install? The RPM, a checkout from CVS,
or the downloadable distribution? They're all different.

The one that comes with ubuntu edgy. Just


apt-get install mdbtools

or something, that's it.

I don't want to start a distro war here - but I always found the
RPM-based distros lacking, to say the least.

Diez

 

[Shane Geiger]

Try installing it from source. Perhaps getting a newer verion is all
you would need. If at that point you find there is a bug, report it.

The one that comes with ubuntu edgy. Just


apt-get install mdbtools

or something, that's it.

I don't want to start a distro war here - but I always found the
RPM-based distros lacking, to say the least.

Diez

--
Shane Geiger
IT Director
National Council on Economic Education
(e-mail address removed) | 402-438-8958 | http://www.ncee.net

Leading the Campaign for Economic and Financial Literacy

 

[Duncan Booth]

| I've read .MDB files using ODBC. I don't know how big your files are,
| but I had a file with almost 3000 rows and I was able to fetch it in
| 1-2 seconds. If you want to give it whirl, you just need to create an
| ODBC connection and then do the following:

I'll just mention that OpenOffice.org Base can make ODBC connections and
output in several formats. Don't know if their ODBC works with .MDB.

I just tried opening an mdb file from Open Office under Ubuntu. No problems
whatsoever except that the pulldown for 'Connect to an existing database'
listed 'Microsoft Access' twice. (This was a completely standard, never
before run, copy of Base.)

 

[John Nagle]

Try installing it from source. Perhaps getting a newer verion is all
you would need. If at that point you find there is a bug, report it.

I did.

I finally got MDBtools to build by throwing out all the "configure"
stuff and the makefiles, then writing some simple makefiles of my own
that just build the command line tools. The supplied build files
fail if you don't have bison, flex, autoconf, etc., although the
documentation claims otherwise.

There are some nice little command line tools in there,
fighting to get out from under all the guck. The original
author intended, I think, to build this thing up into a full
database module for .MDB files. But that was back around 2004
and never got done. So MDBtools doesn't have enough stuff
to directly interface to MDB databases as databases, but
it has too much for a file format converter. It's the extra
guck that has portability problems.

I posted the makefiles I used to the MDBtools forums on
SourceForge.

John Nagle