access data from a table

2 次查看（过去 30 天）
Jenny
Jenny 2016-2-18
回答： Jenny 2016-2-19
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
  1 个评论
Jenny
Jenny 2016-2-18
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 ?

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Jenny
Jenny 2016-2-19
I solved this using str2double since the data inside the Table was class string.

更多回答（1 个）

Peter Perkins
Peter Perkins 2016-2-18
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.
  1 个评论
Jenny
Jenny 2016-2-18
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by