I have a cellarray (100000000*2cell) ,how can I save the cell in excel more quickly ?

5 views (last 30 days)
if i choose the fonction csvwrite(), i think that will be a long time to wait , do you have some good ideas saving it more quick? thanks
Stephen23 on 15 Oct 2014
A cell array does seem like a pretty inefficient storage choice for this data. Why not simply in a numeric array? And for that matter, saving the data in compressed binary form (eg .mat).

Sign in to comment.

Accepted Answer

per isakson
per isakson on 15 Oct 2014
Edited: per isakson on 16 Oct 2014
A good way to learn about performance is testing. Thus, I made a little comparison. I use a smaller array to save time. I think elapsed time increases linearly with array size (for large arrays).
>> double2file_performance
Elapsed time is 38.712062 seconds.
Elapsed time is 2.991493 seconds.
Elapsed time is 0.032539 seconds.
Elapsed time is 0.037617 seconds.
where double2file_performance is
M = [
8500000190000013093,8500000840000005660 ];
m1e6 = repmat( M, [1e5,1] );
csvwrite( 'c:tmp\test.csv', m1e6 )
fid = fopen( 'c:tmp\test.txt', 'w' );
fprintf( fid, '%f,%f\n', m1e6 );
fclose( fid );
fid = fopen( 'c:tmp\test.bin', 'w' );
cnt = fwrite( fid, m1e6 );
fclose( fid );
save( 'c:tmp\test.mat', 'm1e6', '-v6' )
Convert from a double to a cell array and back
tic, c1e6 = num2cell( m1e6 ); toc
tic, n1e6 = cell2mat( c1e6 ); toc
Elapsed time is 1.073974 seconds.
Elapsed time is 1.679481 seconds.
and check the sizes of the arrays
>> whos
Name Size Bytes Class Attributes
c1e6 1000000x2 240000000 cell
m1e6 1000000x2 16000000 double
n1e6 1000000x2 16000000 double
Comments on this comparison
  • csvwrite and fprintf create text files. fwrite and save creates binary files. I used save(...'-v6') because it is a bit faster than default.
  • csvwrite is slow. That's partly because it is a wrapper of dlmwrite, which in turn is a wrapper of sprintf and fwrite.
  • fprint is an order of magnitude faster that csvwrite. I think fprint is the fastest way to write to a text file.
  • writing to a binary file is two orders of magnitude faster than fprint to a text file.
The content of the text files depend on the precision specification used when writing. Of course it does! However, with cvswrite precision cannot be specified and the default is not appropriate in this case. The first lines of the file, test.csv, are
which might not be the expected result. With dlmwrite it is possible to specify precision.
fprintf( fid, '%d,%d\n', m1e6 );
saves 30% in elapsed time and 25% in file size compared to
fprintf( fid, '%f,%f\n', m1e6 );
without loosing any precision.

More Answers (2)

Iain on 15 Oct 2014
Here's a better idea.
Don't save it to a text file. 100 million lines of text, of 40 bytes per line (more if nicely formatted), is a 40 * 100M = 4GB file. It is a truly immense file saved in an extremely inefficient fashion.
You have 19 significant figures. - I'm guessing these numbers are unsigned 64 bit integers? Why not write them to file as such?
This way, you only need a few hundred characters (as text, to describe the filetype [possibly even including the matlab code to read the file]) at the top, then 8 bytes per number - 16 bytes per line. 16 * 100M = 1.6GB file. It will write to disk in less than half the time, use up less space, AND be easier to read chunks of it at a time.

Orion on 14 Oct 2014
if your cell array is really a 100000000*2 cell, it is too big for Excel (maximum number of line : 2^20 = 1048576).
you should consider to write your data in a simple text file.
or if you reaaly need to write it in a xls file, you need to split your cell in smaller elements and write each "small" cell in a new sheet.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!