Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Iteratively selecting excel files

Asked by Muahmmad on 15 Aug 2014
Latest activity Commented on by Muahmmad on 23 Aug 2014

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

1 Comment

Muahmmad

Products

No products are associated with this question.

1 Answer

Answer by Image Analyst on 16 Aug 2014
Edited by Image Analyst on 16 Aug 2014

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.

3 Comments

Muahmmad on 23 Aug 2014

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

Muahmmad on 23 Aug 2014

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

Image Analyst

Contact us