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

260 次查看（过去 30 天）
Honey
Honey 2021-9-6
评论： Image Analyst 2021-9-10
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.

请先登录，再回答此问题。

采纳的回答

the cyclist
the cyclist 2021-9-6
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 个评论
显示 1更早的评论
the cyclist
the cyclist 2021-9-6
Ran in:
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?

请先登录，再进行评论。

更多回答（1 个）

Image Analyst
Image Analyst 2021-9-6
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 个评论
显示 1更早的评论
Honey
Honey 2021-9-6
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
Image Analyst 2021-9-10
@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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by