Extract specific rows and columns from excel and store in a matrix

56 views (last 30 days)
Hi I have two questions
  1. I have multiple excel files that I want to use matlab to extract specific row and columns from and then save the new data in a matrix for plotting afterwards. So logically, I want matlab to go inside a folder to read excel filename number1, extracting rows(3:till end) and column (I:K), then store it in a matrix in matlab. Next, matlab goes back to the folder and reads excel filename number2, extracting rows(3:till end) and column (I:K), then store it in a matrix and so on and so forth. So it does that same procedure for all the excel files in the folder. The figure below shows how each of the excel files are formatted with data.
  2. After the data is extracted and those matrices are made, I want to plot all those three columns on one 2D figure (X axis is Time, Left Y-axis is PSI and Right Y-axis is ROLL). Given that my data is huge, I am worried it might not all plot on one figure. So if you could provide another option to plot for each matrix, please tell me
The files are very large so I can't unfortunately combine them into one. I have to extract each excel seperately but if I can first extract the data I need and then have some sort of loop or whatever works that plots the data of the all the matrices three columns on one figure.

Accepted Answer

mpz
mpz on 12 Aug 2022
Edited: mpz on 12 Aug 2022
If someone has an easy way to do it, please post. Below is how I was able to solve it. First read all the data from the excel using readtable. Then extracted the specific columns and rows I needed. Finally sorted using column 1 which should be in ascending order. It would have been great if I could do it using the excel filename instead of sorting by columns.
clear all;clc
% Specify the folder where the files live.
myFolder = 'C:\Users\myname\Documents\software\test';
% Check to make sure that folder actually exists. Warn user if it doesn't.
if ~isfolder(myFolder)
errorMessage = sprintf('Error: The following folder does not exist:\n%s\nPlease specify a new folder.', myFolder);
uiwait(warndlg(errorMessage));
myFolder = uigetdir(); % Ask for a new one.
if myFolder == 0
% User clicked Cancel
return;
end
end
% Get a list of all files in the folder with the desired file name pattern.
M=[];
filePattern = fullfile(myFolder, '*.xlsm'); % Change to whatever pattern you need.
theFiles = dir(filePattern);
for k = 1 : length(theFiles)
baseFileName = theFiles(k).name;
fullFileName = fullfile(theFiles(k).folder, baseFileName);
fprintf(1, 'Now reading %s\n', fullFileName);
T = readtable(fullFileName); % Read the entire excel file and stores it in a new matlab table [T]
M=[M;T(1:end,[1,9:11])]; % Extract specific rows and columns from table [T]
B = sortrows(M,1); % Sorts the extracted table [M]
plot(B.Timestamp,B.PSI) % Plots a table with specific columns
end

More Answers (2)

David Hill
David Hill on 11 Aug 2022
If formating is consistent, readmatrix should work.
listing=dir;
M=[];
for k=1:length(listing)
m=readmatrix(listing(k).name);%I am assuming the header information will be removed and formatting is consistent
M=[M;m(:,9:11)];%assuming you can fit all your data together (not too big)
end
  4 Comments
David Hill
David Hill on 11 Aug 2022
Should be k!
M=[];
for k = 1 : 20
T =readtable(sprintf('Number%d.xlsm',k));%need to be inside the folder or update name
M=[M;T(1:end,9:11)];
end

Sign in to comment.


mpz
mpz on 12 Aug 2022
There is the error I keep getting. sprintf only output Number1 not Number1.xlsm
Error using readtable (line 318)
Unable to find or open 'Number1'. Check the path and filename or file permissions.
Error in data2 (line 24)
T =readtable(sprintf('Number%d.xlsm',k));
Here is the full code
clear all;clc
% Specify the folder where the files live.
myFolder = 'C:\Users\myname\Documents\software\test';
% Check to make sure that folder actually exists. Warn user if it doesn't.
if ~isfolder(myFolder)
errorMessage = sprintf('Error: The following folder does not exist:\n%s\nPlease specify a new folder.', myFolder);
uiwait(warndlg(errorMessage));
myFolder = uigetdir(); % Ask for a new one.
if myFolder == 0
% User clicked Cancel
return;
end
end
% Get a list of all files in the folder with the desired file name pattern.
M=[];
filePattern = fullfile(myFolder, '*.xlsm'); % Change to whatever pattern you need.
theFiles = dir(filePattern);
for k = 1 : length(theFiles)
baseFileName = theFiles(k).name;
fullFileName = fullfile(theFiles(k).folder, baseFileName);
fprintf(1, 'Now reading %s\n', fullFileName);
% T = readtable(fullFileName);
T =readtable(sprintf('Number%d.xlsm',k));
M=[M;T(1:end,9:11)];
end

Community Treasure Hunt

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

Start Hunting!