Effacer les filtres
Effacer les filtres

Read from Excel file as a plain table

11 vues (au cours des 30 derniers jours)
Egor Gurov
Egor Gurov le 3 Nov 2020
Commenté : Peter Perkins le 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 commentaires
dpb
dpb le 3 Nov 2020
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.
Walter Roberson
Walter Roberson le 4 Nov 2020
readcell

Connectez-vous pour commenter.

Réponse acceptée

dpb
dpb le 4 Nov 2020
Modifié(e) : dpb le 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 commentaires
Egor Gurov
Egor Gurov le 4 Nov 2020
dpb, many thanks! This is exactly what I need.
Peter Perkins
Peter Perkins le 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.

Connectez-vous pour commenter.

Plus de réponses (1)

drummer
drummer le 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 commentaires
Egor Gurov
Egor Gurov le 3 Nov 2020
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 le 4 Nov 2020
Modifié(e) : drummer le 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.

Connectez-vous pour commenter.

Produits


Version

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by