# Trying to extract information from an MDB file. Import is too slow

I am running a Linux box and as usual the documentation for this type of box is not great.

I am trying to open up and extract and possibly include later on information from a "mdb" file. The file size is not big, but bigger than 100 MB. Unfortunately, the Import function does not work at all for certain requests. It takes too long and then the Kernel breaks down/quits while trying to process the import request.

Is there any other way ?

I understand there is some sort of way to do things using OBDC. Unfortunately, the documentation is written for windows machines and I have little experience in handling database connectivity with OBDC under Linux.

Ideas greatly appreciated.

-
I don't know how to do it on Linux, but I can say that SQL queries are extremely fast for both Access and MySql on Windows. So ODBC or something like that is the right direction. Once you get set up both quries and saves work well. –  George Wolfe Sep 17 '12 at 17:19

I would convert your file first to CSV format. Then, there are a number of options for importing CSV files. If Import is too slow, you can try something similar to what is described here or here. The method described in the former link may require custom code, while the first method of the two described in the latter link is quite general and should work out of the box (but may be somewhat slower). If your resulting data is too large to fit into the memory all at once, you may use/modify either of the above-mentioned links to load data in chunks, and convert the data into file-backed form using the large-data framework described here.

-
I tried renaming and importing as CSV, but i think data turned into garbage. Inhaling the 80 mb was no problem at all when imported this way. This is very strange. Why would import[] fail when mdb is chosen ? –  Yohan Jun 14 '12 at 9:42
@Yohan I can't comment on why Import is the way it is, but you should have your data preserved just fine if you first use some external converter to convert to CSV - e.g. MS Access should have one, you just have to use export-as-CSV. Just renaming won't do of course - MDB and CSV are different formats. The advantage of CSV is that you can consume data in chunks and write custom and reasonably fast code to do that. The references I provided should help with that, or you can do something else, e.g. use JLink/Java. –  Leonid Shifrin Jun 14 '12 at 9:59

I did a little digging, and I do not believe you can access an MDB file using DatabaseLink on anything but Windows. Looking at the available drivers

JDBCDriverNames[]
(*
{"Microsoft Access(ODBC)", "hsqldb", "HSQL(Memory)", "HSQL(Server)",
"HSQL(Server+TLS)", "HSQL(Standalone)", "HSQL(Webserver)",
"HSQL(Webserver+TLS)", "jtds_sqlserver", "jtds_sybase", "mysql",
"MySQL(Connector/J)", "ODBC(DSN)", "odbc", "PostgreSQL",
"Microsoft SQL Server(jTDS)", "Sybase(jTDS)"}
*)

there are three possibilities: {"Microsoft Access(ODBC)", "ODBC(DSN)", "odbc"}. Looking at the MS Access driver

JDBCDrivers["Microsoft Access(ODBC)"]
(*
JDBCDriver[
"Name" -> "Microsoft Access(ODBC)",
"Driver" -> "sun.jdbc.odbc.JdbcOdbcDriver",
"Protocol" ->
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; Dbq=",
"Version" -> 2.,
"Description" ->
"Microsoft Access file using JDBC-ODBC Bridge distributed with the
Sun JVM.  This driver only works on Windows.",
"Location" ->