Not enough memory resources when linking Excel with matlab

Hi everyone,

I've experienced an error of lack of memory resources, this error appears when I write data from MATLAB to excel files through xlswrite() function. I think that error is caused by some misconfiguration of excel because my friends use same matlab version (2017b) with the same code and they did not get any error. Any help will be appreciated.

What I've tried:

-Reinstalling matlab 2017b.

-Repairing excel (Through windows settings).

Thank you.

[dpb]

How large is the array attempted to write? Can you do something as simple as

xlswrite('text.xls',pi)

from the command line or does it also error?

What release of Excel are you using? Is it a locally installed desktop version or are you on a 365 subscription, perhaps?

Are you sure you are writing to a folder that have write permission for and that you do have available disk space?

[Rik]

Is there a difference in memory on your machine vs your friend's?

I know that xlswrite can be quite a resource hog, especially when writing to xls instead of xlsx, so there might not be a real solution.

[Edison Gabriel]

@dpb xlswrite('text.xls',pi) work well, the problem appears when I use it with other .m files of my project. Unfortunately I can't attach code due to confidential reasons.

About subscription, it is office365.

Yeah, I manage only one user in my computer.

[Edison Gabriel]

@Rik

We all have at least 100gb (SSD) of free space and 16gb of RAM.

[dpb]

"...xlswrite('text.xls',pi) work well, the problem appears when I use it with other .m files..."

Then the Q? you didn't answer is how big is the array being written and what does it consist of (all numeric, cell array, ...) and how much other memory is being used in the m-file functions? That the small write works shows the problem isn't in there being something going wrong in the base function or Excel itself.

For testing/debugging, you can try using a random array of the same size and data type from the command line and see if that works outside the m-files to find out if it is other memory usage, maybe. If that doesn't succeed, try cutting the size down by factors of 2 and see just where it succeeds and compare that to memory.

And, of course, you can use @Harald's suggestion and try converting the output array to a table and then using writetable to see if it can do the same thing with fewer resources. It may, if I recall correctly, xlswrite creates a second workbook as well as the one it ends up with; don't know if they've worked around that later or not; the internals of writetable are opaque.

If your particular machine has something else installed and in the background that the other users don't have, that could also be a contributing factor in one succeeding and another not.

While you may not want to share data and large chunks of code, certainly it should be possible to post the pertinet snippet of code and a description or a sample dataset that is either randomized or otherwise obfuscated to illustrate without giving away anything proprietary.

Answer 1

[Harald]

Hi,

my recommendation would be to try writetable, writematrix, or writecell instead of xlswrite. I have no way of checking quickly whether this was already available in R2017b but in newer releases, there is a UseExcel name-value argument. Ensure that you set it to false if that is not the default in R2017b.

If you can provide more details on how you use xlswrite, I can better advise which of those functions to use.

Best wishes,

Harald

[dpb]

The new(ish) writeXXX family was not introduced until R2019a so those aren't available to @Edison Gabriel unless he can upgrade at least that far.

[Harald]

I just checked the documentation of these functions.

According to the Version History section, writematrix and writecell were indeed only introduced in R2019a, my apologies for this oversight.

The writetable function was introduced in R2013b, but may require some rewriting of the code compared to xlswrite.

[dpb]

Actually, the route

writetable(table(x),filename)           % x is numeric array to one variable table array

or

writetable(cell2table(x),filename)      % if x is cell array

might not be a bad workaround. I'd forgotten how far in front of the others writetable was (that just do the above internally before calling writetable themselves).

Have to adapt any other input arguments needed like sheet, range, etc., etc., to match the syntax difference, of course.

The potential catch here is the extra memory that would be needed for the table in addition to the original array. To make it work, one might have to convert to table first and then clear the original arrays...the table will take more memory than the original array will.