Iteratively selecting excel files

Hi,i have around 1500 excel files in a folder with almost 900 x 130 cells of data in each file and i want to read (xlsread) one-by-one by randomly selected some rows from each file and this process continues to all files and then write (xlswrite) step by step to a new file. Please give some hint... Thanks

[Geoff Hayes]

Muahmmad - I gave a hint at http://www.mathworks.com/matlabcentral/answers/147292-chossing-random-data-from-xls-files. Did you try to code that up?

Answer 1

[Image Analyst]

Geoff, he accepted it but I doubt he actually tried to code anything. If I were to do this, I'd use the FAQ http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F to loop over every xlsx file. Then use ActiveX calls to do the transfer of data:

myFolder = 'C:\my Excel workbooks';
if ~isdir(myFolder)
  errorMessage = sprintf('Error: The following folder does not exist:\n%s', myFolder);
  uiwait(warndlg(errorMessage));
  return;
end
filePattern = fullfile(myFolder, '*.xls*');
% TO DO: ActiveX call to open Excel.
% TO DO: ActiveX call to open output workbook
excelFiles = dir(filePattern);
for k = 1:length(excelFiles)
  baseFileName = excelFiles(k).name;
  fullFileName = fullfile(myFolder, baseFileName);
  fprintf(1, 'Now reading %s\n', fullFileName);
  % TO DO: ActiveX call to open workbook
  % TO DO: ActiveX call to get number of rows
  % TO DO: Call to randi(numberOfRows, 1,1) to get random starting and ending rows.
  % TO DO: ActiveX call to read the data from the input workbook.
  % TO DO: ActiveX calls to send data to output workbook.
  % TO DO: ActiveX calls to close input workbook
end
% TO DO: ActiveX calls to save and close output workbook
% TO DO: ActiveX calls to quit Excel: Excel.Quit
% TO DO: delete Excel object: delete('Excel')

I attach an ActiveX demo that you will easily be able to adapt, or at least you should be able to.

The reason for using ActiveX, instead of xlsread() and xlswrite(), is that you want to process these 1500 files in a finite amount of time. If you don't use ActiveX it could take hours instead of minutes.

Also, I don't know what is interactive about this. You said you want to process them one by one. If you don't want all of them in the folder but just user-chosen ones, then skip the call to dir() and put a call to uigetfile() inside the loop, though with 1500 files there is no way on earth your use will remember which files have been processed yet and which have not, and thus will not know which file to select.

[Muhammad Usman]

Geoff Hayes,here the code

clc
  source_dir = 'E:\All files\xls_c11'; %here is the path where all the 1500 excel files are placed
  source_files = dir(fullfile(source_dir, '*.xlsx'));
   j = 0;

for i = 1%:length(source_files)
    what(fullfile(source_dir,source_files(i).name));
    [numbers, strings, misc] = xlsread(fullfile(source_dir,source_files(i).name),'sheet1');
    [rows, columns] = size(numbers);

%     j= j+ rows;
    p = randperm(rows,69);

      for s=p(1:69)
      str=sprintf('%c%d:%c%c%d','E',s,'EB',s);
      data = xlsread(fullfile(source_dir,source_files(i).name), 'sheet1', str);  

      j = j+1;
      str1=sprintf('%c%d:%c%c%d','A',j,'DX',j);
      xlswrite('E:\All files\c11_voc.xlsx',data,'sheet1',str1);
      end
  end

It takes almost 80 secs for 1 file to read and write and when it goes for 20 files an error appears "error registering event(s) advise failed " that stops the code. Please improve my code and help me in adapting the ActiveX. Thanks for your time

[Image Analyst]

Maybe try to go find Geoff at your other thread http://www.mathworks.com/matlabcentral/answers/147292-chossing-random-data-from-xls-files

[Muhammad Usman]

yes i am sorry for that. Please help me on my this code.