Write 2d matrix to single Excel cell

I need to make an Excel file (or CSV) with a list of variable names in the first column (e.g. attached "Nm.mat" file), and their respective value in the second column. The values are a mix of scalars and matrices (e.g. attached "Val.mat" file). I used the following command to generate the csv file after loading Nm and Val to the workspace:

>> writetable(table(Nm,Val),'test.csv','WriteVariableNames',false);

Unfortunately, it writes all the values from the 12x12 matrix in a long row array with one value per cell (see "test_is.csv"). I would like it to arrange them all into a single cell as in the "test_shouldbe.csv" file.

Please help me generate the "test_shouldbe.csv" file. It's fine if it writes it to an Excel instead as that's ultimately what it's going to be. Thanks in advance!

Answer 1

[Voss]

load('Nm.mat');
load('Val.mat');
t = table(Nm,Val);
% make a cell array containing the table's contents
C = table2cell(t);
% convert the second column to character vectors representing the matrices
C(:,2) = cellfun(@mat2str,C(:,2),'UniformOutput',false)

C = 2×2 cell array
    {'Name1'}    {'0'                                                                                                                                                                                                                                                                                                 }
    {'Name2'}    {'[1 -1 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 1 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0;0 0 0 0 0 0 0 0 0 0 0 0]'}

% write the cell array to file
writecell(C,'test_shouldbe.csv');

[Bastian Kindereit]

Hello,

Thanks for the very prompt response! The output is still on a single line for me, albeit separated by semicolons to indicate a new row. Is there any way to get it to actually go to a new line? Thanks again

[Voss]

I see now. I wasn't able to see the complete contents of the cell in test_shouldbe.csv before.

You can try the following. (The resulting csv for the sample data you shared is attached, as "test_shouldbe.csv".)

load('Nm.mat');
load('Val.mat');
t = table(Nm,Val);
% make a cell array containing the table's contents
C = table2cell(t);
% convert the second column to character vectors representing the matrices
C(:,2) = cellfun(@mat2str_multi_line,C(:,2),'UniformOutput',false)

C = 2×2 cell array
    {'Name1'}    {'"0"'                                                                                                                                                                                                                                                                                                 }
    {'Name2'}    {'"[1 -1 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 1 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0]"'}

% write the cell array to file
writecell(C,'test_shouldbe.csv');

% check the file (have to specify ('Delimiter', ',') to get it to read right)
readcell('test_shouldbe.csv','Delimiter',',')

ans = 2×2 cell array
    {'Name1'}    {'"0"'                                                                                                                                                                                                                                                                                                 }
    {'Name2'}    {'"[1 -1 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 1 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0↵0 0 0 0 0 0 0 0 0 0 0 0]"'}

function s = mat2str_multi_line(M)
s = ['"' strrep(mat2str(M),';',sprintf(newline())) '"'];
end

[Bastian Kindereit]

Wow, thanks so much, that worked perfectly!

[Voss]

Excellent! You're welcome!