How to load xlsx data file with readtable?

Hi all,

I try to write a matlab-script for loading an .xlsx file. In column A, D and G are variable names like: age, sex, last name, etc. and in column B, E and H are the corresponding values or text. Columns C and F are empty. in the end i would like to make variables age, sex, etc. with the data in the corresponding matlab table.

In the columns J until CG the variable name is in the first row and the other rows contain the data per time point.

First I tried:

bestand=[pathname filename{file}];
DataK5 = readtable(bestand,'Sheet', 'Data','Range','A:CG'); 

This works well for the J-CG part of the data but not for the A-H part.

Then I tried:

bestand=[pathname filename{file}];
DataK5 = readtable(bestand,'Sheet', 'Data','Range','J:CG','ReadVariableNames', true);
DataPt = readtable(bestand,'Sheet', 'Data','Range','A1:B8','ReadRowNames',true,'ReadVariableNames',false);
DataMeas = readtable(bestand,'Sheet', 'Data','Range','D1:E14', 'ReadRowNames',true,'ReadVariableNames',false);
DataCond = readtable(bestand,'Sheet', 'Data','Range','G1:H15', 'ReadRowNames',true,'ReadVariableNames',false);

In the beginning this worked well for DataK5, DataMeas and DataCond, but not for DataPt while readtable skipt the first 4 lines. I thought because of the B1 being empthy and B2-B4 being strings. Then I tried for DataPt

DataPt (1:4,:) = readtable(bestand,'Sheet', 'Data','Range','A1:B4','TextType','string','ReadRowNames',true,'ReadVariableNames',false);
DataPt(5:8,:) = readtable(bestand,'Sheet', 'Data','Range','A5:B8','ReadRowNames',true,'ReadVariableNames',false);

This worked for rows 1:4 but not for the second part, for the second part the variable names from column A disappeared and column B was a string.

The data file for the columns A:H looks like this:

Does anyone of you know how to properly load this excel-file in one (or if necessary more) matlab tables? With the the variable names either in the row or column header.

Kind regards,

Jaimy

[Mathieu NOE]

hello

maybe you should share your xlsx file to let us help you !

tx

[Jaimy van den Hout]

@Mathieu NOE Thanks, I added my xlsx file to the original post!

[Cris LaPierre]

All data in a column must have the same data type. If you take the 'RowNames' approach, that means all your values will have to be strings or categoricals. I guess you could use cells, but that kind of defeats the point of using a table in my mind.

[Jaimy van den Hout]

@Cris LaPierre hmm.. OK thanks. At the moment, I load the first columns A:H and columns J:CG separately. For the second part I use the ReadVariableNames and for the A:H part I use cells. This works (except that the time variables are not correct), but I would like to load the data based on the variable name, so I don't have to change the row/column numbers in case the output format of my measurement device changes. Do you perhaps know if there are other options to easily load an excel-file and the variables based on the variable name?

[Cris LaPierre]

The easy way is to have variable name in the first row, and value in the 2nd row. Then, readtable will automatically use the header name as the variable name, and generally does a pretty good job autodetecting the datatype.

If you haven't see it already, I suggest looking at the Import Spreadsheets documentation page.

The challenge here is that the spreadsheet has not been formatted is a way the import functions expect. You can probably still accomplish what you want, but there is not going to be an easy way to do so.

Answer 1

[Oren Lee]

I have done something recently which is parallel to this. I recommend using readcell() over your whole excel sheet instead of readTable over individual columns. In this way you can do all the data handling in MATLAB seperate and more efficiently, and you are only calling the excel opener once (vs 1/column needed). This should also help improve speed of your code.

allData = readcell(bestand);

[Jaimy van den Hout]

@Oren Lee Thanks for your answer! I expect that with readcell you cannot select the column you need by means of the variable name? Instead you have to give the rows and columns in which the variable is? Like for example: time = allData(:,10)?

[Oren Lee]

Hi Jaimy,

That would be correct, but then you can always pass the output of the cellread into cell2table(). If you have static column names you can do something like the following.

allData = cell2table(readcell(bestand),'VariableNames',{'etc','etc2'});