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. >>

How can I fix this?


A little googling reveals that for large files Wolfram recommends the approach that RMMA posted:

<< 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 ideally 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
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
1  
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
1  
See this related question Speeding up Import and Export in CSV Format, if that does not solve your problem you'll have to give a sample of your file. –  RunnyKine Feb 11 at 3:02
1  
I usually export to mx.gz (not that this format is not necessary compatible among versions and platforms, but it's an order of magnitude faster to work with). You might also take a look at HDF5. –  Ajasja Feb 11 at 14:46

4 Answers 4

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

I see there are no accepted answers for this question after more than 10 months so I thought I'd have a go at it. Although I have been using Mathematica for since V8, I am only an occasional user and hence not at all an expert like the others who have chimed in so far - but I'll give it a shot.

Rather than using the Java based import to open and import the Excel file, I think a good alternative is to save the excel file as CSV and then import the CSV file using import (or other faster import methods - like discussed here).

I hate the idea of manually opening Excel and saving as CSV as it's messy and prone to error when you are trying to maintain integrity of your data analysis process. So I think a suitable solution is to automate the process of opening Excel and saving the csv file. Once you have imported the CSV file into Mathematica, then you can delete the CSV file to ensure you don't leave messy intermediate files in your file system.

To automate this process you can use Mathematica's NETLink package and take advantage of Microsoft's COM automation like so:

Define filenames and paths

SetDirectory[NotebookDirectory[]];
dataPath = "..\\Received\\";
dataFileName = "BigExcel.xlsx";
fullFilePath = FindFile[FileNameJoin[{dataPath, dataFileName}]];
(* we should really get the data file from the repo along with the \
   revision number *)
tempCSVPath = FileNameJoin[{NotebookDirectory[], "temp.csv"}];

Use a COM object to control Excel:

Needs["NETLink`"] (* load NETLink *)
NETBlock[
  excel = CreateCOMObject["Excel.Application"];

  thisWB = excel@Workbooks@Open[fullFilePath];

  (* Save the opened workbook as a temporary csv file *)
  thisWB@SaveAs[tempCSVPath, 6] (* "6" is Excel speak for .csv *)

  (* if successful then close excel *)
  thisWB@Close[False]
]

Finish up by loading the .csv file then deleting it:

rawData = Import[tempCSVPath]

DeleteFile[tempCSVPath]

COM automation works really well in Mathematica and is as easy as writing VBA code (less the intellisense). To work out what you are doing you will need to reference the Excel Object Model Reference which can be found here.

More information on using this from Mathematica can be found here: http://reference.wolfram.com/language/NETLink/tutorial/CallingNETFromTheWolframLanguage.html.

share|improve this answer
    
have you tried to directly get the data from excel through COM, probably in chunks to save memory? I'm just wondering whether that'd be faster (slower) or more (less) memory efficient and if so by how much. It would also avoid the intermediate file altogether for which writing and reading performance might depend a lot on the hardware (e.g. HD vs. SSD)... –  Albert Retey yesterday
    
No I haven't tried that but I can't see any reason why it wouldn't work. It may be quicker as it eliminates the intermediate save to csv which is time consuming. I suppose the convenience all depends on how you intend working on the data once you get it back into Mathematica. –  CrustyNoodle yesterday
    
I'm not sure whether it will be quicker in any case as there is also quite some overhead in the COM approach, especially I think copies of the data will be made which might lead to memory problems. I absolutely aggree that of the many possible ways to get the data every one has its strong and weak sides, it certainly depends very much on the use case which approach will turn out to be the best... –  Albert Retey 21 hours ago

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'm afraid it will take some effort to solve your problem with either of them:

  • 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. Of course that will most probably only work on Windows.

  • access the data via DatabaseLink` and the ODBC Excel Driver. I know that that basically works but have no experience about performance of that approach with large excel worksheets and large datasets. Of course this will also only work on Windows.

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

You can also try to increase the Java Virtual Machine Heap space using the following code

Needs["JLink`"];
ReinstallJava[JVMArguments -> "-Xmx1536m"];
share|improve this answer
    
It does not work. I still get the error message about Java heap space –  newbie Feb 20 at 9:51
    
@newbie you are right. I tried to export a large array as .xlsx but failed. There seems to be something messed up by Mathematica in dealing with .xlsx files via Java. You may try to convert your .xlsx file to a .csv one in Excel and then import the .csv. –  sunt05 Feb 20 at 10:24
    
FWIW its not evidently related to the particular file. I found .xlsx Import craps out at about 90000x35 (10% of what you want) for a file I made with nothing but plain floats. You can use 'g' for gigabyte by the way eg, -Xmx8g .. –  george2079 Feb 20 at 13:48
    
@george2079 I have tried -Xmx8g, but still get the error message even if I partitioned my original data set into the smaller one, which contains 371958 rows by 25 columns. Any other solutions to import big excel file ? –  newbie Feb 21 at 2:14
    
i think it was already said a couple of times -- use excel to save as .csv. It might also be worth a try to save as the pre-2007 .xls format. There will be minor differences in the way dates and such get imported. –  george2079 Feb 21 at 3:04

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.