How to skip Excel files in a loop when special characters exist as sheet names?

Question

Calabrese 2017-7-10

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/348068-how-to-skip-excel-files-in-a-loop-when-special-characters-exist-as-sheet-names

回答： Calabrese 2017-7-14

I am processing multiple excel files by getting file info, finding the last sheet, if the last sheet has 'Number' in B2, I delete the file.

MS_xlsx=dir('*.xlsx');  % list of the files
for i=1:length(MS_xlsx)
    [~,file]=xlsfinfo(MS_xlsx(i).name);
    last_sheet=file{end};
    [~,txt]=xlsread(MS_xlsx(i).name,last_sheet,'B2');   % read the text
  if strcmp(txt,'Number:')
    delete(MX_xlsx(i).name)
  end
end

Some of the excel files have special characters within the sheet name (symbols, foreign language characters, odd spacing). The sheets are recognized as (Nut Plate???? or Nut Plate or ????) and the code errors out at the following when searching for the nonexistent sheet (Nut Plate???? or Nut Plate or ????).

[~,txt]=xlsread(SVMS_xlsx(i).name,last_sheet,'B2'); % read the text

Since this is the case, how can I skip the file if the sheet name 'doesn't exist' (it does but is translated) because MATLAB recognizes it with either additional spaces or question marks?

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Calabrese 2017-7-14

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/348068-how-to-skip-excel-files-in-a-loop-when-special-characters-exist-as-sheet-names#answer_274224

在 MATLAB Online 中打开

This doesn't solve the question but provides another solution to the need. Find length of the file and instead of trying to read the sheet name, we can read the sheet position by inputting the length into the sheet section of xlsread.

MS_xlsx=dir('*.xlsx');  % list of the files
for i=1:length(MS_xlsx)
    [~,file]=xlsfinfo(MS_xlsx(i).name);
    L=length(file);
    [~,txt]=xlsread(MS_xlsx(i).name,L,'B2');   % read the text
  if strcmp(txt,'Number:')
    delete(MX_xlsx(i).name)
  end
end

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 2

Walter Roberson 2017-7-10

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/348068-how-to-skip-excel-files-in-a-loop-when-special-characters-exist-as-sheet-names#answer_273570

在 MATLAB Online 中打开

https://www.mathworks.com/help/matlab/ref/isstrprop.html

For example,

if ~all(isstrprop(last_sheet, 'alphanum'))

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Calabrese 2017-7-10

编辑：Calabrese 2017-7-10

在 MATLAB Online 中打开

Hi Walter, MATLAB locates the last_sheet and assigns it a different name if the original had odd characters or odd spacing. Since sometimes it is just odd spacing, I was hoping to search the file worksheets for the translated sheet name and since it doesn't exist, skip it and move to the next file. Like this but it's not working...

MS_xlsx=dir('*.xlsx');  % list of the files
for i=1:length(MS_xlsx)
    [~,file]=xlsfinfo(MS_xlsx(i).name);
    last_sheet=file{end};
    sheetValid = any(strcmp(MS_xlsx(i).name, last_sheet));
%    if sheetValid == 1;
    [~,txt]=xlsread(MS_xlsx(i).name,last_sheet,'B2');   % read the text
  if strcmp(txt,'Number:')
    delete(MS_xlsx(i).name)
  end
%    else if sheetValid == 0;
%         end
%    end
end