How to extract the numerial values out of readtable output?

Good afternoon! My apologies for the many questions related to readtable.

Attached is my data file. I use the below command to read the Excel file into Matlab

T = readtable('AA.xlsx');

I know there will be 1-5 columns (unknow amount) that will be scalar strings. They will all be located towards the rightmost side of the table.

Here is my question. How do I get the numerical columns out of the table T. For example, in the above example, I have a total of 48 columns, but only 47 of them are numerical. What I need is

Dat = T(:,[1:47]);

How do I get Dat (numerical values of the table) in a more automatic way, because the 47 number could vary from file to file.

Many thanks!

Answer 1

[Star Strider]

It is possible, although not without some serious conniptions.

Originally:

AA_Table = readtable('AA.xlsx');
headers = AA_Table.Properties.VariableNames;

then:

AA_C = table2cell(AA_Table);                                    % Convert To Cell Array
NumIdx = cellfun(@isnumeric,AA_C);                              % Determine Numeric Values
Vrbls = headers(NumIdx(1,:));                                   % Get Corresponding Headers (If Needed Later)
NrsC = reshape(AA_C(NumIdx), size(AA_C,1), []);                 % Reshape Vector To Matrix
Nrs = cell2mat(NrsC);                                           % Numeric Array (Finally)

The only works if specific columns are all numeric and other columns are all not numeric. It willl fail otherwise. For what it’s worth, it works here.

[Leon]

Many thanks for the solution.

Why is it so hard to do so in an upgraded version of xlsread? I mean the old xlread function can do this easily.

[Star Strider]

As always, my pleasure.

It’s not xlsread. This just extracts the data from a table. I originally thought that it was preferable to use readtable with your data, since you needed to refer to your data using the variable names, and a table makes that straightforward.

[Nancy Hammond]

Why is this so complicated?

We all use excel products for data?

In all these years, why don't you have a simple procedure for reading date with dates and numeric values?

[Star Strider]

For the record, I’m a volunteer here. I have no significant connection with MathWorks, ancd certainly do not make any design decisions.

There have been a number of upgrades to readtable in the last 2½ years since this was posted. It generally imports dates as datetime variables if it recognises the format, otherwise it usually imports them as character arrays that can then be used with 'InputFormat' to convert them to datetime arrays.

Not all Excel files are easy to read because they may not have been created correctly, one example being character variables of numeric values instead of the numeric values themselves. Beyond that, detectImportOptions can be used to specify how fields are read in many situations, however it’s sometimes easier to do the conversion on the immported files instead.

With respect to reading the dates, this file is actually a relevant example —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/223011/AA.xlsx', 'VariableNamingRule','preserve')

T1 = 3×48 table
    STNNBR    CASTNO    BTLNBR    BTLNBR_FLAG_W       DATE        Year    Month    Day            TIME            LATITUDE    LONGITUDE    DEPTH    CTDPRS    CTDTMP    CTDSAL    CTDSAL_FLAG_W    SALNTY    SALNTY_FLAG_W    CTDOXY    CTDOXY_FLAG_W    OXYGEN    OXYGEN_FLAG_W    SILCAT    SILCAT_FLAG_W    NITRAT    NITRAT_FLAG_W    NITRIT    NITRIT_FLAG_W    PHSPHT    PHSPHT_FLAG_W    AMMONI    AMMONI_FLAG_W    TCARBN    TCARBN_FLAG_W    ALKALI    ALKALI_FLAG_W    PH_TOT    PH_TOT_FLAG_W    PH_TMP    CHLORA    PON     POC     POC/PON    PON_1    POC_1    LINE    Accession         EXPOCODE    
    ______    ______    ______    _____________    ___________    ____    _____    ___    ____________________    ________    _________    _____    ______    ______    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    ______    ____    ____    _______    _____    _____    ____    __________    ________________

      1         1         1             2          12-Aug-2011    2011      8      12     31-Dec-1899 14:21:41     47.11       -126.05     2292     2309.6    1.759     34.636          2          34.633          2           75.6           2          75.61           6           185.3          2          40.02           2           0.05           2           2.89           2           0.01           2          2386.8          6          2436.3          2          7.447           3             25      -999     -999    -999     -999      -999     -999      1      1.5746e+05    {'32WC20110812'}
      1         1         2             2          12-Aug-2011    2011      8      12     31-Dec-1899 14:21:59     47.11       -126.05     2292     2309.6    1.759     34.636          2            -999          9             72           2           -999           9            -999          9           -999           9           -999           9           -999           9           -999           9            -999          9            -999          9           -999           9           -999      -999     0.37    3.75     10.2      0.38     3.86      1      1.5746e+05    {'32WC20110812'}
      1         1         3             2          12-Aug-2011    2011      8      12     31-Dec-1899 14:23:39     47.11       -126.05     2292     2286.3    1.759     34.636          2            -999          9           71.9           2           -999           9          185.22          2          39.98           2           0.04           2           2.91           2           0.01           2          2385.8          2          2429.4          6          7.442           3             25      -999     -999    -999     -999      -999     -999      1      1.5746e+05    {'32WC20110812'}

T1.DateTime = T1.DATE + timeofday(T1.TIME);                      % Combine 'DATE' & 'TIME' Into A Single Array

T1.DateTime

ans = 3×1 datetime array
   12-Aug-2011 14:21:41
   12-Aug-2011 14:21:59
   12-Aug-2011 14:23:39

Then, ‘DATE’, ‘Year’, ‘Month’, ‘Day’ and ‘TIME’ can be deleted and replaced by ‘DateTime’. They can then be put in a cell array because they are different variable types, and only cell arrays allow that, or the numerical values can be put into a matrix, and the ‘DateTime’ variable into a cell array by itself (or perhaps with ‘EXPOCODE’).

Reading uncomplicated Excel files has become fairly straightforward, actually.

.

Answer 2

[Arjun]

An additional comment:

var_name = T{row_start:end,[columns]}

OR

var_name = T{row_start:end,('column_name')}