Find NaNs at the end of an Excel file

Question

James Ryan 2021-3-5

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/764166-find-nans-at-the-end-of-an-excel-file

评论： Walter Roberson 2021-3-5

Hi,

New user, first question, so bear with me but I haven't been able to find anything on it. I need to find file names containing NaN values that are on the end of a comma delimited file. Xlsread seems to automatically truncate. The files vary in length. What is the best way to do this?

baseInputFolder = 'C:\Users\me\Desktop\Test\';
filename = strcat(baseInputFolder,'find_NaNs.xlsx');
inputFiles = dir(fullfile(baseInputFolder,'**\*.csv'));
nextRow = 1;
for k = 1:length(inputFiles)
    baseFileName = inputFiles(k).name;
    fullFileName = fullfile(inputFiles(k).folder, baseFileName);
    fprintf('Reading file %d of %d named %s\n',k,length(inputFiles),baseFileName);
    if any(isnan(xlsread(fullFileName,1)), 'all')
        range1=sprintf('%s%d','A',nextRow);
        writematrix(fullFileName,filename,'Sheet','Sheet1','Range',range1);
        nextRow = nextRow+1;
    end
end

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

请先登录，再进行评论。

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

Answer 1

Star Strider 2021-3-5

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/764166-find-nans-at-the-end-of-an-excel-file#answer_640191

See if the contains function (introduced in R2016b) will do what you want.

7 个评论
显示 5更早的评论隐藏 5更早的评论

Star Strider 2021-3-5

在 MATLAB Online 中打开

I’m not certain what you’re doing, or if this is any significant improvement:

nextRow = 1;
for k = 1:length(inputFiles)
    baseFileName = inputFiles(k).name;
    fullFileName = fullfile(inputFiles(k).folder, baseFileName);
    fprintf('Reading file %d of %d named %s\n',k,length(inputFiles),baseFileName);
    D1 = readmatrix(fullFileName);
    [r,c] = find(isnan(D1));
    nrnan = nnz(isnan(D1));                                                         % Number Of ‘NaN’ Values In File
    nanconsec = nnz(diff(r)>1)==0;                                                  % If Rows Containing ‘NaN’ Values Are Consecutive = ‘true’
    if nrnan & nanconsec
        nanfiles{nextRow} = fullFileName;                                           % If Both Conditions Are ‘true’, Store ‘filename’
        nextRow = nextRow+1;
    end
end

That assumes that you are only checking for NaN values in rows at the end of each file, not intermediate NaN values (if any exist anywhere else). This will not store file names if the rows with NaN values are not consecutive. If the NaN values are only at the end of the file, it would store them, if they are also in other places in the file, it would not store that specific name. (It might also be necessary to test to be certain the NaN values fill each row completely. I have no idea if this is necessary.)

This stores them in the looop and then would write the file name cell array to a text file after the loop completes.

Star Strider 2021-3-5

As always, my pleasure!

Walter Roberson 2021-3-5

Yes, when you use use xlsread(), the first output, num, automatically has leading and trailing rows and columns of nan removed. This is because when you are talking about numeric values, text shows up as NaN (not a number, after all) and xlsread() wants to trim out header lines and trailer lines and text columns.

Also it is because if you ask excel to read a range of values and the range exceeds the size actually in the file, then excel returns nan. So xlread() cannot tell the difference between nans supplied because the file "ended" and nans that were part of the data. Indeed, unless there is a template in the file or formatting has been specifically applied to a particular range, Excel itself cannot really tell where the end of the data is. It is all ambiguous in spreadsheets: if you wrote something to row 10000 and then deleted the content, then is the spreadsheet now "really" 10000 rows, or is it "really" the size implied by the last non-empty data?

请先登录，再进行评论。

Find NaNs at the end of an Excel file

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

采纳的回答

7 个评论
显示 5更早的评论隐藏 5更早的评论

更多回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

Find NaNs at the end of an Excel file

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

采纳的回答

7 个评论 显示 5更早的评论隐藏 5更早的评论

更多回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

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

7 个评论
显示 5更早的评论隐藏 5更早的评论