Search code examples
excelmatlabmatlab-table

writetable replace NaN with blanks in Matlab

Given a Matlab table that contains many NaN, how can I write this table as an excel or csv files where the NaN are replaced by blanks?

I use the following function:

T = table(NaN(5,2),'VariableNames',{'A','C'})

writetable(T, filename)

I do not want to replace it with zeros. I want that the output file:

  1. has blanks for NaN and
  2. that the variable names are included in the output.
Solution

  • You just need xlswrite for that. It replaces NaNs with blanks itself. Use table2cell or the combination of table2array and num2cell to convert your table to a cell array first. Use the VariableNames property of the table to retrieve the variable names and pad them with the cell array.

    data= [T.Properties.VariableNames; table2cell(T)];
%or data= [T.Properties.VariableNames; num2cell(table2array(T))];
xlswrite('output',data);

    Sample run for:

    T = table([1;2;3],[NaN; 410; 6],[31; NaN; 27],'VariableNames',{'One' 'Two' 'Three'})

T =

  3×3 table

    One    Two    Three
    ___    ___    _____

    1      NaN     31  
    2      410    NaN  
    3        6     27

    yields:

    output

    Although the above solution is simpler in my opinion but if you really want to use writetable then:

    tmp = table2cell(T);             %Converting the table to a cell array
tmp(isnan(T.Variables)) = {[]};  %Replacing the NaN entries with []
T = array2table(tmp,'VariableNames',T.Properties.VariableNames); %Converting back to table
writetable(T,'output.csv');      %Writing to a csv file