Excel sheet extraction data

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 ?

Answer 1

[Dave B]

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 

[Image Analyst]

I think you mean

a=readmatrix('foo.xlsx','Sheet','Sheet1','Range','F32:L33');

because they need to read BOTH row 32 and row 33. Then row 32 goes into matrix 1 and row 33 goes into matrix 2. And of course that is for just one workbook so you'll still need the loop over all workbook files.

[Dave B]

You're totally right, I read it (incorrectly) as row 32 from sheet 1 and row 33 from sheet 2.

[basma awad]

Is there a way to do a loop over 30 differenct excel sheet?

[Dave B]

Sure! You can use sheetnames to get a list of excel sheets if you want all of the sheets in an excel file and they're not named in a way that you can construct the strings naturally (e.g. Sheet1, Sheet2, etc.)

sheets = sheetnames('MyExcelFile.xlsx')
for i = 1:numel(sheets)
    a=readmatrix('foo.xlsx', 'Sheet', sheets(i), 'Range', 'F32:L33');
    % ... do stuff with a here
end

[basma awad]

Am sorry i have 20 different excel file, they are seperate and not in the same excel file. so 20 excel files.

[Dave B]

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]

Thank you so much !!

Answer 2

[Image Analyst]

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.

https://matlab.fandom.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F