Read from Excel file as a plain table

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?

[dpb]

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]

readcell

Answer 1

[dpb]

[~,~,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

[Egor Gurov]

dpb, many thanks! This is exactly what I need.

[Peter Perkins]

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.

Answer 2

[drummer]

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

[Egor Gurov]

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]

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.