Import table from row oriented excel sheet

I have Excel worksheets oriented such that variable names are in the first column and variables in rows. e.g.:

Var1 1 2 3 ...

Var2 4 5 6 ...

Var3 7 8 9 ...

etc.

This would be "row oriented" data to use the Matlab description of the readtable command which is expecting "column oriented" data. There does not seem to be a way to use readtable to transpose the data so it is read correctly. I can read the array and then transpose the data but there does not seem to be a way to read in variable names from a column with this command/approach. Seems like this should just be a simple orientation option that you can set in the function call or table options. Am I missing something? I looked at a bunch of similar themed questions but did not find a satisfactory answer. My guess is that I have two choices if I wnat to end up with table data:

1) Transpose the data in Excel into a new worksheet and use readtable()

2) Use xlsread() or some other variant to read data and varialbe names into separate arrays, do the transpose on the data and cobble together the table using opts.VariableNames

[Bob Thompson]

Does the option to 'ReadRowNames' not work for your situation? This is for the readtable command.

Answer 1

[Bruce MacWilliams]

Thanks. That got me going in the right direction. It was at least a way to read the variable names. Here's the complete solution I came up with ...

% Get row-oriented table A options, columns of interest
Aopts = detectImportOptions(FileName,'Sheet','Worksheet Name','Range','J:CI');
% Set the variable range for the column containing variable names
Aopts.RowNamesRange = 'B3:B291';
% Now read the table with these options and ReadRowNames set to true
A = readtable(FileName,Aopts,'ReadRowNames',true);
% Now parse out numerical array that will be transposed
AData = table2array(A(:,10:end));
% and store the row names in a separate vector
VarNames = matlab.lang.makeValidName(A.Properties.RowNames);
% Now build the new transposed table
A2 = array2table(AData','VariableNames',VarNames);

Still more kludge than it needs to be but it works.

[Remi Ehounou]

Thanks for posting your Solution. It helped me

[Jerome Baranger]

Thanks for sharing, it helped me as well