changing the size of data being written to xls
1 vue (au cours des 30 derniers jours)
Afficher commentaires plus anciens
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](https://www.mathworks.com/matlabcentral/answers/uploaded_files/206767/matrix%20set%20up.png)
1 commentaire
Walter Roberson
le 4 Mar 2019
Your data is about 6 times too large for a .xls or .xlsx file, as those are restricted to 1048576 rows.
Réponses (2)
Star Strider
le 4 Mar 2019
They might be more suitable.
7 commentaires
Guillaume
le 4 Mar 2019
@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
le 4 Mar 2019
@Guillaume — Thank you for your contribution. (I always delete my Answer if another Answer is Accepted and mine is not.)
Walter Roberson
le 4 Mar 2019
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.
0 commentaires
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!