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

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.

Answer 1

[mpz]

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

Answer 2

[David Hill]

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

[mpz]

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);
    M=[M;T(1:end,9:11)];
end

I was able to do the above which essentially reads all my files in the "test" folder, then picks rows (1,end) and columns 9:11, then stores in matrix M. The problem is that the data in Matrix "M" is not in order. I need the data to be in order i.e.

In my computer folder, the excel files are sorted as

Number1.xlsm

Number 2.xlsm

Number 3.xlsm

....

Number 10.xlsm

...

Number 20.xlsm

However in matlab the data is read and written in matrix as

Number1.xlsm

Number10.xlsm

....

Number 2.xlsm

Number20.xlsm

I need it to be read and written Number1.xslm, Number2.xslm, Number3.xslm, etc.

[David Hill]

for k=1:20
    m=readtable(sprintf('Number%d.xlsm',k));
end

[mpz]

Does this go inside my current for loop under

T = readtable(fullFileName);

or somewhere else. What does the "d" represent?

[David Hill]

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

Answer 3

[mpz]

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