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]=xlsread(file_name)”

Question

Arepalli 2024-9-5

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2150344-how-do-i-get-num-txt-and-raw-as-a-cell-array-or-matrix-or-table-from-the-readtable-file_name-com

评论： Arepalli 2024-9-11

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.

2 个评论
显示无隐藏无

Stephen23 2024-9-5

编辑：Stephen23 2024-9-5

"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 2024-9-11

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

请先登录，再进行评论。

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

Answer 1

Gayathri 2024-9-5

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2150344-how-do-i-get-num-txt-and-raw-as-a-cell-array-or-matrix-or-table-from-the-readtable-file_name-com#answer_1511394

在 MATLAB Online 中打开

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.

4 个评论
显示 2更早的评论隐藏 2更早的评论

Stephen23 2024-9-5

编辑：Stephen23 2024-9-5

在 MATLAB Online 中打开

test.xlsx

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 2024-9-5

编辑：Gayathri 2024-9-5

在 MATLAB Online 中打开

myExample.xlsx

@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 2024-9-5

编辑：Stephen23 2024-9-5

"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.

请先登录，再进行评论。

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]=xlsread(file_name)”

2 个评论
显示无隐藏无

回答（1 个）

4 个评论
显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

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)”

2 个评论 显示 无隐藏 无

回答（1 个）

4 个评论 显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

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]=xlsread(file_name)”

2 个评论
显示无隐藏无

4 个评论
显示 2更早的评论隐藏 2更早的评论