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

366 visualizzazioni (ultimi 30 giorni)
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.

Risposta accettata

the cyclist
the cyclist il 6 Set 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 Commenti
the cyclist
the cyclist il 6 Set 2021
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?

Accedi per commentare.

Più risposte (1)

Image Analyst
Image Analyst il 6 Set 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 Commenti
Honey
Honey il 6 Set 2021
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 il 10 Set 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.

Accedi per commentare.

Prodotti


Release

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by