How do I get num, txt and raw as a cell array or matrix or table from the “readtable(file_name)” command, without using “[num,txt,​raw]=xlsre​ad(file_na​me)”

How do I get num, txt and raw as a cell array/matrix/table from the “readtable(file_name)” command. The alternative command “[num,txt,raw]=xlsread(file_name)” couldn’t be used as it is not recommended by Matlab and requires basic excel (which is decommissioned in our company). So please guide me in this case, Thankyou.

[num,txt,raw] = xlsread(___): num returns the numeric data in a matrix. additionally returns the text fields in cell array txt, and both numeric and text data in cell array raw.

[Stephen23]

"How do I get num, txt and raw as a cell array/matrix/table from the “readtable(file_name)”"

That is not really what READTABLE does. It might be possible, with a large effort.

A much better approach would be to use READCELL, which returns something a bit like the RAW array. From that cell array you could derive the numeric and text parts with a few lines of code.

The best approach would be to rethink your code: do you really have mixed data in every column?

[Arepalli]

Thankyou Stephen23, readcell with a few lines of code worked perfeclty for extracting data similar to xlsread().

Answer 1

[Gayathri]

Hello @Arepalli,

I understand that you want to avoid using "xlsread" function. "readtable" along with other matlab functions, can be used to fulfill the functions of "xlsread". The usage is as shown below.

tbl = readtable('filename.xlsx')
raw = table2cell(tbl)
num = table2array(tbl)
txt = tbl.Properties.VariableNames

If you want "raw" variable to contain headers of the excel file then the following code can be used.

data = table2cell(tbl);
raw = [txt; data]

For more information about "readtable" please refer below link.

https://www.mathworks.com/help/matlab/ref/readtable.html

I hope the above information is helpful to you.

[Stephen23]

This forum is just filling up with AI-generated nonsense. Sigh.

Lets try the suggested code. I created a simple XLSX file (attached) with some mixed data.

For comparison, lets first try with XLSREAD:

[num,txt,raw] = xlsread('test.xlsx')

num = 3x2

1.0000 2.3000 2.0000 NaN 3.0000 999.0000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

txt = 2x2 cell array
    {0x0 char}    {'hello'}
    {'cat'   }    {'world'}

raw = 3x3 cell array
    {[1]}    {[2.3000]}    {'hello'}
    {[2]}    {'cat'   }    {'world'}
    {[3]}    {[   999]}    {[  NaN]}

Now lets try the code given in this answer:

tbl = readtable('test.xlsx');
raw = table2cell(tbl) % note that "cat" turned into NaN

raw = 3x3 cell array
    {[1]}    {[2.3000]}    {'hello' }
    {[2]}    {[   NaN]}    {'world' }
    {[3]}    {[   999]}    {0x0 char}

try
    num = table2array(tbl) % this fails with an error
catch ME
    ME.message
end

ans = 'Unable to concatenate the table variables 'Var1' and 'Var3', because their types are double and cell.'

txt = tbl.Properties.VariableNames % nothing like the 2nd output from XLSREAD

txt = 1x3 cell array
    {'Var1'}    {'Var2'}    {'Var3'}

No, this code is flawed, written by AI that does not know and posted by people that do not test their code.

@Gayathri: do you work for TMW ?

[Gayathri]

Hi @Stephen23,

I had considered an excel file with numeric values with text headers. I had not considered any strings in excel file. Hence, the confusion happened.

[num,txt,raw] = xlsread('myExample.xlsx')

num = 3x3

1 2 3 4 5 6 7 8 9

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

txt = 1x3 cell array
    {'First'}    {'Second'}    {'Third'}

raw = 4x3 cell array
    {'First'}    {'Second'}    {'Third'}
    {[    1]}    {[     2]}    {[    3]}
    {[    4]}    {[     5]}    {[    6]}
    {[    7]}    {[     8]}    {[    9]}

tbl = readtable('myExample.xlsx')

tbl = 3x3 table
    First    Second    Third
    _____    ______    _____

      1        2         3  
      4        5         6  
      7        8         9  

raw = table2cell(tbl)

raw = 3x3 cell array
    {[1]}    {[2]}    {[3]}
    {[4]}    {[5]}    {[6]}
    {[7]}    {[8]}    {[9]}

num = table2array(tbl)

num = 3x3

1 2 3 4 5 6 7 8 9

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

txt = tbl.Properties.VariableNames

txt = 1x3 cell array
    {'First'}    {'Second'}    {'Third'}

[Gayathri]

@Arepalli, To make it work on excel files with mixed data I recommend using "readcell" and "readmatrix" function. Please find the below codes. "xlsread" function has been shown for comparison.

[num,txt,raw] = xlsread('myExample.xlsx')

num = 4x3

1.0000 2.3000 NaN 2.0000 NaN NaN 3.0000 999.0000 NaN 7.0000 8.0000 9.0000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

txt = 2x2 cell array
    {0x0 char}    {'hello'}
    {'cat'   }    {'world'}

raw = 4x3 cell array
    {[1]}    {[2.3000]}    {'hello'}
    {[2]}    {'cat'   }    {'world'}
    {[3]}    {[   999]}    {[  NaN]}
    {[7]}    {[     8]}    {[    9]}

num=readmatrix('myExample.xlsx')

num = 4x3

1.0000 2.3000 NaN 2.0000 NaN NaN 3.0000 999.0000 NaN 7.0000 8.0000 9.0000

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

raw=readcell('myExample.xlsx')

raw = 4x3 cell array
    {[1]}    {[2.3000]}    {'hello'    }
    {[2]}    {'cat'   }    {'world'    }
    {[3]}    {[   999]}    {[<missing>]}
    {[7]}    {[     8]}    {[        9]}

txt = raw(cellfun(@ischar, raw) | cellfun(@isstring, raw))

txt = 3x1 cell array
    {'cat'  }
    {'hello'}
    {'world'}

Here "txt" gives all the text values as a nx1 cell array.

For more information about "readmatrix" and "readcell" refer to the below links.

https://www.mathworks.com/help/matlab/ref/readmatrix.html

https://www.mathworks.com/help/matlab/ref/readcell.html

[Stephen23]

"I had considered an excel file with numeric values with text headers."

The OP stated "num returns the numeric data in a matrix. additionally returns the text fields in cell array txt, and both numeric and text data in cell array raw", making it clear that their data includes text. Perhaps they meant something like headers, who knows.

They way that people abused XLSREAD for such data... ugh. Which is why I would strongly advise using more suitable and capable features of tables etc rather than trying to force their code into ugly patterns just to suit a deprecated function that is more than twenty years old.