MATLAB: How to extract a subset of person identified data from several excel files into one excel file

data extractionmultiple excel files

I have a collection of seven very large (10,000 records in each) excel files where the data (33 attributes) for each student (identified by student ID) is spread across each of the seven excel files. I'd like to randomly extract 1000 students data from across the seven files into one excel file (1000 rows, 34 columns). I'm hoping that someone has already written MATLAB code to do something similar and save me from my very patchy programming skills? Many thanks.

Best Answer

Well, there's a lot of code that will read a spreadsheet, but there's probably nothing that you can just blindly throw at the particular problem.
But, it's not hard problem to solve if the spreadsheets are reasonably regular in their format.
Just read each file into a an array and select the rows desired--if it is truly random selection, then something like
d=dir('AppropriateWildCardString.xls*');   % get the dir() list of files
for i=1:length(d)
  [~,~,raw]=xlsread(d(i).name);            % read raw data from the sheet
  if i==1                                  % generate a random subset indexing vector
    ix=randperm(size(raw,1),1000);
    data=raw(ix,:);                        % and save the selected subset
  else
    data=[data raw(ix,2:end)];             % and append to previous
  end
end
At that point you'll have cell array of the desired data; I'd recommend converting to table and storing the data in appropriate variable types (double, string, categorical, ...) based on its characteristics.
Related Question