4
$\begingroup$

I have a very large Microsoft Excel file that will not Import even after setting a 5GB heap size for Java as follows.

Needs["JLink`"];
ReinstallJava[JVMArguments -> "-Xmx5g"];

The file can be found here as the "UIC INJECTION VOLUMES 2014" download and is only 64MB.

I am now attempting to open it with 

Needs["DatabaseLink`"];
conn = OpenSQLConnection[JDBC["Microsoft Excel", filename]]

but am getting a JDBC error.

JDBC::classnotfound: sun.jdbc.odbc.JdbcOdbcDriver

Any ideas to either import with Import or OpenSQLConnection or some other method?

Is this a bug that it will not import a 64MB file with a 5GB java heap size?

Confirmed bug CASE:3760897

Version 11 is using Java 1.8 but some of the JDBC drivers are using jdbc:odbc bridge which is no longer supported in Java 1.8. WRI is working on updating all JDBC drivers to be Java 1.8 compatible.

Workarounds include

  1. Download a portable version of Java 1.7 and using JLink's InstallJava[CommandLine-><path>] to install. This would let all the out of date JDBC drivers work.

  2. Download a pure JDBC driver for Microsoft Excel, install, and use.

  3. Import the Excel file into Microsoft Access and use MS Access JDBC driver.

  4. Save file as CSV and Import

  5. Automate Excel through .NETLink (@AlbertRetey from comments)

  6. Buy WRI Mathematica Link for Excel.

Improve this question
$\endgroup$
6
  • $\begingroup$ Have you tried to save each sheet in separate file and then import them one by one? $\endgroup$
    – Basheer Algohi
    Oct 31, 2016 at 16:10
  • $\begingroup$ @Algohi No, I haven't. But I'm sure you agree that a 64MB XLSX should be able to be opened with a heap size of 5GB. Also, that something is amiss with the "Microsoft Excel" JDBC driver. $\endgroup$
    – Edmund
    Oct 31, 2016 at 16:24
  • $\begingroup$ It is a one time operation? If so, it is probably easier that you just export from Excel to a plain text file (CSV) and then import it in Mathematica. $\endgroup$
    – Gustavo Delfino
    Oct 31, 2016 at 19:18
  • $\begingroup$ As for the missing odbc driver there is another relevant question with some answers and some background information. As for the import not working I would also consider the restrictions in memory and filesizes (also for other import/export formats) something that WRI should work on, no matter whether one calls the current behavior a bug or just a suboptimal implementation. Unfortunately all we can do on this site is provide workarounds, you should report your problems to WRI... $\endgroup$
    – Albert Retey
    Oct 31, 2016 at 20:14
  • 1
    $\begingroup$ another alternative would be to use .NETLink to access excel programmatically. This would be to do what the Link for Excel would do yourself. Might be an option for those who know some Excel VBA and have experience with .NETLink... $\endgroup$
    – Albert Retey
    Nov 12, 2016 at 18:34

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.