access data from a table

I am trying to extract data from a table, T. I am reading in the xlsx data with no headers (I am specifically not using xlsread). The file is attached.

Col 1 = DateTime (dd/mm/yyyy HH:MM:ss)

Col2 - Col 13 = numeric data

I would like to convert the DateTime (col 1 in table T) with datenum.

I create a DataArray with table2array with only numeric data (col 2:13 in table T) and DataArray is class cell and not double.

When I try to convert the dates with datenum I get an error. I have tried various ways of accessing the 'cell' but to no avail.

When I try to extract the data from DataArray I get only class cell and cannot convert to class double.

If I use {} brackets to extract the data, I get class char and only the first data point.

If I used () brackets to extract the data, I get class cell and not double - I need class double.

How to extract my DateTime and convert with datenum and how do I extract my numeric data as class double ?

[filename,pathname] = uigetfile({'*.*';'*.txt';'*.csv';'*.xlsx'});
T = readtable(filename,'ReadVariableNames',false','ReadRowNames',false');
[row, col]=size(T);

% extract dates from table T
DateTime = table2array(T(3:row,1)); % class cell
% convert DateTime to datenum
Date = datenum(DateTime{1}, 'dd/mm/yyyy HH:MM:ss'); % returns only the first DateTime and not the entire column

% convert table to array and extract data
DataArray = table2array(T(3:end,2:13)); % DataArray is class cell and not double

Temp = DataArray(1:end,10); % class cell not double
Temp2 = DataArray{1:end,10}; % class 1 x 4 char (returns only 1st data point and not entire col) 

PingCount = NaN(length(DataArray),1); % class cell
Pressure = DataArray(1:end, 11); % class cell
Tilt = DataArray(1:end, 12); % class cell

% extract speed and direction based on col
ColData= input('What column group is the data in: options: 1, 2 or 3? ');

if ColData == 1;
    Spd = DataArray(1:row, 2);
    Dir = DataArray(1:row, 3);
    %Spd = Spd*100;
    %uCurr = Spd.*cos((90-Dir)*pi()/180); % E/W
    %vCurr = Spd.*sin((90-Dir)*pi()/180); % N/S
end

[Jenny]

I believe the problem with my DateTime is that the DateTime has occurrences where is is the format 'dd/mm/yyyy' with no HH:MM:ss. this occurs when the DateTime goes over midnight. How do i overcome this ?

Answer 1

[Jenny]

I solved this using str2double since the data inside the Table was class string.

Answer 2

[Peter Perkins]

Jenny, "datetime" is a datatype in MATLAB since R2014b. It appears that you are not using that, and that what you're calling "DateTime" is a cell array of timestamp strings that you've read from a spreadsheet. Probably best to not use the term "datetime" to avoid confusion.

Excel stores date/time values internally as numbers, but displays them as strings and that's how MATLAB reads them in. But Excel has this habit of leaving off the 00:00 for times exactly at midnight. I think maybe that's what you're running into. You can fix this by searching your cell array of strings for any that are not 19 (or whatever) characters long, and tacking on '00:00:00' before calling datenum.

Another option is to read the spreadsheet using basic mode (see the doc for readtable), and you'll get EXCEL serial date numbers, which you can convert to datenums.

Hope this helps.

[Jenny]

Thank you. I have solved the datenum problem and have renamed the DateTime variable. But I still have the problem of extracting data from cell / tables as class double.