Continue loop on other files if current file is deleted within a loop?

I want to delete *.xlsx files if they meet a certain criteria. However when a file is deleted based on the first if statement it continues to try and look though the files for the other criteria. How can bypass the remaining if statements and continue onto the next file if one of the if statements are satisfied?

excel_xlsx=dir('*.xlsx');  % list of the files

for i=1:length(excel_xlsx)
    [~,file]=xlsfinfo(excel_xlsx(i).name);
    L=length(file);
    [~,txt]=xlsread(excel_xlsx(i).name,L,'B2');   % read the text
  if strcmp(txt,'Number:')
    delete(excel_xlsx(i).name)
  else
    [~,txt]=xlsread(excel_xlsx(i).name,1,'B3');   % read the text
    strcmp(txt,'PROGRAM')
    delete(excel_xlsx(i).name)
  else
    [~,txt]=xlsread(excel_xlsx(i).name,1,'A10');   % read the text
    strcmp(txt,'UPPER')
    delete(excel_xlsx(i).name)
  else
    [~,txt]=xlsread(excel_xlsx(i).name,1,'A2');   % read the text
    strcmp(txt,'Effect')
    [~,sheets] = xlsfinfo(excel_xlsx(i).name);
    xlsprotect(excel_xlsx(i).name,'unprotect_sheet',sheets{1,1});
    xlswrite(excel_xlsx(i).name,' ',1,'F1');
    xlswrite(excel_xlsx(i).name,' ',1,'F2');
    xlswrite(excel_xlsx(i).name,' ',1,'F3');
    xlswrite(excel_xlsx(i).name,' ',1,'E1');
    xlswrite(excel_xlsx(i).name,' ',1,'E2');
    xlswrite(excel_xlsx(i).name,' ',1,'E3');
    xlswrite(excel_xlsx(i).name,' ',1,'E4');
  end

end

Answer 1

[Walter Roberson]

excel_xlsx=dir('*.xlsx');  % list of the files

for i=1:length(excel_xlsx)
    [~,file]=xlsfinfo(excel_xlsx(i).name);
    L=length(file);
    [~,txt]=xlsread(excel_xlsx(i).name,L,'B2');   % read the text
    if strcmp(txt, 'Number:')
      delete(excel_xlsx(i).name);
      continue;
    end
    [~,txt]=xlsread(excel_xlsx(i).name,1,'B3');   % read the text
    if strcmp(txt, 'PROGRAM')
      delete(excel_xlsx(i).name);
      continue;
    end
    [~,txt]=xlsread(excel_xlsx(i).name,1,'A10');   % read the text
    if strcmp(txt, 'UPPER')
      delete(excel_xlsx(i).name);
      continue;
    end
    [~,txt]=xlsread(excel_xlsx(i).name,1,'A2');   % read the text
    if strcmp(txt, 'Effect')
      [~,sheets] = xlsfinfo(excel_xlsx(i).name);
      xlsprotect(excel_xlsx(i).name,'unprotect_sheet',sheets{1,1});
      xlswrite(excel_xlsx(i).name,' ',1,'F1');
      xlswrite(excel_xlsx(i).name,' ',1,'F2');
      xlswrite(excel_xlsx(i).name,' ',1,'F3');
      xlswrite(excel_xlsx(i).name,' ',1,'E1');
      xlswrite(excel_xlsx(i).name,' ',1,'E2');
      xlswrite(excel_xlsx(i).name,' ',1,'E3');
      xlswrite(excel_xlsx(i).name,' ',1,'E4');
    end

end

However, having all of those xlswrite is inefficient, especially for .xlsx files, as each one can involve rewriting the entire text of the xml file inside the .xlsx archive.

It would be more efficient to read all of the data at the same time. The way reading .xlsx files is implemented, when you put a restriction on which cells to read, that is handled by reading the entire .xml file and throwing away the unwanted cells, so you would be more efficient to just have one xlsread(), do the appropriate tests, modify the appropriate cells, and write the result out if it changed.

Note by the way that you read in the list of sheets early on, so it is not efficient to do a second xlsinfo() .

Could you confirm that you want to read data from the last sheet, but that it is the first sheet that you want to modify E1:F3 and E4 of ?

[Calabrese]

Thank you, this runs much more smoothly.

I forgot about the extra xlsinfo, I'll remove it. I wasn't aware that reading a specific cell is slower that reading the file as a whole, thank you for sharing that. I only read data on the last sheet for the first criteria.

[Walter Roberson]

It is possible that reading specific cells from .xls files is efficient on MS Windows systems with Excel installed.

But .xlsx files are actually zip archives with several directories and a number of .xml files, and the way MATLAB handles reading them is to use regexp to pull out all occurrences of the appropriate data structures and then later to look through what it found to figure out which parts to keep. This process is faster than parsing the .xml text file line by line to figure out whether the entries on the line are part of what should be kept, because doing that testing at the MATLAB level gets expensive.

Answer 2

[Image Analyst]

You can skip the contents of the loop and restart at the top of the loop with the next index by using the continue command.

But I don't understand two things. You're calling xlsread() right after you deleted the file? Why? It won't be there to read! Secondly, why do you need to restart the loop, either with the next index or with an index of i=1? Why??? I don't see any need for that at all. Explain why you think that is necessary.

[Calabrese]

I think you have a misunderstanding of what I was trying to do by reading the code. I am not very experienced with loops however I modified the code to the following and it works now. Do you know of a more efficient way to write it so I don't have to read the text if the file was previously deleted? I am not sure how to incorporate continue here..

excel_xlsx=dir('*.xlsx');  % list of the files

for i=1:length(excel_xlsx);
    [~,file]=xlsfinfo(excel_xlsx(i).name);
    L=length(file);
    [~,txt]=xlsread(excel_xlsx(i).name,L,'B2');   % read the text
    [~,txt2]=xlsread(excel_xlsx(i).name,1,'B3');  % read the text
    [~,txt3]=xlsread(excel_xlsx(i).name,1,'A10'); % read the text
    [~,txt4]=xlsread(excel_xlsx(i).name,1,'A2');  % read the text
  if strcmp(txt,'Number:');
    delete(excel_xlsx(i).name);
  elseif strcmp(txt2,'PROGRAM');
    delete(excel_xlsx(i).name);
  elseif strcmp(txt3,'UPPER');
    delete(excel_xlsx(i).name);
  else
    strcmp(txt4,'Effect');
    [~,sheets] = xlsfinfo(excel_xlsx(i).name);
    xlsprotect(excel_xlsx(i).name,'unprotect_sheet',sheets{1,1});
    xlswrite(excel_xlsx(i).name,' ',1,'F1:F3');
    xlswrite(excel_xlsx(i).name,' ',1,'E1:E4');
  end
end

[Image Analyst]

OK, good, now the xlsread() line is not the very next line of code after the delete() line like it used to be.

The file will not be read (or try to get read) after you delete it because each filename in excel_xlsx structure is looked at exactly once (to read or delete) and never again.

Explain exactly how you think you might be calling xlsread() on a file after it's been deleted, because I don't see how that's possible, unless you have a second, unshown, loop where you don't update excel_xlsx and do the loop using the old excel_xlsx, which would be a bad idea.

[Calabrese]

I would like to bypass the remaining if statements and continue onto the next file if one of the if statements is satisfied. I would also like to re-position the xlsread functions to only read the file if it still exists, so consecutively after each delete function like I originally had it, otherwise I am reading the file 3 extra times for nothing if the first if statement is satisfied.

[Image Analyst]

If you have R2015b (I believe that's the version) or later, calling xlsread 3 or 4 times won't take much longer than calling it once since the Excel server is left running and it's simply memory transfer, you don't have to launch and shutdown Excel like you did in earlier versions which took a lot of time.