Matrix with strings and numbers

Dear all,

I have a column that contains thousands of the following elements (this is just a sample)

   A=[ '2008-12-05 15:59:57'
    '2008-12-05 15:59:57'
    '2008-12-05 15:59:58'
    '2008-12-05 15:59:58'
    '2008-12-05 15:59:58'];

and I also have the following matrix that contains thousands of elements (this is just a sample)

    B=[39.44         4900
        39.44          100
        39.44          200
        39.44          100
        39.44          100];

How can I get a matrix, say C=[A B]; and then save it in an excel?

Is there any code?

Please bear in mind that A and B contain thousands of elements.

Also to create A I used the command datestr(D,31);

Thank you in advance

Answer 1

[Ameer Hamza]

Normal MATLAB array can only hold data of single type. You need to use cell array. Try something like this

A=[ '2008-12-05 15:59:57'
    '2008-12-05 15:59:57'
    '2008-12-05 15:59:58'
    '2008-12-05 15:59:58'
    '2008-12-05 15:59:58'];

B=[39.44         4900
    39.44          100
    39.44          200
    39.44          100
    39.44          100];

C = [num2cell(string(A)) num2cell(B)];
writecell(C, 'test.xlsx');

It will work on R2019a and later versions.

[ektor]

Thank you so much! But writecell does not work for v2018

What can I do?

I used

 xlswrite('test.xlsx',C);

but the first column containing the dates is empty!

[Ameer Hamza]

In R2018, you can use writetable

writetable(cell2table(C), 'test.xlsx', 'WriteVariableNames', false);

[ektor]

Thank you. It worked but it changes the format of the date column and I get for example

01/12/2008 09:30  41.64  3441  

instead of

 2008-12-01 09:30:02 41.64  3441  

Why is this happening?

Thank you so much.

[Walter Roberson]

I am not seeing any change in the format of the date column; I opened the file in excel to confirm that it is considered a text field there. When I readtable() it back in, it still knows it as text.

Note: I am not using Windows with Excel installed, so my system would not be talking to Excel to do the writing.

[ektor]

Hi Walter,

I use Windows with excel. Any other ideas how to solve this issue?

[Ameer Hamza]

ektor, maybe you can try to set the 'UseExcel' option to false (although it should is false by default). Alternatively, you can also experiment by setting it to true

writetable(cell2table(C), 'test.xlsx', 'WriteVariableNames', false, 'UseExcel', false);

and

writetable(cell2table(C), 'test.xlsx', 'WriteVariableNames', false, 'UseExcel', true);

Also, open the MS Excel file and check what datatype the Excel considers this cell to be

01/12/2008 09:30

Answer 2

[Steven Lord]

I recommend converting A into a datetime array and using that to create a timetable array.

[ektor]

Any sample code?

[ektor]

I found a way.

I copy directly the C matrix from data editor and I paste it inside an empty excel file.

It works. The only problem is that I get the operators '' '' for the date column

''2008-12-01 09:30:02'' 41.64 3441

Can I remove those within matlab before pasting into excel?