Importing a large Excel file

Question

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.

Answer 1

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.

Answer 2

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

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

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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:

ExcelActivate["prices-new.xlsx"];
rawpriceworksheet = 
Join[Excel["a1:jaa1000"], Excel["a1001:jaa2000"], 
Excel["a2001:jaa3000"], Excel["a3001:jaa4000"], 
Excel["a4001:jaa4593"]];
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"];
ExcelActivate["Baskets"];
data1 = Excel["a1:zz100"]
ExcelActivate[index <> "-use.xlsx"];
ExcelActivate["Adjustments"];
data2 = Excel["a1:zz100"]

I expect that this approach is limited only by available memory

Answer 7

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.