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

  • 2
    $\begingroup$ 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. $\endgroup$ Commented Apr 2, 2012 at 18:01
  • 1
    $\begingroup$ 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. $\endgroup$
    – Lou
    Commented Apr 2, 2012 at 18:14
  • 1
    $\begingroup$ Can you check if the method (import the appropriate xml object in the zipped xls/xlsx file) described in this answer works for you $\endgroup$
    – kglr
    Commented Apr 2, 2012 at 19:43
  • 2
    $\begingroup$ 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. $\endgroup$
    – RunnyKine
    Commented Feb 11, 2014 at 3:02
  • 1
    $\begingroup$ 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. $\endgroup$
    – Ajasja
    Commented Feb 11, 2014 at 14:46

7 Answers 7


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:

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

  • $\begingroup$ I was going to suggest something similar, +1. $\endgroup$ Commented Apr 2, 2012 at 20:03
  • $\begingroup$ I have no trouble importing CSV files. I'm trying to un-break Mathematica's XLS importing behavior. Is this impossible? $\endgroup$ Commented Apr 2, 2012 at 21:22
  • $\begingroup$ @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… $\endgroup$
    – F'x
    Commented Apr 2, 2012 at 21:56

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

ReinstallJava[JVMArguments -> "-Xmx1536m"];
  • $\begingroup$ It does not work. I still get the error message about Java heap space $\endgroup$
    – newbie
    Commented Feb 20, 2014 at 9:51
  • $\begingroup$ @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. $\endgroup$
    – sunt05
    Commented Feb 20, 2014 at 10:24
  • $\begingroup$ 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 .. $\endgroup$
    – george2079
    Commented Feb 20, 2014 at 13:48
  • $\begingroup$ @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 ? $\endgroup$
    – newbie
    Commented Feb 21, 2014 at 2:14
  • $\begingroup$ 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. $\endgroup$
    – george2079
    Commented Feb 21, 2014 at 3:04

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

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 *)
  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 *)

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

rawData = Import[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.

  • 1
    $\begingroup$ 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)... $\endgroup$ Commented Dec 24, 2014 at 0:15
  • $\begingroup$ 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. $\endgroup$ Commented Dec 24, 2014 at 11:30
  • $\begingroup$ 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... $\endgroup$ Commented Dec 24, 2014 at 16:08

I know this is an old question, but I ran into this problem myself. The permanent solution for me was to alter the settings of JLink directly.

As for most commenters too, increasing the heap-space by giving commandline options to ReinstallJava and friends didn't work. Specifically, I tried to increase the heap-space by specifying the complete CommandLine which did not work. Using JVMArguments worked in 10.4, but then again I already searched how this works and found the file InstallJava.m

FileNames["InstallJava.m", {$InstallationDirectory}, Infinity]

Open this file (you probably need admin rights) and search for the term -Xmx. There, I have changed the line to

extraArgs = extraArgs <> " -Xmx5g -Xms512m"

I have a system with 64GB RAM and you should adjust the settings to your needs. Xmx is the maximum possible heap size and Xms is the initial heap size.

  • $\begingroup$ +1 Should the fact that increasing the heap-space (by options to ReinstallJavaand friends) doesn't work be considered a bug? Should it be reported? $\endgroup$
    – P. Fonseca
    Commented Apr 11, 2016 at 21:15
  • $\begingroup$ @P.Fonseca JVMArguments works for me in 10.4 but it is not documented. This is a thing I truly consider a bug of the documentation. What I don't understand is that specifying the CommandLine didn't work at all under OSX. $\endgroup$
    – halirutan
    Commented Apr 12, 2016 at 0:28

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


I actually found a workaround for this problem, but it might not work for everyone because it depends on ExcelLink. The code looks like this:

rawpriceworksheet = 
Join[Excel["a1:jaa1000"], Excel["a1001:jaa2000"], 
Excel["a2001:jaa3000"], Excel["a3001:jaa4000"], 
priceworksheet = rawpriceworksheet /. {Empty -> 0};

In this example I am loading a 129MB file with 4593 rows and 6787 columns. It's pretty fast - about 20 seconds. This approach lets Excel do the heavy lifting of getting the data into memory. ExcelLink doesn't loose any precision.

I also load another workbook with several worksheets with the following procedure:

ExcelActivate[index <> "-use.xlsx"];
data1 = Excel["a1:zz100"]
ExcelActivate[index <> "-use.xlsx"];
data2 = Excel["a1:zz100"]

I expect that this approach is limited only by available memory


Yes, I figured out, the problem even occurs when setting the Java Heap Space exactly to the maximum amount of RAM available on my laptop. I have an old one with 8GByte RAM. But it does not occur when I set it over the maximum available memory. Setting the Java Heap Space to 9GByte helped for me, because the JVM is not able to swap to Pagefile of my Win7, but Win7 can swap into Pagefile. And that is what exactly happens when the huge excel file import runs to the maximum available RAM. Win7 starts to write on my SSD which is not so performant like the RAM, but still fast enough to support my intended task. So all you have to do: Try setting your Java Heap Space to (YourRAM)+1GByte using your SSD and be patient, the runtime is only 2/3 longer than over RAM, so I think. Or, first, restore your excel file into a CSV-similar format and use the MMA function ReadList[...,"Record"] to experience speed (see F1 - Details and Options). Hope this helps. And hope JVM can swap in future versions. I wish Mathematica would have a command to force writing into Pagefile. It would be big advance for large file processing if one could set the whole mathematica session to an allowed percentage of - let's say 80% - available RAM to force swapping much earlier resulting in free RAM for postprocessing. Try this oversetting "+1GByte" on your own. And see in Task Manager and Ressource Monitor how your RAM behaves and when Pagefile writing starts.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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