# Import Large Excel File

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

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

Needs["JLink"];

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