Write cell array with multiple elements in the cell to Excel File

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.

Answer 1

[Bob Thompson]

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

[Danna Pinto]

Thank you for your answer.

What do you mean the column of my data? Do you mean the column of the cell array that contains the vectors?

Also something doesn't seem to work with the code, this is what I did with my data

data_cell= {nameOfTrialCell' TargetTimes_Cell' targetNum_cell' Trialtype_cell'}; 
nums= NaN(size(data_cell,2),3);
for i = 1:size(data_cell,2);
    nums(i,:)= data_cell{i,2};
end

and I got this error

The following error occurred converting from cell to double:

Error using double

Conversion to double from cell is not possible.

Answer 2

[Peter Perkins]

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.

[Danna Pinto]

Thank you, but my final goal is to add this data to another data sheet that I already have saved.

This is the way I did it with xlswrite, is there something like this a I could do with writetable

[num,txt,raw] = xlsread('condition_list_AROMA_fixed.xlsx');
raw{1,8} = 'TargetTimes'
raw{1,9} = 'TargetNum';
raw{1,10} = 'Trialtype';
data= [nameOfTrialCell' TargetTimes_Cell' targetNum_cell' Trialtype_cell'];
for i=1:40
    raw{i+1,8} = data{i,2};
end

for i=1:40
    raw{i+1,9} = data{i,3};
end

for i=1:40
    raw{i+1,10} = data{i,4};
end

filename= 'conditionList.xlsx'
xlswrite(filename,raw,1,'A1');

[Runcong Kuang]

@Peter Perkins, I like yours, but how can I write to file without those "ragged data" being expanded to multiple columns?

[Walter Roberson]

@Runcong Kuang

The only way to avoid it would be to convert the columns with variable-length data into a (single) row of text, and ask writetable to write with 'QuoteStrings','all'

[Runcong Kuang]

@Walter RobersonThanks.

I find this post helpful for the first step.

For the second step, I am not sure about how it makes it different.

[Walter Roberson]

outfilename = 'test.csv';

V1 = [11;12]; V2 = [4 5 6; 7 8 9]

V2 = 2×3

4 5 6 7 8 9

T = table(V1, V2)

T = 2×2 table
    V1        V2     
    __    ___________

    11    4    5    6
    12    7    8    9

TT = rowfun(@(varargin)cell2table(cellfun(@mat2str, varargin, 'uniform', 0),'VariableNames', T.Properties.VariableNames), T)

TT = 2×1 table
            Var1         
      V1          V2     
    _____________________

    {'11'}    {'[4 5 6]'}
    {'12'}    {'[7 8 9]'}

writetable(TT.Var1, outfilename, 'QuoteStrings', 'all')

%cross-check
dbtype(outfilename)

1     V1,V2
2     "11","[4 5 6]"
3     "12","[7 8 9]"

%now see if we can read it back in
opts = detectImportOptions(outfilename, 'Delimiter', ',');
opts = setvartype(opts, opts.VariableNames', 'char')

opts = 
  DelimitedTextImportOptions with properties:

   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
       TrailingDelimitersRule: 'ignore'
                EmptyLineRule: 'skip'
                     Encoding: 'UTF-8'

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'V1', 'V2'}
                VariableTypes: {'char', 'char'}
        SelectedVariableNames: {'V1', 'V2'}
              VariableOptions: [1-by-2 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

   Location Properties:
                    DataLines: [2 Inf]
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview

tempT = readtable(outfilename, opts);
recoveredT = cell2table(cellfun(@str2num, table2cell(tempT), 'uniform', 0), 'VariableNames', tempT.Properties.VariableNames)

recoveredT = 2×2 table
    V1        V2     
    __    ___________

    11    4    5    6
    12    7    8    9

[Runcong Kuang]

Thanks @Walter Roberson for your detailed answer.

Could you help me understand this line:

TT = rowfun(@(varargin)cell2table(cellfun(@mat2str, varargin, 'uniform', 0),'VariableNames', T.Properties.VariableNames), T)

?