bugGNU Octave - Bugs: bug #56489, System freeze due to Excel import

 
 

bug #56489: System freeze due to Excel import

Submitted by:  None
Submitted on:  Thu 13 Jun 2019 11:59:22 AM UTC  
 
Category:  Octave Function Severity:  3 - Normal
Priority:  5 - Normal Item Group:  None
Status:  Need Info Assigned to:  None
Originator Name:  Frank Knappe Originator Email:  -email is unavailable-
Open/Closed:  Open Release:  5.1.0
Operating System:  Microsoft Windows

Add a New Comment (Rich Markup)
   

You are not logged in

Please log in, so followups can be emailed to you.

 

( Jump to the original submission)

Tue 25 Jun 2019 08:49:36 PM UTC, comment #6: 

Thanks for testing.

(1) I've seen the system clock stall as well when I was declaring huge arrays (in other SW), at one time I left my system running overnight to see it responding again the next morning. Stopping the SW then again introduced a quite conspicuous hang :-)

(2) Yes I know that invoking Excel/COM is about 10 times faster (with reasonably sized spreadsheets) than OCT (native octave code). Next slower are the Java-based interfaces. OXS (OpenXLS, only for .xls) is the fastest of them by a small margin, POI the slowest.

What do you want me to do with this bug report now that you have an alternative?
I'm willing to look into it; if so, could you upload your spreadsheet somewhere (wetransfer, Google drive, whatever) so that I can try myself?
Otherwise I'll close this bug report with "won't fix" or maybe just lower priority and set it to "postponed", as the size of your Excel file is not very common.

FYI:
The relative small size of some output arrays is due to xlsread (or rather its helper functions) strip the num, txt and raw arrays of empty outer rows and columns after processing. They're much bigger while reading.
You can try again without stripping as follows:

[NUMARR1, TXTARR1, RAWARR1, LIMITS1] = xlsread ('v201981_rawdata.xlsx', 1, [], 'OCT', struct (" strip_array", 0)); ## watch out for line break; all on one line

and then run "whos".
As I wrote the size of RAWARR reported by "whos" is deceiving, its real RAM occupation is a multiple, up to tenfold.

Philip Nienhuis <philipnienhuis>
Project Member
Mon 24 Jun 2019 08:21:37 PM UTC, comment #5: 

>> pkg load windows
>> xlsfinfo ("v201981_rawdata.xlsx", "com")

Checking requested interface(s):
COM*; (* = default interface)
  1: v201981_rawdata                  (Used range ~ A1:AU170017)
ans = Microsoft Excel Spreadsheet

>>


>> [NUMARR1, TXTARR1, RAWARR1, LIMITS1] = xlsread('v201981_rawdata.xlsx', 1, [],'com');


works flawlessly and also rather quick (~30s or less)

>> whos

Variables in the current scope:

   Attr Name         Size                     Bytes  Class
   ==== ====         ====                     =====  =====
        LIMITS1      1x1                         96  struct
        NUMARR1 170016x46                  62565888  double
        RAWARR1 170017x47                  43719134  cell
        TXTARR1      1x47                       686  cell
        ans          1x27                        27  char

Total is 15811610 elements using 106285831 bytes

As also the clock wasn't running any more in the previous tests I  assume that the system was frozen.

BR
Frank

Anonymous
Mon 17 Jun 2019 06:56:26 PM UTC, comment #4: 

Right, that makes 64 MB for the numeric data alone if they were written as doubles (but they are written as better-compressible text strings). Yeah I see that you're hitting some resource limits.

To invoke the COM interface you'd need to also load the windows package and have MS-Excel installed.
Have you ever checked the memory consumption of Excel when it has read the file? Could be a lot more than Octave uses.

Another worthwile try would be:

## Open file pointer
xls = xlsopen ('v201981_rawdata.xlsx', 0);
## Read raw data
RAWARR1 = xls2oct (xls, 1);
## Close file pointer, along the lines release associated memory
xls = xlsclose (xls);
## Split data in numeric and text parts
[NUMARR1, TXTARR1, LIMITS1] = parsecell (RAWARR1);

(analogous to what xlsread itself does, but in another order).

A little warning: you'd get ~64 MB of doubles in NUMARR1, but a maybe tenfold memory consumption for the cell arrays TXTARR1 and RAWARR1 (cell arrays have an unbelievable RAM overhead not shown in "whos"), so those latter ones might consume > 1 GB.

BTW it may be that your system just appears to freeze, but you can only be sure if you look in Task Manager, Processes or Performance tab.
Given the size of your data I suspect your system may silently just be busy with the regexps and may even be trashing (swapping RAM in and out from/to virtual memory on disk). If the latter I'm not surprised that things appear to grind to a halt.

Philip Nienhuis <philipnienhuis>
Project Member
Mon 17 Jun 2019 09:22:22 AM UTC, comment #3: 

I have to stand corrected for some values.

Some columns we hided in the excel file. Overall the Excel file has
47 columns (~30 filled with data) and 170017 lines. An export to txt yields a file size ~52MB.

>> xlsfinfo ("v201981_rawdata.xlsx", "oct")

Checking requested interface(s):
OCT*; (* = default interface)
warning: strmatch is obsolete; use strncmp or strcmp instead
  1: v201981_rawdata                  (Used range ~ A1:AU170017)
ans = Microsoft Excel Spreadsheet

4>> xlsfinfo ("v201981_rawdata.xlsx", "com")
Checking requested interface(s):

COM is not supported.

Next mistake: Its not just 3 month but 3 years and 3 month:-(

I'll try the other suggestions later.

BR
Frank

Anonymous
Sat 15 Jun 2019 01:10:44 PM UTC, comment #2: 

I made a mistake: you mentioned 170017 entries (I guess cells with content), I multiplied it by 5 columns but that is not needed as it would be included in your total of 170017.
The actual size of the data (the different parts that compress less well) would be 1.3 MB not 6.6. So the compressed internal spreadsheet overhead would be > 35 MB.
Clearly there seems to be an issue with your spreadsheet.

Philip Nienhuis <philipnienhuis>
Project Member
Fri 14 Jun 2019 09:02:30 PM UTC, comment #1: 

Maybe export the file (from excel) as a .csv file and read that. Have you tried using the COM interface rather than OCT?

37 MB exploding to > 6.6 GB is weird. But OTOH to hold 6*170017 values a 37 MB Excel file (and that is zipped size, FYI) is insanely big. The raw data would occupy just 170017*5*8 bytes ~6.8 MB., so there would be 30 MB zipped (heaven may know how much if unzipped) just for the spreadsheet overhead, and that is quite out of proportion.
So, it may be that your .xlsx file contains e.g., loads of empty but formatted cells that also have to be expanded into RAM before Octave can decipher they're empty. So have you tried conveying an actual range (~ "A1:E170017") rather than letting xlsread sort it out? Or can you try increasing that range until you hit OOM errors?
A first try to see what the file looks like would be
xlsfinfo ("rawdata.xlsx", "oct")
or
xlsfinfo ("rawdata.xlsx", "com")
and see how that goes.

FYI I sometimes read several years rather than 3 months of 10 min. values (tidal data) and I don't see this problem.

Maybe you can upload the file somewhere or can I download it, than I can try with later (dev) Octave versions.

Philip Nienhuis <philipnienhuis>
Project Member
Thu 13 Jun 2019 11:59:22 AM UTC, original submission:  

Hi all,

I have a 37MB Excel file (one work sheet only) which has 170017 entry lines (3 month of 10min values)

date   time   value1 value2 value3

I try to import this file into octave using:
[NUMARR1, TXTARR1, RAWARR1, LIMITS1] = xlsread('v201981_rawdata.xlsx', 1, [],'OCT');

System is W7 prof 64bit with 8GB RAM. The import starts, octave-gui eats up to 6.6GB RAM and then the system freeze completely.

Is there a way to import this file into octave without this issue?

BR
Frank

Anonymous

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

No files currently attached

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by None (Submitted the item)
  •  

    Do you think this task is very important?
    If so, you can add your encouragement to it.
    This task has 0 encouragements so far.

    Only project members can vote.

     

     

     

    Follows 1 latest change.

    Date Changed by Updated Field Previous Value => Replaced by
    2019-06-14 philipnienhuis StatusNone => Need Info

    Back to the top


    Powered by Savane 3.5