how to read multiple sheets (Tab) data and save it in a single matrix?

Hello, I am trying to read an excel file with 19 sheets (tabs) whereas each tab has 365(or 366)*1 data. I want all the tabs to be on one sheet (all columns side by side) in a matrix form. I tried with code below but no success. Appreciate all the good thoughts and help.

[~,SheetNames]  = xlsfinfo('Yr00.xlsx');
nSheets = length(SheetNames);

for ii=1:nSheets
    Name=SheetNames{ii};
    Data=xlsread('Yr00.xlsx',Name);
    S(ii).Data=Data;
 end

Answer 1

[Majid Farzaneh]

Hi, I hope this can help you:

[~,SheetNames]  = xlsfinfo('Yr00.xlsx');
nSheets = length(SheetNames);
Data=[];
for ii=1:nSheets
    Name=SheetNames{ii};
    Data=[Data, xlsread('Yr00.xlsx',Name)];
end
S.Data=Data

[Majid Farzaneh]

If number of rows are not equal in all sheets, it's impossible to merge all data in a matrix. If so, you should make number of rows equal.

[Hydro]

Hello Majid, yes, your code is good, the leap year is creating the issue now. Any thoughts on that. Thanks

[Majid Farzaneh]

[~,SheetNames]  = xlsfinfo('Yr00.xlsx');
nSheets = length(SheetNames);
Data=zeros(366,nSheets);
for ii=1:nSheets
    Name=SheetNames{ii};
    sheetData=xlsread('Yr00.xlsx',Name);
    Data(1:numel(sheetData),ii)= sheetData;
end
S.Data=Data

[Majid Farzaneh]

By recent code you have a zero in last row for column that have 365 values.

[Hydro]

Many thanks, this will definitely help in future. I did via the macro in excel though.

[Majid Farzaneh]

You're welcome

[A V A Bharat Kumar]

however this code is not working if the number of rows in different sheets are not same

[Walter Roberson]

The code Majid posted in https://www.mathworks.com/matlabcentral/answers/401573-how-to-read-multiple-sheets-tab-data-and-save-it-in-a-single-matrix#comment_569736 should work when the number of rows is not the same; shorter rows will be padded with zeros.

The code I posted at https://www.mathworks.com/matlabcentral/answers/401573-how-to-read-multiple-sheets-tab-data-and-save-it-in-a-single-matrix#comment_569711 is specific to leap years or not and inserts nan for the years that do not have February 29.

[Eman Hassan]

Dear All,

The code is not working for different row sizes. how to padded the shorter rows with zeros?

I have got the following error when run the code for my data,

"Dimensions of arrays being concatenated are not consistent."

[Walter Roberson]

As a minor change to what Majid posted earlier in this thread:

[~,SheetNames]  = xlsfinfo('Yr00.xlsx');
nSheets = length(SheetNames);
Data = zeros(0,nSheets);
for ii=1:nSheets
    Name = SheetNames{ii};
    sheetData = xlsread('Yr00.xlsx',Name);
    Data(1:numel(sheetData),ii) = sheetData(:);
end
S.Data=Data

This will take all of the data in the sheet and arrange it as a side-by-side column, with the shorter columns been padded with zeros.

[Philippe Corner]

Hi all, how would it be if we want that to create different matrixes with the name of the sheets? and no to merge all data in one matrix?

[Walter Roberson]

Philippe Corner: see

http://www.mathworks.com/matlabcentral/answers/304528-tutorial-why-variables-should-not-be-named-dynamically-eval

[Johann Martinez]

Hi all! It doesn't work thw accepted answer since Matlab 2019a presents lot of issues with xlsinfo(), and xlsread().

Does anyone could state another proposal with current well worked Matlab's functions ?

Thanks in advance.

Answer 2

[Walter Roberson]

That looks okay as far as it goes. What is left after that is putting the data all together in one matrix. For that purpose, how do you want to handle the leap year? Do you want the rows matched by calendar date, in which case 3/4 of the rows would have missing data for February 29? Or do you want the rows matched by day number of the year, in which case 1/4 of the rows would have one extra column? What "fill" value do you want to use for the "holes" ?

[Hydro]

Hello Walter, I won't care about the leap day as it's not that important as I will be looking at data from April to July. But for now, I want the rows matched by calendar date. for the holes, use NaN (i will remove it anyway).

Many thanks for looking into it.

[Walter Roberson]

allvalues = [];
for ii = 1 : length(SS)
  thisdata = SS(ii).Data;
  if length(thisdata) == 366
    allvalues = [allvalues, thisdata];
  else
    allvalues = [allvalues, [thisdata(1:59); nan; thisdata(60:end)]];
  end
end

Sometimes it just isn't worth using fancy vectorized algorithms.

[Hydro]

Hello Walter,

Gave me the matrix dimension error. Tried to attach my excel sheet, but is not supported? I have many excel files like this- that's why looking for a code as doing it one by one would be very laborious.

[Walter Roberson]

You can zip up .xls and .xlsx and attach those.