Clear Filters
Clear Filters

Get non numerical parts from different sheets in excel file.

1 view (last 30 days)
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 Comment
Stephen23
Stephen23 on 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.

Sign in to comment.

Accepted Answer

Cris LaPierre
Cris LaPierre on 5 Apr 2024
Edited: Cris LaPierre on 5 Apr 2024
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 Comments
Claartje Nijman
Claartje Nijman on 14 Apr 2024
Edited: Cris LaPierre on 14 Apr 2024
This is my simple code and the sample data can be found in the attachment>
clc
clear all
r=3; %number of sheets
%initials
P2=[];
P3=[];
P4=[];
P5=[];
for k=1:r
k;
[num,txt,P] = xlsread("Book1.xlsx",k);
%P = readcell("Book1.xlsx")
%personal info
P1=P(1:6,4);
P1=(P1)';
%Block 1 grades
P2=P(8:17,1:5);%get block one
j=size(P2,1);%number of rows in block 1
P11=repmat(P1,j,1);%match dimensions
P2=[P11 P2];%add personal info to grades
%Block 2 grades
P3=P(8:24, 8:12);
i=size(P3,1);
P31=repmat(P1,i,1);
P3=[P31 P3];
P4=[P2; P3];
%StudentDatabase
P5=[P5;P4];
end
P5
P5 = 81x11 cell array
{'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Algebra1' } {[ 7]} {[ 5]} {[43895]} {[ 1]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Algebra2' } {[ 6]} {[ 5]} {[43896]} {[ 1]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Algebra3' } {[ 8]} {[ 3]} {[43897]} {[ 1]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Algebra4' } {[ 6]} {[ 1]} {[43898]} {[ 1]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Algebra5' } {[8.3000]} {[ 5]} {[43899]} {[ 1]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {[ NaN]} {[ NaN]} {[ NaN]} {[ NaN]} {[NaN]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Calculus1' } {[5.6000]} {[ 2]} {[44197]} {[ 2]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Calculus2' } {[6.5000]} {[ 3]} {[44198]} {[ 2]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Calculus3' } {[ NaN]} {[ 4]} {[44564]} {[ 2]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Calculus4' } {[8.9000]} {[ 5]} {[44200]} {[ 2]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Datascience1'} {[ NaN]} {[ 8]} {[44715]} {[ 3]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Datascience2'} {[ NaN]} {[ 6]} {[44716]} {[ 3]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Datascience3'} {[ NaN]} {[8.3000]} {[44717]} {[ 3]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Datascience4'} {[ NaN]} {[ 9]} {[44718]} {[ 3]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {'Datascience5'} {[ NaN]} {[5.6000]} {[44719]} {[ 3]} {'BLABLABLA'} {[12345]} {'Roy Amerson'} {[2020]} {[38149]} {[NaN]} {[ NaN]} {[ NaN]} {[ NaN]} {[ NaN]} {[NaN]}
Cris LaPierre
Cris LaPierre on 14 Apr 2024
Edited: Cris LaPierre on 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
sDB = 63x10 table
STUDY STUDENTNO STUDENTNAME YEAR ENROLLED COURSE GRADE EC DATE SEMESTER ___________ _________ _____________ ____ ___________ ______________ _____ ___ ___________ ________ "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Algebra1" 7 5 05-Mar-2020 1 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Algebra2" 6 5 06-Mar-2020 1 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Algebra3" 8 3 07-Mar-2020 1 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Algebra4" 6 1 08-Mar-2020 1 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Algebra5" 8.3 5 09-Mar-2020 1 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Calculus1" 5.6 2 01-Jan-2021 2 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Calculus2" 6.5 3 02-Jan-2021 2 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Calculus3" NaN 4 03-Jan-2022 2 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Calculus4" 8.9 5 04-Jan-2021 2 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Datascience1" NaN 8 03-Jun-2022 3 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Datascience2" NaN 6 04-Jun-2022 3 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Datascience3" NaN 8.3 05-Jun-2022 3 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Datascience4" NaN 9 06-Jun-2022 3 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "Datascience5" NaN 5.6 07-Jun-2022 3 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "geometry1" NaN 5.6 03-Jan-2023 4 "BLABLABLA" 12345 "Roy Amerson" 2020 11-Jun-2004 "geometry2" NaN 5.7 04-Jan-2023 4
%% 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

Sign in to comment.

More Answers (0)

Categories

Find more on Data Import from MATLAB in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!