changing the size of data being written to xls

Hi,

Apologies in advance if this is a really obvious question. I have tried to look up solutions and have a go myself, but am struggling.

I am trying to write data from a pre-saved matrix (60480000x3 double cell array) using xls to an excel sheet (see image below). The matrix is really large and so writing the whole matrix to excel takes ages and not all the data is written across. I am not sure how to adjust my code so that I can break up the data into smaller chunks to write across. Is anyone able to help or make suggestions? I have tried to adjust line 3 in terms of the size function but I just keep getting errors. The code I am using is:

data=load('xyzdfp69.mat');
 f=fieldnames(data);
 for k=1:size(f,1)
   xlswrite('xyzdfp69.xlsx',data.(f{k}),f{k})
 end

[Walter Roberson]

Your data is about 6 times too large for a .xls or .xlsx file, as those are restricted to 1048576 rows.

Answer 1

[Star Strider]

Have you considered other options such as dlmwrite (link) or writetable (link)?

They might be more suitable.

[Rebecca Hadley]

I've had a look at dlmwrite and tried the following:

data=load('xyzdfp69.mat');
 f=fieldnames(data);
 for k=1:size(f,1)
   dlmwrite('xyzdfp69.txt',data.(f{k}),f{k})
 end
 
 

However, I get the error:

 Error using dlmwrite (line 104)
xyz is not a valid attribute or delimiter. Delimiter must be a single character.

Error in dfp69txt (line 4)
   dlmwrite('xyzdfp69.txt',data.(f{k}),f{k})

[Star Strider]

I don’t understand what you’re doing in the loop.

Try:

dlmwrite('xyzdfp69.txt',data)

without the loop and see if that works.

[Rebecca Hadley]

The loop was taken from a code provided on Mathwords by someone else (I am a Matlab newbie and so not great at writing code myself, as you can probably tell!).

I did the following (hopefully I've interpreted what you've written correctly):

data=load('xyzdfp69.mat');
dlmwrite('xyzdfp69.txt',data);

It produced the error:

Undefined function 'real' for input arguments of type 'struct'.

Error in dlmwrite (line 181)
                    str = sprintf('%.*g%+.*gi',precn,real(m(i,j)),precn,imag(m(i,j)));

Error in dfp69dlm (line 2)
dlmwrite('xyzdfp69.txt',data);

[Star Strider]

I’m lost.

I will delete my Answer in a few hours.

[Guillaume]

I will delete my Answer in a few hours.

Please, don't. There's essential information in there.

[Guillaume]

@Rebecca, it doesn't look like you understand what your code is doing, which is puzzling.

You're trying to save several fields of a structure into a file. As pointed out by Walter, the size of the matrices stored in the fields is much too large to be stored in excel files. You can save these as text files instead, but obviously, you can only store one field per text file.

dlmwrite doesn't know what to do with a structure. It expects matrices. Hence why you get an error. Admittedly the error could have been clearer, but you should have known you were passing a structure to it.

One possible solution:

data = load('xyzdfp69.mat');
fnames = fieldnames(data);
for fidx = 1:numel(fnames)
     dlmwrite(sprintf('xyzdfp69-%s', fnames{idx}), data.(fnames{fid}));
end

This will create one file for each field, named xyzdfp69- with the field name appended.

[Star Strider]

@Guillaume — Thank you for your contribution. (I always delete my Answer if another Answer is Accepted and mine is not.)

Answer 2

[Walter Roberson]

data=load('xyzdfp69.mat');
writetable(struct2table(data), 'xyzdfp69.csv');

This will create a file with one field per column of input variable. It will work with multiple variables stored in the file. It does, however, require that all of the variables have the same number of rows.

For example for a .mat with two variables stored in it, each 5 x 4, then it created

pqr_1,pqr_2,pqr_3,pqr_4,xyz_1,xyz_2,xyz_3,xyz_4
6,6,-9,2,-3,-1,1,0
9,3,1,7,5,2,-2,-8
5,-6,-7,0,-2,8,-8,-9
-1,0,-9,9,3,9,6,-2
-5,2,-1,-8,-4,5,-9,1

Again I caution that with your array sizes, these files cannot be opened in Excel, which is why you have to write as .csv instead of .xls or .xlsx

A different approach would involve splitting the data up into chunks that were written into different worksheets numbers.