How to transform a table to a matrix?

I have an excel file that I import to matlab by:

data = 'spatialcurve.xls';

flux = readtable(data,'PreserveVariableNames',true);

flux = table2array(flux);

But at the last line I get "Error using table2array (line 37)

Unable to concatenate the table variables '2023-08-16 13:49:49.151' and 'Var7', because their types are cell and double."

If I use:

data = importdata('spatialcurve.xls');

flux = struct2cell(data);

flux = cell2mat(data);

Then I get "Error using cell2mat (line 45)

All contents of the input cell array must be of the same data type."

I can slove it by using xlsread but since this is not recommended anymore (for some reasons I don't know), I wonder if it is possible to do it with readtable or importdata?

[Dyuman Joshi]

To create (or convert to) a numeric matrix, all the elements must be numeric scalars (or numerical arrays of compatible dimensions for concatenation), which, by looking at the errors, is not the case with your data.

You can either store hetergeneous data in a cell array or a table (or a struct).

Could you please attach the excel? Use the paperclip button to do so.

[Tomas]

I'm only interested to use the numbers from row 14 to 514.

Answer 1

[dpb]

xlsread has been deprecated because it is more difficult to use with irregular data than the table.

You can't put different data types into a single array other than a cell array, no matter how you read it; it's not reading that's the issue here; it is that you have two (at least) different data types that rightfully should remain as such.

The solution to the problem is to not continue to try to go down an impossible path but use the table you just read and <address the variables from it directly>. Given that one is a datetime, perhaps it would make more sense to use a timetable instead; it has certain additional functionality that can be quite helpful with time-based data.

[Tomas]

Thank you for your answer! I just wanted to find an easy way to be able to use only some of the numbers in spatialcurve.xls (without removing anything from this file) in matlab but since it includes different types of data, table2array won't work then.

[dpb]

"I'm only interested to use the numbers from row 14 to 514..."

Generally, it's abadidea™ to hard code ranges into code; it means if anything changes you have to edit the code; the general solution is to have code logic that selects range of interest.

But, if there is a reason and the rows can be known a priori, then that's easy enough to deal with in a table as well, but at least make them variables to be able to change easily...

Well, let's see what the spreadsheet really contains...

data = 'spatialcurve.xls';
c=readcell(data);
c(1:20,:)

ans = 20×8 cell array
    {[16-Aug-2023 13:49:49.151]}    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {[<missing>               ]}    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {[<missing>               ]}    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {'Scenario: REFERENCEVALU…'}    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {'Formula:'                }    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {'B_OBJECT'                }    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {'Abscissa:'               }    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {'ABS_CURV_COMPOUNDPATH_1' }    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {'SPATIALCURVE_1'          }    {[<missing>        ]}    {[<missing>        ]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}    {[<missing>]}
    {[<missing>               ]}    {'Support'          }    {'Global Coordinat…'}    {[<missing>]}    {[<missing>]}    {'C1'       }    {[<missing>]}    {[<missing>]}
    {'Labels'                  }    {'ABS_CURV_COMPOUN…'}    {'X'                }    {'Y'        }    {'Z'        }    {'C1'       }    {[<missing>]}    {[<missing>]}
    {'Units'                   }    {0×0 char           }    {'m'                }    {'m'        }    {'m'        }    {0×0 char   }    {[<missing>]}    {[<missing>]}
    {'Values'                  }    {[                0]}    {[               -5]}    {[        0]}    {[        3]}    {[-132.3684]}    {[  -0.0463]}    {[ -20.6779]}
    {[<missing>               ]}    {[           0.0200]}    {[          -4.9800]}    {[        0]}    {[        3]}    {[-133.8162]}    {[   0.0142]}    {[ -20.3929]}
    {[<missing>               ]}    {[           0.0400]}    {[          -4.9600]}    {[        0]}    {[        3]}    {[-135.2797]}    {[   0.0227]}    {[ -20.0503]}
    {[<missing>               ]}    {[           0.0600]}    {[          -4.9400]}    {[        0]}    {[        3]}    {[-136.7431]}    {[   0.0298]}    {[ -19.7097]}

Aha! That datetime string apparent came from an aborted attempt that got that initial header line;

One might find the use of detectImportOptions of some benefit here first before reading the table to set the data location...

opt=detectImportOptions(data);
opt.VariableNamingRule='preserve';
opt.VariableNamesRange='A12';
opt.DataRange='A14';                 % the Value row
flux=readtable(data,opt);
whos flux

  Name        Size            Bytes  Class    Attributes

  flux      514x8             84949  table              

[head(flux);tail(flux)]

ans = 16×8 table
           Labels           ABS_CURV_COMPOUNDPATH_1      X       Y      Z       C1         Var7        Var8  
    ____________________    _______________________    _____    ___    ___    _______    _________    _______

    {'Values'          }                0                 -5      0      3    -132.37    -0.046264    -20.678
    {0×0 char          }             0.02              -4.98      0      3    -133.82     0.014182    -20.393
    {0×0 char          }             0.04              -4.96      0      3    -135.28     0.022695     -20.05
    {0×0 char          }             0.06              -4.94      0      3    -136.74     0.029787     -19.71
    {0×0 char          }             0.08              -4.92      0      3    -138.21     0.035458    -19.371
    {0×0 char          }              0.1               -4.9      0      3    -139.67     0.039708    -19.035
    {0×0 char          }             0.12              -4.88      0      3    -141.22      0.04023    -18.717
    {0×0 char          }             0.14              -4.86      0      3    -142.76     0.040461    -18.339
    {0×0 char          }              NaN                NaN    NaN    NaN        NaN          NaN        NaN
    {'Rms values:'     }              NaN                NaN    NaN    NaN      692.8          NaN        NaN
    {0×0 char          }              NaN                NaN    NaN    NaN        NaN          NaN        NaN
    {'Integral values:'}              NaN                NaN    NaN    NaN     4751.6          NaN        NaN
    {0×0 char          }              NaN                NaN    NaN    NaN        NaN          NaN        NaN
    {'Minimal values:' }              NaN                NaN    NaN    NaN    -20.758          NaN        NaN
    {0×0 char          }              NaN                NaN    NaN    NaN        NaN          NaN        NaN
    {'Maximal values:' }              NaN                NaN    NaN    NaN     1432.5          NaN        NaN

Now you've got the beginnings of a useful table; you can get at the X,Y,Z components easily by name or by index, for example.

Now, of course, you could also just import the floating point data as an array, now that we know where it is in the file and the structure of the file, to do that use readmatrix with the desired range instead or from here it would be simply

ix=find(contains(flux.Labels,'Rms values'))-1;    % the blank line befor summary statistics start
flux=flux(1:ix,:);                                % keep those rows
flux=removevars(flux,'Labels');                   % don't need anymore

[Tomas]

The "line opt.VariableNamingRule='preserve';" gives the error "Unrecognized property 'VariableNamingRule' for class 'matlab.io.spreadsheet.SpreadsheetImportOptions'."?

And if I skip the line "opt.VariableNamingRule='preserve';" then I get the error "Unrecognized table variable name 'Labels'." for the line "ix=find(contains(flux.Labels,'Rms values'))-1;", but maybe this is because VariableNamingRule didn't work? I see that my 16x8 table still have x2023_08_1613_49_49_151, Var2, Var3 etc. as headers?

[dpb]

The "line opt.VariableNamingRule='preserve';" gives the error "Unrecognized property 'VariableNamingRule' for class 'matlab.io.spreadsheet.SpreadsheetImportOptions'."

Which release of MATLAB are you using? I don't recall when the facility to use other than valid MATLAB variable names as table variable names was introduced; prior to that there was no need for a naming rule.

If you're using an older release prior to that, then it won't work to try to use those names that aren't valid and if it can't use those names, then "Yes, Virginia, we can't expect a comparison to them to work."

You can rename the variables in the import object as you wish and then use those, but if your release does, at it appears it does, predates using invalid MATLAB names, then those also will have to be valid names; all you can do is get rid of the x prefixes and such.

However, with the string you show as "x2023_08_1613_49_49_151, Var2, Var3", then it's still tried to read that first record in the file as the variable names line which means the whole import object apparently was basically ignored when the error occurred.

[Tomas]

I use matlab21019b. Don´t know if this is a suffuciently new version to be able to do the things you do?

[dpb]

<The R2019b doc> says it exists so something else must be going on...double check spelling including an inadvertent hidden character or somesuch.

Well, the functionality exists, it had a different name/syntax then, it is

'PreserveVariableNames',|true/false| instead; I whiffed on the name change earlier, sorry.

Answer 2

[Dyuman Joshi]

"I'm only interested to use the numbers from row 14 to 514."

You can utilize the range functionality of readmatrix -

%Specific approach
%Directly specify the cells to get data from
mat = readmatrix('spatialcurve.xls','Range','B14:H514')

mat = 501×7

0 -5.0000 0 3.0000 -132.3684 -0.0463 -20.6779 0.0200 -4.9800 0 3.0000 -133.8162 0.0142 -20.3929 0.0400 -4.9600 0 3.0000 -135.2797 0.0227 -20.0503 0.0600 -4.9400 0 3.0000 -136.7431 0.0298 -19.7097 0.0800 -4.9200 0 3.0000 -138.2065 0.0355 -19.3712 0.1000 -4.9000 0 3.0000 -139.6699 0.0397 -19.0346 0.1200 -4.8800 0 3.0000 -141.2159 0.0402 -18.7169 0.1400 -4.8600 0 3.0000 -142.7639 0.0405 -18.3387 0.1600 -4.8400 0 3.0000 -144.3119 0.0404 -17.9608 0.1800 -4.8200 0 3.0000 -145.8599 0.0401 -17.5830

%Generalized answer
%Specify rows to get data from 
mat = readmatrix('spatialcurve.xls','Range','14:514')

mat = 501×8

NaN 0 -5.0000 0 3.0000 -132.3684 -0.0463 -20.6779 NaN 0.0200 -4.9800 0 3.0000 -133.8162 0.0142 -20.3929 NaN 0.0400 -4.9600 0 3.0000 -135.2797 0.0227 -20.0503 NaN 0.0600 -4.9400 0 3.0000 -136.7431 0.0298 -19.7097 NaN 0.0800 -4.9200 0 3.0000 -138.2065 0.0355 -19.3712 NaN 0.1000 -4.9000 0 3.0000 -139.6699 0.0397 -19.0346 NaN 0.1200 -4.8800 0 3.0000 -141.2159 0.0402 -18.7169 NaN 0.1400 -4.8600 0 3.0000 -142.7639 0.0405 -18.3387 NaN 0.1600 -4.8400 0 3.0000 -144.3119 0.0404 -17.9608 NaN 0.1800 -4.8200 0 3.0000 -145.8599 0.0401 -17.5830

%and delete any NaN columns
mat(:,all(isnan(mat),1))=[]

mat = 501×7

0 -5.0000 0 3.0000 -132.3684 -0.0463 -20.6779 0.0200 -4.9800 0 3.0000 -133.8162 0.0142 -20.3929 0.0400 -4.9600 0 3.0000 -135.2797 0.0227 -20.0503 0.0600 -4.9400 0 3.0000 -136.7431 0.0298 -19.7097 0.0800 -4.9200 0 3.0000 -138.2065 0.0355 -19.3712 0.1000 -4.9000 0 3.0000 -139.6699 0.0397 -19.0346 0.1200 -4.8800 0 3.0000 -141.2159 0.0402 -18.7169 0.1400 -4.8600 0 3.0000 -142.7639 0.0405 -18.3387 0.1600 -4.8400 0 3.0000 -144.3119 0.0404 -17.9608 0.1800 -4.8200 0 3.0000 -145.8599 0.0401 -17.5830

Answer 3

[Bruno Luong]

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1471061/spatialcurve.xls')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T = 516×8 table
       Var1       Support    GlobalCoordinates    Var4    Var5      C1         Var7        Var8  
    __________    _______    _________________    ____    ____    _______    _________    _______

    {'Labels'}      NaN              NaN          NaN     NaN         NaN          NaN        NaN
    {'Units' }      NaN              NaN          NaN     NaN         NaN          NaN        NaN
    {'Values'}        0               -5            0       3     -132.37    -0.046264    -20.678
    {0×0 char}     0.02            -4.98            0       3     -133.82     0.014182    -20.393
    {0×0 char}     0.04            -4.96            0       3     -135.28     0.022695     -20.05
    {0×0 char}     0.06            -4.94            0       3     -136.74     0.029787     -19.71
    {0×0 char}     0.08            -4.92            0       3     -138.21     0.035458    -19.371
    {0×0 char}      0.1             -4.9            0       3     -139.67     0.039708    -19.035
    {0×0 char}     0.12            -4.88            0       3     -141.22      0.04023    -18.717
    {0×0 char}     0.14            -4.86            0       3     -142.76     0.040461    -18.339
    {0×0 char}     0.16            -4.84            0       3     -144.31     0.040402    -17.961
    {0×0 char}     0.18            -4.82            0       3     -145.86     0.040053    -17.583
    {0×0 char}      0.2             -4.8            0       3     -147.41     0.039413    -17.205
    {0×0 char}     0.22            -4.78            0       3     -149.05     0.033003    -16.828
    {0×0 char}     0.24            -4.76            0       3     -150.69     0.037098    -16.383
    {0×0 char}     0.26            -4.74            0       3     -152.33     0.041193    -15.938

A=T{1:end,2:end} % remove first column that contains string, adapt range to your need

A = 516×7

NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0 -5.0000 0 3.0000 -132.3684 -0.0463 -20.6779 0.0200 -4.9800 0 3.0000 -133.8162 0.0142 -20.3929 0.0400 -4.9600 0 3.0000 -135.2797 0.0227 -20.0503 0.0600 -4.9400 0 3.0000 -136.7431 0.0298 -19.7097 0.0800 -4.9200 0 3.0000 -138.2065 0.0355 -19.3712 0.1000 -4.9000 0 3.0000 -139.6699 0.0397 -19.0346 0.1200 -4.8800 0 3.0000 -141.2159 0.0402 -18.7169 0.1400 -4.8600 0 3.0000 -142.7639 0.0405 -18.3387

class(A)

ans = 'double'

size(A)

ans = 1×2

516 7

[Tomas]

When I tried your solution the line "A=T{1:end,2:end}" gives the error "Unable to concatenate the table variables 'Var2' and 'Var7', because their types are cell and double." I can see that my table is still a 525x8 with the headers x2023_08_1613_49_49_151, Var2, Var3 etc?

[Bruno Luong]

My code run with your data you have provided.

If you have different data then you should check if the data are similar, or restricted to the rectangular array where only double class is presented, or you have to provide the data if you want to us to take a look.

[Tomas]

It's still the same data that I uploaded so I can't understand why it's not working? Could it be a problem that I am using matlab2019b?

[Bruno Luong]

Could be I don't have R2019b to test. In the code

I wrote the comment "adapt range to your need". Did you attemp to do that?

[Tomas]

Unfortunately, no. After I do

T = readtable('spatialcurve.xls');

I get a table that looks different to yours

x2023_08_1613_49_49_151 Var2 Var3 Var4 Var5 Var6 Var7 Var8

_____________________________ ___________________________ ______________________ __________ __________ _____________ __________ ________

{0×0 char } {0×0 char } {0×0 char } {0×0 char} {0×0 char} {0×0 char } NaN NaN

{0×0 char } {0×0 char } {0×0 char } {0×0 char} {0×0 char} {0×0 char } NaN NaN

{'Scenario: REFERENCEVALUES'} {0×0 char } {0×0 char } {0×0 char} {0×0 char} {0×0 char } NaN NaN

{'Formula:' } {0×0 char } {0×0 char } {0×0 char} {0×0 char} {0×0 char } NaN NaN

{'B_OBJECT' } {0×0 char } {0×0 char } {0×0 char} {0×0 char} {0×0 char } NaN NaN

{'Abscissa:' } {0×0 char } {0×0 char } {0×0 char} {0×0 char} {0×0 char } NaN NaN

{'ABS_CURV_COMPOUNDPATH_1' } {0×0 char } {0×0 char } {0×0 char} {0×0 char} {0×0 char } NaN NaN

{'SPATIALCURVE_1' } {0×0 char } {0×0 char } {0×0 char} {0×0 char} {0×0 char } NaN NaN

{0×0 char } {'Support' } {'Global Coordinates'} {0×0 char} {0×0 char} {'C1' } NaN NaN

{'Labels' } {'ABS_CURV_COMPOUNDPATH_1'} {'X' } {'Y' } {'Z' } {'C1' } NaN NaN

{'Units' } {0×0 char } {'m' } {'m' } {'m' } {0×0 char } NaN NaN

{'Values' } {'0' } {'-5' } {'0' } {'3' } {'-132.3684'} -0.046264 -20.678

etc

and then I can't do

A=T{1:end,2:end}

due to "Unable to concatenate the table variables 'Var2' and 'Var7', because their types are cell and double."

[Tomas]

I thought I could get a nice table (like you have above) by copy and paste from matlab command window but this was just messy, sorry.

[Bruno Luong]

Can you save T returned by readtable in mat file and attach here?

But with visual inspection it looks like the reatable in your version is not able to read data from this xls file. Many field contain just 0x0 char.

[Tomas]

OK, so it seems that it's something that is not working as supposed to with my readtable, hmm.. But I have attched the table as .mat.

[Bruno Luong]

load T.mat % R2019b : T = readtable('spatialcurve.xls')

c26=T{13:512,2:6};
c78=T{13:512,7:8};
A=[str2double(c26) c78]

A = 500×7

0.0200 -4.9800 0 3.0000 -133.8162 0.0142 -20.3929 0.0400 -4.9600 0 3.0000 -135.2797 0.0227 -20.0503 0.0600 -4.9400 0 3.0000 -136.7431 0.0298 -19.7097 0.0800 -4.9200 0 3.0000 -138.2065 0.0355 -19.3712 0.1000 -4.9000 0 3.0000 -139.6699 0.0397 -19.0346 0.1200 -4.8800 0 3.0000 -141.2159 0.0402 -18.7169 0.1400 -4.8600 0 3.0000 -142.7639 0.0405 -18.3387 0.1600 -4.8400 0 3.0000 -144.3119 0.0404 -17.9608 0.1800 -4.8200 0 3.0000 -145.8599 0.0401 -17.5830 0.2000 -4.8000 0 3.0000 -147.4079 0.0394 -17.2054