How can I read specific data from excel with multiple sheets?

Hello, I am trying to read an excel file with 50 sheets whereas each sheet has 4 column and about 7000 rows data.one of the columns is related to the year of data and anothers, month and day. I want all the sheets to be in a mat file format with cells categorized according to the years. I am so confused about it and need good idea. So appreciate all the good thoughts and help.

Answer 1

[the cyclist]

My first choice for reading data from Excel is usually the readtable function. It has many options for pulling data from individual sheets, data ranges, etc.

If you know all of the sheet names ahead of time (e.g. if they obey a particular naming pattern), then you should be able to create a for loop to read from every sheet. I would suggest reading each individual sheet into an element of a cell array.

It's difficult to be more specific without see the input file. Can you upload perhaps a subset of the file, with just a few sheets, and maybe not all the data from each sheet?

[Honey]

Thank you the cyclist for your answer. My example file is ulpoaded here. The main file is included 50 sheets.

I used this code to read from a specific sheets and column but the problem is that I want to read year, month, day and the data. then sorting them in a cell file whcih is arranged by the year.

for ii=1:nSheets

Name=SheetNames{ii};

Observation=[Data, xlsread('Final_Data.xls',Name, 'D:D')];

end

[the cyclist]

From your comment, it is not clear to me how you want the data arranged. But this code will read in all the data from the file you uploaded, and make one "long" array from the sheets.

nSheets = 5;
SheetNames = {'16-157','16-089','16-085','16-054','12-040'};
Data = [];
for ii=1:nSheets
    Name = SheetNames{ii};
    Data = [Data; xlsread('Final_Data.xls',Name)];
end
size(Data)

ans = 1×2

30685 5

Does this do what you need? If not, how does it need to be different?

[Honey]

Thank you dear the cyclist. It worked.

Answer 2

[Image Analyst]

Use readmatrix(). There is an option to specify the sheet name, for examples:

data1 = readmatrix('text.xlsx', 'Sheet', 'Sheet1');
data2 = readmatrix('text.xlsx', 'Sheet', 'Parameters');
data3 = readmatrix('text.xlsx', 'Sheet', 'Results');
% etc.

[the cyclist]

I'm sure you meant data{1}, data{2}, data{3}, ...

:-)

[Honey]

Thank you Image Analyst for your answer but I think it can't helpful for me. I am looking for a way to read them with a specific order.

[Image Analyst]

@the cyclist, I would only use data{1} if I wanted the data to go into cells. I used readmatrix() assuming the data would be numerical.

And I think you thought the code was supposed to be inside of a loop or something. I was not thinking that. I was just simply showing how you could read three different sheets with different names from the same workbook, if you had three sheets. Obviously you could have 1 sheet or 2 sheets or more, or have different names than I used.

@Honey, to do 50 files, you need to put the readmatrix() or readcell() code inside a loop as shown in the FAQ:

https://matlab.fandom.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F

Now you can put each sheet's contents either into separate cells like @the cyclist showed, or you could append the data onto a single, growing array (either double array or cell array).

However you said "it can't helpful for me. I am looking for a way to read them with a specific order." Even the FAQ gives you the workbook filenames in a sorted order. If you really want them in some specific order, then you'll have to do what I said (list names explicitly) but just list the order of the workbooks you want in advance of the loop:

fileNames = {'1.xlsx', 'next one.xlsx', 'the third one.xlsx', abc.xlsx', '983.xlsx', 'last.xlsx');
% Now read the workbook files in the specific order.
allData = [];
for k = 1 : length(fileNames)
    thisData = readmatrix(fileNames{k});
    % If you want them in the same array
    allData = [allData; thisData];
    % If you want them in separate cells:
    caData{k} = readmatrix(fileNames{k}); % or readcell()
end

I show both ways - putting data into a cell array, and vertically concatenating all data into a single array. Of course if there are multiple sheets, you'd want to list the sheet name in readmatrix() like I already showed you.