Extract same columns from multiple excel files

I have 145 excel files all formatted the same way. I want to pull the same cells from each excel file and compile all the values into one matrix in matlab. I want cells A19:E? (different number of rows in each excel file), as well as single values in C4, C11, C12, C13, and C14 that should be place in separate columns but repeated for all rows in a single file.

I'm currently using the following code:

clear
f=dir(fullfile('C:\Users\ct\Documents\MastThesis\Matlab\ExcelFiles\OK_UIC_Wells\Wells_by_Operator','*.xlsx'));
for i=1:length(f)
  data=xlsread(f(i).name,'C4','C11:C14','A9:E800'); % 800 is max number of rows in all files
end

What am I missing?

Answer 1

[dpb]

Several things--

1. xlsread only will handle one range expression per call
2. data is a single variable so each pass thru overwrites what was there before (if the xlsread call were correct anyway)

Simplest way to do this would be to simply read the full worksheet and select what you want from it from memory and build the result--specifically how to compile into that resulting array depends on how you want the result. Are the multiple datasets to be strung end-to-end in a 2D array or kept separate by, for example, using the planes of a 3D array for each sheet? Or, if as you note there's not the same number of rows per worksheet, you could use a cell array for each.

What would work bestest would depend in large part on just what you need to do with the results when you've got 'em. Also, just how you want these individual values, specifically, would help.

ADDENDUM

OK, with the clarification and a test, the following should get you started...

cidx=[11:14]-8;  % index of rows wanted overlapping in data array
data=[];         % initialize an empty arrays
c=[];
for i=1:length(f)
  data=[data;xlsread(f(i).name,'A9:E800')];  % accumulate array
  c4=xlsread(f(i).name,'C4:C4');             % get the one additional cell
  c=[c;[c4 data(cidx,3)]];                   % build the C4 column
end

At the end you'll have the two arrays. It'll begin to bog down owing to the automagic reallocation; if it gets to be too bad and you have to do this more than just once, preallocate a very large array for data and c that guesses at the total number of rows needed and keep a running index of the sizes read and index into the array, checking there is sufficient space first. If needed, then reallocate additional memory then.

Alternatively, if the spreadsheet is full of data and you're just starting at this location, it could be expedient to just use

data=xlsread(d(i).name);

and use indexing inside that array to select the data similarly as I did for the C values that are in the already read array.

[Hannah Chambles]

Yeah, the output I was getting was data from only one excel file, so that makes sense. When you say read the full worksheet and select what you from it from memory, do you mean just read each excel file separately? I'm trying to avoid having to do that since I have over 100, but maybe I'm misunderstanding what you mean. I want to string the results end-to-end in a 2D array.