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.

Currently, I am working on a large time series dataset, which contains 928,586 rows and 35 time intervals in columns, in excel format (.xlsx) and using Import to read it in. Actually, I cannot use Import to handle this file since it gives me the Java heap error message: Import::nojmem: There was insufficient Java heap space for the operation. Try increasing the Java Virtual Machine heap size.

Here is only a part of my data after using Import.

data = {{"BRANCHNO", "Date", "JustDay", "JustDate", "JustMonth", "JustYear", 
  "DoM.Train", "DoM.Test", "Total TXN", "08:00:00", "08:30:00", 
  "09:00:00", "09:30:00", "10:00:00", "10:30:00", "11:00:00", 
  "11:30:00", "12:00:00", "12:30:00", "13:00:00", "13:30:00", 
  "14:00:00", "14:30:00", "15:00:00", "15:30:00", "16:00:00", 
  "16:30:00", "17:00:00", "17:30:00", "18:00:00", "18:30:00", 
  "19:00:00", "19:30:00", 
  "20:00:00"}, {"B0001", {2011, 1, 4, 0, 0, 0.}, "Tuesday", 4., 
  "January", 2011., 4., 4., 1538., 47., 59., 71., 77., 115., 111., 
  103., 75., 101., 121., 135., 113., 82., 88., 79., 72., 54., 13., 
  21., 1., "", "", "", "", ""}, {"B0001", {2011, 1, 5, 0, 0, 0.}, 
  "Wednesday", 5., "January", 2011., 5., 5., 1589., 20., 51., 33., 
  68., 97., 82., 101., 78., 104., 119., 137., 109., 114., 142., 85., 
  77., 62., 40., 51., 19., "", "", "", "", 
  ""}, {"B0001", {2011, 1, 6, 0, 0, 0.}, "Thursday", 6., "January", 
  2011., 6., 6., 1399., 3., 39., 61., 59., 63., 108., 66., 85., 108., 
  119., 114., 121., 94., 80., 62., 88., 56., 46., 16., 9., "", "", 2.,
   "", ""}, {"B0001", {2011, 1, 7, 0, 0, 0.}, "Friday", 7., "January",
   2011., 7., 7., 1510., 13., 55., 74., 70., 105., 95., 71., 82., 86.,
   113., 124., 91., 98., 76., 112., 84., 78., 64., 18., 1., "", "", 
  "", "", ""}}

I am wondering that:

  1. How can I import this file ?
  2. What is the best way to handle excel file ?
  3. After importing the excel file, can we save the data in other format (like MATLAB can save in .mat file) so that next time we can import it faster ?

Thanks for help

share|improve this question
1  
See this related question Speeding up Import and Export in CVS 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
    
Can you actually Import that? In my experience Importing .xlsx is considerably faster than .csv -- when it works . The native excel import gets flaky with jave heap errors and such with large files though (while .csv import is more robust..) –  george2079 Feb 11 at 18:59
    
correcting myself - Import[.xlsm] is slow as molasses when it works. Gotta use AbsoluteTiming not Timing.. –  george2079 Feb 12 at 1:53
    
@HDF5 actually for speed alone .mx is slightly better. Of course there is a file size trade off the .gz compression makes the file a good bit smaller. –  george2079 Feb 20 at 13:55

1 Answer 1

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.