Read from Excel file as a plain table

9 次查看（过去 30 天）
Egor Gurov
Egor Gurov 2020-11-3
评论： Peter Perkins 2020-11-18
Hi everyone!
I have an excel file with the data like this:
The size (columns and rows quantity) is not defined and can be changed. Some data may be missing.
I want to get this table in Matlab as a simple table where every cell in the file is in a cell in the table. I do not need to use variable name read, to read in separate structures text and numerical data, or anything else. It will be more convenient to have only one variable as a table and then to get the required information and store it in a suitable variable format. For example, A4, A5, ... convert to datetime, to merge B1 and B2 into one string, numeric values as a matrix.
However, readtable() function always tries to do something with head, and I didn't find a way to skip it. The way like 'ReadVariableNames',false only do not read head, but I want to see it in the variable.
Could you please tell me what to do to import data from an excel file without such smart reading?
  2 个评论
dpb
dpb 2020-11-3
The irregular nature of the arrangement in the spreadsheet is the primary culprit.
Why are there four (4) rows of data but only three (3) corresponding dates?
Creating the original file in a more consistent manner would simplify things a whole lot.
It will be simple enough to create the compound name from rows 1:2; what's not so clear is the remainder.

请先登录，再进行评论。

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

采纳的回答

dpb
dpb 2020-11-4
编辑：dpb 2020-11-4
[~,~,r]=xlsread('yourfile.xls');
will return a cell array r that is a duplicate of the spreadsheet content with no interpretation of what is what if that is what is wanted.
Or, as Walter points out, the modern replacement would be readcell
  2 个评论
Egor Gurov
Egor Gurov 2020-11-4
dpb, many thanks! This is exactly what I need.
Peter Perkins
Peter Perkins 2020-11-18
readcell would work, but then where are you? A big cell array that is probably not useful.
As other have said, this file is ... peculiar. If the first three lines are the same in all instances of these files, it would be easy to use readtable/readtimetable and tell it the number of header lines to skip, and to not read var names, and then add the desired var names by hand to the timetable you've read in. Or maybe read the first two lines of the file separately and build up the var names programmatically from that.
But there's the matter of four rows of measurements, and only three timestamps. That can't be right.

请先登录，再进行评论。

更多回答（1 个）

drummer
drummer 2020-11-3
Well, if you want to have this specific small table, then you could insert them in MATLAB manually.
Would it work?
If so, then you could use cell arrays and tell MATLAB the way you want the table, without the 'fancy' readtable features.
Again, bigger tables would not be ideal. But if you want this small one. I wonder that's ok.
This way, it will be ok to ignore the (relevant) observation from dpb.
Let us know if it worked for you.
Cheers
  2 个评论
Egor Gurov
Egor Gurov 2020-11-3
drummer, thanks for your answer.
Perhaps I have described the problem incorrectly. This table is just an example, real tables may have hundreds of columns and rows, and, of course, they are large tables. Moreover, the numbers of columns and rows aren't known in advance.
dpb suggested modifying the file before processing (thank you for your answer). Unfortunately, this is not possible. Files are filled in manually. There is no way to build a 3D table or more in Excel. It is good that, for example, B1 and C1 were not merged into one cell in the Excel table.
So, you mean that it isn't possible to directly import an Excel file in Matlab without distinguishing between variable names and values?
drummer
drummer 2020-11-4
编辑：drummer 2020-11-4
I have never dealt with such table formation.
I'm not familiar with your dataset but if you use Name and parameter as categorical variables and each date as a single variable (like hot-encoding your date variables) would not work?
So you would have
Name (categorial) | Parameter (categorical) | ParameterOutput (numerical) | Date 1 | Date 2 | Date 3
Unless you're not looking to fix your table layout. rs
I wonder dpb suggestion would work if that's the case.

请先登录，再进行评论。

类别

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

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by