changing the size of data being written to xls

4 次查看（过去 30 天）
Rebecca Hadley
Rebecca Hadley 2019-3-4
回答： Walter Roberson 2019-3-4
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
matrix set up.png
  1 个评论
Walter Roberson
Walter Roberson 2019-3-4
Your data is about 6 times too large for a .xls or .xlsx file, as those are restricted to 1048576 rows.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（2 个）

Star Strider
Star Strider 2019-3-4
Have you considered other options such as dlmwrite (link) or writetable (link)?
They might be more suitable.
  7 个评论
显示 5更早的评论
Guillaume
Guillaume 2019-3-4
@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
Star Strider 2019-3-4
@Guillaume — Thank you for your contribution. (I always delete my Answer if another Answer is Accepted and mine is not.)

请先登录，再进行评论。

Walter Roberson
Walter Roberson 2019-3-4
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.

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2018a

Community Treasure Hunt

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

Start Hunting!

Translated by