8
$\begingroup$

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.

Improve this question
$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$
    – George Wolfe
    Sep 17, 2012 at 17:19

2 Answers 2

Reset to default
4
$\begingroup$

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.

Improve this answer
$\endgroup$
2
  • $\begingroup$ 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 ? $\endgroup$
    – Yohan
    Jun 14, 2012 at 9:42
  • $\begingroup$ @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. $\endgroup$
    – Leonid Shifrin
    Jun 14, 2012 at 9:59
3
$\begingroup$

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

Needs["DatabaseLink`"]
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" -> 
   "/Applications/Mathematica.app/SystemFiles/Links/DatabaseLink/
    DatabaseResources/accessodbc.m"
 ]
*)

under "Description" you can see the line "This driver only works on Windows." That statement shows up in the other two drivers, also. The MS SQL Server driver does not exhibit the same difficulty, but I do not believe it can be used to access an MDB file.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.