Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I'm trying to import a simple Excel file containing 94,000 rows and 52 columns of various types, mostly Reals. It's a 38-MB file. I get this super helpful error message:

Import::fmterr: Cannot import data as XLS format. >>

A little googling reveals that for large files, Wolfram recommends this magic snippet:

<< JLink`;
InstallJava[];
ReinstallJava[JVMArguments -> "-Xmx512m"]

But I get the same error no matter how big I make the number in "-Xmx512m", which I assume is the Java heap size. Can anyone recommend a way to import this file without using Excel to convert it to a different format?

Addendum: I appreciate the effective ideas that have been proposed as workarounds. But I'm looking for a way of directly importing Excel files. It will be difficult to explain that my favorite software cannot import Excel files without prior conversion using some external program. If this is the case, I'm truly surprised.

share|improve this question
    
Can you save it as a CSV in excel and then try importing it? –  s0rce Apr 2 '12 at 17:37
    
That works, but is an unacceptable workaround. –  ArgentoSapiens Apr 2 '12 at 17:41
1  
I would suggest to try something similar to what I suggested in this answer, as another possible work-around. In the case of that discussion, this was vastly more efficient than Import both run-time and memory-wise, and actually made it possible to import the file. Of course, I was relying on CSV format specifics, so this may not work for your case directly. –  Leonid Shifrin Apr 2 '12 at 18:01
    
It seems this just doesn't work with large XLS or XLSX files no matter how large I define the Javaw to be (running Home edition). A fully loaded XLS with 65536 rows to IV collumns fails to load (and takes 10 minutes+) but a CSV loads in a couple of seconds. –  Lou Apr 2 '12 at 18:14
1  
Can you check if the method (import the appropriate xml object in the zipped xls/xlsx file) described in this answer works for you –  kguler Apr 2 '12 at 19:43
show 2 more comments

2 Answers 2

From a very pragmatic point of view, it might be easier to use an external tool such as xlsx2csv (Python script, but other alternatives exist). Then simply import the comma-separated values:

ImportString[StringJoin
  @Riffle[ReadList[OpenRead["!./xlsx2csv.py test.xlsx"], "String"], "\n"], "CSV"];

On a 21 MB XLSX file on my Mac Book Pro, the above takes 115 seconds. xlsx2csv accounts for 95 of those, and 20 seconds for the Mathematica import. It can be made even faster with a temporary file (and then directly importing instead of using string operations), reducing the timing of the Mathematica part down to 10 seconds.

share|improve this answer
    
I was going to suggest something similar, +1. –  Leonid Shifrin Apr 2 '12 at 20:03
    
I have no trouble importing CSV files. I'm trying to un-break Mathematica's XLS importing behavior. Is this impossible? –  ArgentoSapiens Apr 2 '12 at 21:22
    
@ArgentoSapiens I can’t say that it’s impossible, but all I could find is a workaround that doesn’t require manual export in Excel but relies on Mathematica calling an external tool… –  F'x Apr 2 '12 at 21:56
add comment

I don't have a solution to your problem, but maybe some more ideas what you can try. I understand that you actually want a solution which would allow you to import the large excel file directly without workarounds, but as you have seen that might not be possible. You might want to contact WRI in the hope they will improve the import in future versions. For now here are some things I would try, but honestly I doubt they will solve your problem:

  • Import only parts of the file at a time, which would be possible with something like: col1 = Import["testfile.xlsx"], {"Data",1, All, 1}]. Actually I think there are chances that this could help for xlsx format if the data is distributed over several worksheets, since it could well be that these are read as single entities. In all other cases I wouldn't be surprised if all the data is read to memory but only what you ask for is transfered to Mathematica -- which most probably wouldn't solve your problem.

  • use the COM interface to get access to the data, depending on you knowledge of Excel this might or might not be difficult to do. See the section "Calling COM from Mathematica" in "NETLink/tutorial/Overview" for a simple example to get started. I wouldn't expect this to be very fast though, but if you actually only need part of the data it could be worth a try.

If you look at how import of excel files is implemented in newer versions of Mathematica you will find evidence that it uses a java library (jxl.jar) which can be found in the Converters/Java directory of your Mathematica installation, maybe you can find information on limitations of this library. The error message you see looks like a general message issued when the underlying java routines throw an exception. For your large file it might well be an exception due to memory problems, but it could just as well be something else. If it really is a memory problem increasing the java heap size would actually make sense, but probably you just can't make it large enough. Have you tried a file that is somewhat smaller, but still will give the same error with the standard settings and then checked whether increasing the heap size would help?

Just a side note (because you mentioned difficulties to explain this limitation of Mathematica): I just had a similiar problem in matlab when importing huge (well, about 30-50MB uncompressed) xml files: there the resulting exception caused a crash, so the unhelpful error message of Mathematica doesn't compare that bad. While increasing the heap size did help there it didn't solve our problems completely (there is not much room for correction if a wrong guess causes a crash, end there were some additional problems with character encodings). We ended up to parse the xml-file as text, which wasn't that difficult. The final code is very fast and reliable and we have full control over memory usage that way. If you are after a practial solution I guess you'll end up doing something similar in your case...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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