writetable replace NaN with blanks in Matlab

Question

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.

Answer 1

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:

---

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

Answer 2

I honestly think the most straight-forward way to output the data in the format you describe is to use xlswrite as Sardar did in his answer. However, if you really want to use writetable, the only option I can think of is to encapsulate every value in the table in a cell array and replace the nan entries with empty cells. Starting with this sample table T with random data and nan values:

T = table(rand(5,1), [nan; rand(3,1); nan], 'VariableNames', {'A', 'C'});

T = 
            A                    C        
    _________________    _________________

    0.337719409821377                  NaN
    0.900053846417662    0.389738836961253
    0.369246781120215    0.241691285913833
    0.111202755293787    0.403912145588115
    0.780252068321138                  NaN

Here's a general way to do the conversion:

for name = T.Properties.VariableNames  % Loop over variable names
  temp = num2cell(T.(name{1}));        % Convert numeric array to cell array
  temp(cellfun(@isnan, temp)) = {[]};  % Set cells with NaN to empty
  T.(name{1}) = temp;                  % Place back into table
end

And here's what the table T ends up looking like:

T = 
             A                      C         
    ___________________    ___________________

    [0.337719409821377]    []                 
    [0.900053846417662]    [0.389738836961253]
    [0.369246781120215]    [0.241691285913833]
    [0.111202755293787]    [0.403912145588115]
    [0.780252068321138]    []

And now you can output it to a file with writetable:

writetable(T, 'sample.csv');