How to load xlsx data file with readtable?
42 views (last 30 days)
Show older comments
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
5 Comments
Cris LaPierre
on 28 Jan 2021
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.
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.
Answers (1)
Oren Lee
on 28 Jan 2021
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);
2 Comments
Oren Lee
on 28 Jan 2021
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'});
See Also
Categories
Find more on Structures in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!