Find NaNs at the end of an Excel file
조회 수: 4 (최근 30일)
이전 댓글 표시
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
채택된 답변
Star Strider
2021년 3월 5일
댓글 수: 7
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?
추가 답변 (0개)
참고 항목
카테고리
Help Center 및 File Exchange에서 Spreadsheets에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!