Excel sheet extraction data

7 views (last 30 days)
basma awad
basma awad on 16 Nov 2021
Commented: basma awad on 19 Nov 2021
Hello,
I have 20 excel sheet which contain a bunch of data. I want to extract element from these excel and make 2 matrix.
the first matrix is : row 32 from each excel sheet and only three elemet in coloum 6,8 and 12
the second matrix is row 33 from each excel sheet and only three element which are again element 6,8 and 12.
Is there an easy way to do it instead of importing each excel ark then hand piciking the elemet of each 20 excel ark and making them into a matrix ?

Answers (2)

Dave B
Dave B on 16 Nov 2021
Edited: Dave B on 16 Nov 2021
When you call readtable (or readmatrix or readcell) you can specify a range. I think the range has to be contiguous (i.e. you could grab row 32 columns 6 to 12.
Having said that, it's probably easier to just read in the whole row. That's still less than reading in the whole thing (though you can check performance, my guess is it makes little difference):
a=readmatrix('foo.xlsx','Sheet','Sheet1','Range','F32:L32');
a([1 3 7])
ans = 1×3
57 67 87
% probably easier to read the whole row in:
b=readmatrix('foo.xlsx','Sheet','Sheet1','Range','32:32');
b([6 8 12])
ans = 1×3
57 67 87
% readtable version
c=readtable('foo.xlsx','Sheet','Sheet2','Range','33:33');
c(:,[6 8 12])
ans = 1×3 table
Var6 Var8 Var12 ____ ____ _____ 156 166 186
  7 Comments
Dave B
Dave B on 19 Nov 2021
For different files it's similar:
fp = 'C:\mypath\to\myexcelfiles';
% this is all files in a folder...if you have a different subset,
% or they're in different places, you'll need to come up with an
% alternate approach to telling MATLAB which files to read...
fl = dir(fullfile(fp,'*.xlsx'));
for i = 1:numel(fl)
a = readmatrix(fullfile(fp,fl(i).name), 'Range', 'F32:L33')
end
basma awad
basma awad on 19 Nov 2021
Thank you so much !!

Sign in to comment.


Image Analyst
Image Analyst on 16 Nov 2021
No "easier" way. Since you have 20 different workbooks with unique filenames, you're going to have to import each one one-at-a-time, and then extract the data and paste it into your two output matrices. See the FAQ for code samples to process a sequency of files.

Community Treasure Hunt

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

Start Hunting!