MATLAB Answers

Read from Excel file as a plain table

3 views (last 30 days)
Egor Gurov
Egor Gurov on 3 Nov 2020
Commented: Peter Perkins on 18 Nov 2020
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 Comments

Sign in to comment.

Accepted Answer

dpb
dpb on 4 Nov 2020
Edited: dpb on 4 Nov 2020
[~,~,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 Comments
Peter Perkins
Peter Perkins on 18 Nov 2020
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.

Sign in to comment.

More Answers (1)

drummer
drummer on 3 Nov 2020
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 Comments
drummer
drummer on 4 Nov 2020
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.

Sign in to comment.

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!