Write cell array with multiple elements in the cell to Excel File
13 views (last 30 days)
Show older comments
Is there a way to write a cell array in which somes cells contain more than one element (vector of numbers) into an excel or cvs file?
I have a cell array that contains a column in which the cells contains vectors of 1-3 numbers, when I tried to use xlswrite it only wrote the cells that contained one element and skipped over the cells which contained more than one element.
Is there another function that I can use instead, or can I do something to structure my data differently so that I can use the xlswrite function?
I have attached my data, so you can see what I mean.
0 Comments
Answers (2)
Bob Thompson
on 10 Apr 2019
xlswrite is not going to write multiple contents of a single cell because an Excel cell can only contain one piece of information at a time, so xlswrite is not sure which bit of data you want in that particular cell, and decides to not write any.
Your best bet would be to reorganize your data. Unfortunately, I am unable to look directly at your data (it's on my end, not yours), but from what you described it should be possible to put all of your data into a 2D array. I have given a method here that isn't the most efficient, but I don't know how to vectorize it off the top of my head. Others are free to comment with better ideas.
data = {};% Your cells with data
nums = NaN(size(data,1),3);
for i = 1:size(data,1);
nums(i,:) = data{i,column}; % column is the column of your data
end
Peter Perkins
on 10 Apr 2019
A cell array is not realluy a good choice for storing your data. Use a table, and use writetable to write out that "ragged" array.
>> t = cell2table(data,'VariableNames',{'FileName' 'List' 'X' 'Y'});
>> head(t)
ans =
8×4 table
FileName List X Y
_____________________________________ ____________ _ _
{'numOfStream_1numTarget_3other.wav'} {1×3 double} 3 2
{'numOfStream_2numTarget_2other.wav'} {1×2 double} 2 2
{'numOfStream_3numTarget_2other.wav'} {1×2 double} 2 2
{'numOfStream_4numTarget_1other.wav'} {[ 21.1676]} 1 2
{'numOfStream_5numTarget_1other.wav'} {[ 20.9014]} 1 2
{'numOfStream_6numTarget_2other.wav'} {1×2 double} 2 2
{'numOfStream_7numTarget_3other.wav'} {1×3 double} 3 2
{'numOfStream_8numTarget_2other.wav'} {1×2 double} 2 2
>> writetable(t,'test.csv');
This creates a file like
FileName,List_1,List_2,List_3,X,Y
numOfStream_1numTarget_3other.wav,5.33195011337869,12.1648752834467,12.1648752834467,3,2
numOfStream_2numTarget_2other.wav,13.8178911564626,25.8402267573696,,2,2
numOfStream_3numTarget_2other.wav,8.86573696145125,15.5477097505669,,2,2
numOfStream_4numTarget_1other.wav,21.167619047619,,,1,2
numOfStream_5numTarget_1other.wav,20.9013605442177,,,1,2
numOfStream_6numTarget_2other.wav,14.5290022675737,24.5581405895692,,2,2
[snip]
where the "ragged" array has been written to three columns in the file.
6 Comments
Walter Roberson
on 26 Oct 2023
outfilename = 'test.csv';
V1 = [11;12]; V2 = [4 5 6; 7 8 9]
T = table(V1, V2)
TT = rowfun(@(varargin)cell2table(cellfun(@mat2str, varargin, 'uniform', 0),'VariableNames', T.Properties.VariableNames), T)
writetable(TT.Var1, outfilename, 'QuoteStrings', 'all')
%cross-check
dbtype(outfilename)
%now see if we can read it back in
opts = detectImportOptions(outfilename, 'Delimiter', ',');
opts = setvartype(opts, opts.VariableNames', 'char')
tempT = readtable(outfilename, opts);
recoveredT = cell2table(cellfun(@str2num, table2cell(tempT), 'uniform', 0), 'VariableNames', tempT.Properties.VariableNames)
Runcong Kuang
on 7 Nov 2023
Edited: Runcong Kuang
on 7 Nov 2023
Could you help me understand this line:
TT = rowfun(@(varargin)cell2table(cellfun(@mat2str, varargin, 'uniform', 0),'VariableNames', T.Properties.VariableNames), T)
?
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!