How to extract multiple excel tabs into MATLAB
Show older comments
Hello All,
I have the following code: [num,text] = xlsread('C:\Users\mahnoor.saeed\Documents\P18 Full SRDC MIDAS Simulations');
The excel sheet consists of 40 tabs (each has over 3,000 columns and 20 rows but this is the same in each sheet) but this piece of code only reads the data from the first page of the sheet.
How can I write a loop function to import the data from all 40 tabs as MATLAB variables as I later need to plot certain data.
Any help would be appreciated.
Thankyou.
Answers (1)
Stephen23
on 22 Jun 2023
Do not use deprecated XLSREAD.
It is very odd that your filename does not have a file extension, I fixed that for you:
F = 'C:\Users\mahnoor.saeed\Documents\P18 Full SRDC MIDAS Simulations.xlsx';
S = sheetnames(F);
D = cell(size(S));
for k = 1:numel(S)
D{k} = readtable(F, 'Sheet',S(k));
end
39 Comments
Mahnoor
on 22 Jun 2023
Stephen23
on 22 Jun 2023
"how can I split 'D' into 40 seperate tables (representing each tab)..."
Do you incorrectly think that D only consists of one table? It doesn't: D already consists of 40 separate tables, all conveniently stored in one cell array which you can trivially and efficiently access using basic MATLAB indexing:
If you imagine that creating lots of separate variables would be good data design, then you need to understand that dynamically creating 40 variables in the workspace forces you into writing slow, complex, inefficient, obfuscated, buggy code that is hard to debug every time you try to access those tables in a loop:
So your concept is slow, inefficient, makes processing your data harder. In contrast, the single simple cell array that I showed you makes accessing your data easier. Which is why I used it in my answer. You should too.
Stephen23
on 23 Jun 2023
"I believe one piece of code can be written for this and repeated 40 times for each table. Would you please be able to advise on how to start with this and what the correct format would be?"
Why not just use the loop that is already in my answer? It already loops over all of the sheets.
If you really want a second loop, then do this:
for k = 1:numel(D)
T = D{k};
.. your code, do whatever you want with table T
end
Mahnoor
on 25 Jun 2023
Stephen23
on 25 Jun 2023
"Where is the plotting function in this?"
Where I wrote "your code" is where you put your code.
Mahnoor
on 25 Jun 2023
How can we do that? You didn't attach your workbook(s) or explain what "need to plot certain data" means. What certain data? All we can recommend is plot
help plot
Mahnoor
on 25 Jun 2023
Hello @Image Analyst
I have use the following code provided by @Stephen23
F = 'C:\Users\mahnoor.saeed\Documents\P18 Full SRDC MIDAS Simulations.xlsx';
S = sheetnames(F);
D = cell(size(S));
for k = 1:numel(S)
D{k} = readtable(F, 'Sheet',S(k));
end
When this code runs, I get this as a result:
(40 tables in one variable 'D')
Each table looks like this:
I would like to plot nEmotor + nEngine (both on y-axis) against Time (x-axis) for each table, so when the code runs, the plots automtically pop up. Im not sure how to write a loop for the repition of plotting the data from 40 tables.
Would you please be able to assist with this as I am quite new to MATLAB. Any help would be highly appreciated.
Thankyou.
Image Analyst
on 25 Jun 2023
I'll try. Like I said, attach your workbook.
Hello @Image Analyst
I have attached the mat file below, please do let me know if you can view it. Please ignore the green comments, the running code is right towards the end.
I cannot seem to attach the workbook, as its too huge and for some reasons even when its zipped, its still to large to upload.
But this is how the first few tabs look like in Excel
Thankyou so much for trying.
@Mahnoor: we cannot import and plot a screenshot of data. Please upload a sample data file by clicking the paperclip button. It does not have to be your complete data file: it only needs to contain two or three worksheets and perhaps one hundred rows per worksheet (experiment until you get a reasonable filesize that can be uploaded here). Do not change the format or arrangement of the data on the worksheets.
"I would like to plot nEmotor + nEngine (both on y-axis) against Time (x-axis) for each table, so bascially 40 times"
Does that mean you expect 40 plots each with 2 lines on it, or 1 plot with 80 lines on it?
Mahnoor
on 26 Jun 2023
F = 'P18 Full SRDC MIDAS Simulations.xlsx';
S = sheetnames(F);
for k = 1:numel(S)
T = readtable(F, 'Sheet',S(k));
plot(T.Time,T.nEMotorR,'r-*', T.Time,T.nEngine,'b-+')
title(S(k),'Interpreter','none')
legend("nEMotorR","nEngine")
xlabel("Time (unit?)")
ylabel("???? (unit?)")
saveas(gcf,sprintf('%s.png',S(k)))
end
I added markers to the plot just to demonstrate that the two lines overlap. Of course you can change the color, markers, etc to suit your own needs. The above graphic shows only the last plot, you can find all plots saved in PNG files:
dir *.png
Mahnoor
on 27 Jun 2023
"And split it into column vectors so the sheet is split into many variables in the workspace."
Best avoided: https://www.mathworks.com/matlabcentral/answers/304528-tutorial-why-variables-should-not-be-named-dynamically-eval
Much better: access the data in the table (rather than slow, complex, inefficient, buggy code that magically creates lots of new variables in the workspace).
"Using code, I would like to create a new variable for 'MSideShaftSUM' which adds all of the values of 'MSideShaftRL' to the values of 'MSideShaftRR'."
T = readtable('SRDC Script Test Try.xlsx', 'Sheet','Autobahn');
T.MSideShaftSUM = T.MSideShaftRL + T.MSideShaftRR % did you try adding them?
"And another new variable for 'nWheel Delta' which subtracts all of the values in 'nWheelRL' from the values of 'nWheelRR'."
I showed you addition, I am sure that you can handle the subtraction yourself.
Mahnoor
on 29 Jun 2023
Hello @Stephen23, thankyou so so much for your support and for answering all of my questions. It has been very very helpful. Thankyou once again.
I do have one last question if thats okay, is there any quick way of repeating the file path for the below pieces of code rather than copy/pasting the new path each time into a new line of code (currently have 40 lines of individual code where each looks at the 'same' file location).
"thankyou so so much for your support and for answering all of my questions. It has been very very helpful. Thankyou once again."
Then please remember to click the Accept button. You can also vote for answers that help you.
"is there any quick way of repeating the file path for the below pieces of code rather than copy/pasting the new path each time into a new line of code"
Of course, just use FULLFILE, e.g.:
P = 'C:\Users\mahnoor.saeed\Documents\P17R Test Cycles';
T = readtable(fullfile(P,'Read Track.xlsx'), 'Sheet','VHS Track');
You can use P as many times as you want.
Mahnoor
on 4 Jul 2023
I have pressed the 'Vote' button but not sure where the 'Accept' one is? Thankyou very very much for your help. Apologies, I keep asking more questions.
Referring to the figure below, I would like to enhance the resoultion of the image and still save it at the same time. Would this code be sufficient or can it be made more efficient?
Code: exportgraphics (gcf, 'Plot 1 for Drivehsaft Torque.png', 'Resolution' ,300)
Also, when the plots do appear in the current folder as images, I copy all of these at once (8 plots) and paste it in 1 folder under My Documents.
Is it possible in any way to view 3/4 plots at one time (like the picture below) rather than opening them individually and placing them like this?
Thankyou so much once again
Stephen23
on 4 Jul 2023
" I would like to enhance the resoultion of the image and still save it at the same time."
Try increasing the resolution.
"Is it possible in any way to view 3/4 plots at one time (like the picture below) rather than opening them individually and placing them like this?"
or
Mahnoor
on 29 Jul 2023
Thankyou for your help in regards to my initial question. Would you please be so kind and able to assist with the below?
I have generated this piece of code which displays a graph of Powertrain torque (Nm) vs Time (s) (similar to what was discussed before). This data has been taken from the attached excel file below and have added its pathway/location to the first line of the code so the data is read directly from the sheet. I have also attached the matlab code and excel sheet below for easy access.
I would like to do some kind of pivot table or a similar/another function to generate the information below once the graph has been run/ or during running.
How many seconds (duration) is the the torque in the following conditions: 0 to 100 Nm, 101 to 200 Nm, 201 to 300 Nm, etc. all the way up to the final limit of 1,000 Nm. And would also need it in the opposite direction from 0 to - 100 Nm, - 101 to -200 Nm. Bascially, in easier terms, trying to find out how long did each torque happen for (time at each torque).
Ideally would like one column that displays the Time (s) and a second column that displays each duration of powertrain torque (Nm). Using the attached Excel sheet, around 103 cells in the Engine and Motor columns equates to a complete second.
Any help is highly appreciated. Thankyou.
"How many seconds (duration) is the the torque in the following conditions: 0 to 100 Nm, 101 to 200 Nm, 201 to 300 Nm, etc. all the way up to the final limit of 1,000 Nm. And would also need it in the opposite direction from 0 to - 100 Nm, - 101 to -200 Nm. Bascially, in easier terms, trying to find out how long did each torque happen for (time at each torque). "
This might get you started:
T = readtable('Autobahn Comfort Matlab.xlsx');
T.MPowerTrain = T.MEngine + T.MEMotorR
V = -200:100:1000
histogram(T.MPowerTrain,V) % lets have a quick look first:
C = histcounts(T.MPowerTrain,V) * 0.01 % 0.01 = sample step
This means:
- between -200 and -100 there were 0 seconds
- between -100 and 0 there were 28.69 seconds
- etc.
Alternatively you could probably leverage some of the features of the TIMETABLE class.
Thankyou for your help. Just a few questions:
Q.1 When I run this code, the variables for appear in the workspace as expected. But for some reason, the variables T, V and C wont appear in the command window. How can I make the variables appear in the command window?
Q.2 In the histogram plot, what is the y-axis representing?
Q.3 What is the histcounts function exactly doing?
Q.3 Is there any way to more easily read the plotted data maybe as a table later on as it is a bit difficult to interpret especially since the variables do not appear in my command window?
Thankyou for your help.
Stephen23
on 29 Jul 2023
Q1: "But for some reason, the variables T, V and C wont appear in the command window. How can I make the variables appear in the command window?"
You can display variables in the command window by calling DISP or FPRINTF... or by simply removing the semi-colon at the end of the lines where those variables are defined.
Q2: "In the histogram plot, what is the y-axis representing?"
By the definition of histogram, this is a count of how many elements occur within those bins. It is not normalized.
Q3a: "What is the histcounts function exactly doing?"
By the defnition of histogram, it counts how any elements occur within those bins. It is not normalized.
Q3b: " Is there any way to more easily read the plotted data maybe as a table later on as it is a bit difficult to interpret especially since the variables do not appear in my command window?"
I have no idea what you think is "more easily read", that sounds like something very subjective that would depend a lot on your needs, experience, etc. Of course you can put those values into a table, if you wish.
There is nothing stopping you from displaying them in the command window either.
Mahnoor
on 29 Jul 2023
Would it be possible to view the data in the way below possibly in the command window?
Thankyou
Stephen23
on 29 Jul 2023
"Would it be possible to view the data in the way below possibly in the command window?"
Yes, look at ARRAY2TABLE, TABLE, etc.
Mahnoor
on 30 Jul 2023
I am using this code below as a reference to create a table. 
I have done the follwing but doesnt seem to be working:
Would you know how to fix the code required to run the table and view it in the command window and workspace?
Thankyou so much.
Stephen23
on 30 Jul 2023
"Would you know how to fix the code required to run the table and view it in the command window and workspace?"
Look at the example: it defines the variables AGE, WEIGHT, and HEIGHT before calling TABLE.
Now look at your code: do you define INTERVALOFTORQUE and DURATIONOFTORQUE before calling TABLE? (hint: no)
Mahnoor
on 30 Jul 2023
Hello @Stephen23, I am trying to make my table like the above picture I had sent previously. Not sure how to make [1 x13 double] and [1 x 12 double] appear exactly as all of the values of V and C like how it is in the window? And how to put all of these values of V and C under their associated columns?
"Not sure how to make [1 x13 double] and [1 x 12 double] appear exactly as all of the values of V and C like how it is in the window? And how to put all of these values of V and C under their associated columns?"
You need to make those variables column vectors of the same length, e.g.:
IntervalOfTorque = V(:);
DurationOfTorque = [C(:);NaN];
Mahnoor
on 31 Jul 2023
Dyuman Joshi
on 31 Jul 2023
This is very simple stuff @Mahnoor, you should be figuring this out on your own. Atleast you should be trying to figure out things on your own.
Read the error, try to understand what it says. Look at the code, see what it does.
C(:)
The above command returns the content in C as a column vector.
To add another element at the end of column vector (i.e. performing vertical concatenation), you need to use a semi-colon instead of a comma.
Just use
[C(:);NaN]
Stephen23
on 31 Jul 2023
@Dyuman Joshi: thank you for the explanation. I fixed my comment.
Mahnoor
on 31 Jul 2023
Thankyou @Dyuman Joshi and @Stephen23. I do have another query, I am trying to replicate the code and run it on another plot. I have attched the initial image which shows that the table and histogram is produced for Powertrain torque.
But, when I seem to do the same for the Driveshaft torque, in the second picture, the code gives the following error. I have tried to rename the variables and change names but still cant seem to get rid of the error.
Would higly appreciate any help with this. Thankyou.
"Would higly appreciate any help with this."
You did not upload the corresponding data file with your last comment, but the last file you uploaded does not have any variables named MSIDESHAFTRL or MSIDESHAFTRR. If the file does not contain those headers then they will not exist in the imported data table:
"I have tried to rename the variables and change names"
You seem to be using the shotgun approach to debugging. That is not very efficient.
Mahnoor
on 31 Jul 2023
Thankyou @Stephen23, that was my mistake was referring to the wrong Excel sheet and completely forgot to look at that specific line.
I am trying to use 2 inputs against time which is engine and motor speed but cannot seem to get the below code for the histogram to work. I have added a new variable to hold the addtional information on speed but still doesnt seem to be working. I have attached an updated Excel sheet which contain nEngine and nEMotorR.
Any hep would be highly appreciated. Thankyou.
"I am trying to use 2 inputs against time which is engine and motor speed but cannot seem to get the below code for the histogram to work."
What makes you think that it is possible to supply two inputs to HISTOGRAM like that?
I don't see anything in the HISTOGRAM documentation that supports that syntax.
Mahnoor
on 1 Aug 2023
Mahnoor
on 9 Aug 2023
It seems that I have got some error in my plots using the code below.
clear; close all; %do not comment
P = 'C:\Users\mahnoor.saeed\Documents\P17R Test Cycles'; %do not comment %change only this line
%% Use for plotting AutoBahn Comfort
T = readtable(fullfile(P,'Autobahn Comfort Matlab.xlsx'),'Sheet','Autobahn Comfort');
%% Plot #5 for Engine Torque (MEngine against Time)
figure ; plot (T.Time,T.MEngine, 'LineWidth',0.80)
xlabel('Time (s)'); ylabel('Engine Torque (Nm))')
ylim([-220 1200]); yticks(-220:142:1200)
legend('MEngine', 'Location', 'best')
title ('Engine Torque vs Time')
exportgraphics(gcf, 'Plot #5 for Engine Torque.png','Resolution',300)
%How many seconds is the engine torque in the following conditions?
T = readtable('Autobahn Comfort.xlsx');
T.MEngine;
V = -220:142:1200;
C = histcounts(T.MEngine,V) * 0.01;
Interval_Of_Torque_Nm = V(:);
Duration_Of_Torque_s = [C(:);NaN];
Engine_Torque = table(Interval_Of_Torque_Nm, Duration_Of_Torque_s, 'VariableNames',{'Interval of Engine Torque (Nm)','Duration of Engine Torque (s)'})
figure ; histogram(T.MEngine,V);
xlabel('Engine Torque (Nm)')
legend('Time (s)', 'Location', 'best')
title ('Duration of Engine Torque')
exportgraphics(gcf, 'Histogram #7 for Duration of Engine Torque.png','Resolution',300)
%
%% Plot #6 for Motor Torque (MEMotorR against Time)
figure ; plot (T.Time,T.MEMotorR, 'LineWidth',0.80)
xlabel('Time (s)'); ylabel('MEMotorR (Nm))')
yline(200, '--r', 'Motor torque limit of 200Nm'); yline(-200, '--r', 'Motor torque limit of -200Nm')
ylim([-220 220]); yticks(-220:40:220)
legend('MEMotorR', 'Location', 'best')
title ('Motor Torque vs Time')
exportgraphics(gcf, 'Plot #6 for Motor Torque.png','Resolution',300)
%How many seconds is the motor torque in the following conditions?
T = readtable('Autobahn Comfort.xlsx');
T.MEMotorR;
V = -220:40:220;
C = histcounts(T.MEMotorR,V) * 0.01;
Interval_Of_Torque_Nm = V(:);
Duration_Of_Torque_s = [C(:);NaN];
Motor_Torque = table(Interval_Of_Torque_Nm, Duration_Of_Torque_s, 'VariableNames',{'Interval of Motor Torque (Nm)','Duration of Motor Torque (s)'})
figure ; histogram(T.MEMotorR,V);
xlabel('Motor Torque (Nm)') % The histogram function automatically chooses an appropriate number of bins to cover the range of values in y
legend('Time (s)', 'Location', 'best')
title ('Duration of Motor Torque')
exportgraphics(gcf, 'Histogram #8 for Duration of Motor Torque.png','Resolution',300)
%
For ex. the graph on the right has some additional horizaontal blue line running in the middle (this is not supposed to be there) whilst the one on the left is perfectly fine. I am not sure how this has happened as I have not changed anything in the data of the Excel sheet. I have attached the Excel sheet, would you please be able to have a look at this?
This is the warning I receive in the workspace but not sure what it entirely means:
Categories
Find more on Logical in Help Center and File Exchange
on 9 Aug 2023
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
