MATLAB Answers

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

27 views (last 30 days)
Honey
Honey on 6 Sep 2021
Commented: Image Analyst on 10 Sep 2021
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.

Accepted Answer

the cyclist
the cyclist on 6 Sep 2021
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?
  3 Comments

Sign in to comment.

More Answers (1)

Image Analyst
Image Analyst on 6 Sep 2021
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.
  3 Comments
Image Analyst
Image Analyst on 10 Sep 2021
@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:
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.

Sign in to comment.

Products


Release

R2020b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!