Find NaNs at the end of an Excel file
3 views (last 30 days)
Show older comments
James Ryan
on 5 Mar 2021
Commented: Walter Roberson
on 5 Mar 2021
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 Comments
Accepted Answer
Star Strider
on 5 Mar 2021
7 Comments
Walter Roberson
on 5 Mar 2021
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?
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!