Get non numerical parts from different sheets in excel file.
Mostra commenti meno recenti
Dear all,
In an excel file I have 20 sheets with study results of students (for each student one sheet). I want to make one database with the study results of these students using parts of these sheets. From each sheet I need 3 parts: The header with information of the student, and two blocks of grades. Uptill now I can do this for one sheet at a time. But I want to do it in a for loop for all the 20 sheets.
Using the function xlsread ("NameFile.xlsx",r) goes into every sheet, within the loop, but reads only the numbers in the cells, no names, and it omit parts of the excel file. The function readcell() reads every letter. But I can not find a way to add the sheet number to it, something like readcell("NameFile.xlsx",r). Is there another way to reach this goal? Taking the percentage away represents the goal I want.
This is the code I am using now:
%r=30 %number of sheets
%for k=1:r
B = readcell("TBCN202425.xlsx");
B1=P(1:6,3); %student information from the header out of sheet r
B1=(B1)'; %transpose, into a rowvector
P2=P(10:18,2:7);% block 1 with grades
j=size(P2,1); %number of rows needed
P11=repmat(P1,j,1); %create rows for student info for database
P2=[P11 P2]; %add student information to block 1 of grades
P3=P(48:50, 9:14); %block 2 with grades
i=size(P3,1); %number of rows needed
P31=repmat(P1,i,1); %create rows for student info for database
P3=[P31 P3]; %add student information to block 1 of grades
P4=[P2; P3]; % stacking block of grades
%end
writetable(P4, "Studentdatabase.xls");
1 Commento
Stephen23
il 5 Apr 2024
"But I can not find a way to add the sheet number to it, something like readcell("NameFile.xlsx",r)"
You could read the READCELL documentation and use the SHEET option:
B = readcell("TBCN202425.xlsx", "Sheet",k);
Or perhaps READTABLE would be more suitable for your file format. It would be much better if you uploaded a sample data file.
Avoid deprecated XLSREAD.
Risposta accettata
Cris LaPierre
il 5 Apr 2024
Modificato: Cris LaPierre
il 5 Apr 2024
0 voti
By default, that is all that xlsread returns. You probably want to use this syntax instead:
Note that xlsread is not recommended anymore, but an approach using the recommended functions will add more steps to extract non-numeric values.
Depending how your data is organzied, I would look into readtable. Student name can be your variable, while grades can be the variable values.
For a personalized answer, please attach a sample data set to your post using the paperclip icon.
2 Commenti
Claartje Nijman
il 14 Apr 2024
Modificato: Cris LaPierre
il 14 Apr 2024
Cris LaPierre
il 14 Apr 2024
Modificato: Cris LaPierre
il 14 Apr 2024
Be warned, the solution is not the prettiest code. I have placed the code for a single sheet in a function. You can then call that function in a for loop for each sheet. The result is concatenated to the bottom of sDB.
fname = "Book1.xlsx";
sDB = table;
for snum = 1:3
sDB = [sDB; createDatabase(fname,snum)];
end
% View the result
sDB
%% Function for loading a single sheet
function studentdatabase = createDatabase(fname,sheetnum)
% Load study info
opts = spreadsheetImportOptions("Sheet",sheetnum);
opts = setvartype(opts,1,"string");
opts.DataRange="D2:D6";
studentInfo = readtable(fname,opts);
% convert to table and update data types
varnames = ["STUDY","STUDENTNO","STUDENTNAME","YEAR","ENROLLED"];
infoTbl = table(studentInfo.Var1');
infoTbl = splitvars(infoTbl,1,'NewVariableNames',varnames);
infoTbl = convertvars(infoTbl,["STUDENTNO","YEAR"],"double");
infoTbl = convertvars(infoTbl,"ENROLLED","datetime");
% Load grades
grades1 = readtable(fname,"Sheet",sheetnum,'Range',"A8:E18",'TextType','string',"VariableNamingRule","preserve");
grades2_1 = readtable(fname,"Sheet",sheetnum,'Range',"H8:L18",'TextType','string',"VariableNamingRule","preserve");
grades2_2 = readtable(fname,"Sheet",sheetnum,'Range',"H22:L25",'TextType','string',"VariableNamingRule","preserve");
grades = [grades1;grades2_1;grades2_2];
grades.Properties.VariableNames = ["COURSE","GRADE","EC","DATE","SEMESTER"];
% Remove empty rows
grades(all(ismissing(grades),2),:) = [];
% Combine study info and grades into a single table
studentdatabase = [repmat(infoTbl,height(grades),1), grades];
end
Più risposte (0)
Categorie
Scopri di più su Spreadsheets in Centro assistenza e File Exchange
il 5 Apr 2024
il 14 Apr 2024
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
