Get non numerical parts from different sheets in excel file.
43 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Claartje Nijman
el 5 de Abr. de 2024 a las 14:27
Editada: Cris LaPierre
el 14 de Abr. de 2024 a las 20:15
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 comentario
Stephen23
el 5 de Abr. de 2024 a las 20:47
"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.
Respuesta aceptada
Cris LaPierre
el 5 de Abr. de 2024 a las 16:00
Editada: Cris LaPierre
el 5 de Abr. de 2024 a las 16:02
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 comentarios
Claartje Nijman
el 14 de Abr. de 2024 a las 11:32
Editada: Cris LaPierre
el 14 de Abr. de 2024 a las 18:11
Cris LaPierre
el 14 de Abr. de 2024 a las 20:14
Editada: Cris LaPierre
el 14 de Abr. de 2024 a las 20:15
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
Más respuestas (0)
Ver también
Categorías
Más información sobre Data Import from MATLAB en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!