My xlsx / csv dataset is getting filled with NaNs

I am in the process of importing a set of xlsx data values using matlab's "import data" function, and extracting out the code. However, for the first time, the data I import gets filled with NaNs. These are data values that I exported from a measurement device, and therefore should all be numeric and have no reason to be not classified as so.

The dataset file I am trying to use is attached, and the specific routine imported from matlab is below. Thanks for any help in advance.

%%Import data from spreadsheet
% Script for importing data from the following spreadsheet:
%
%    Workbook:
%    
%    Worksheet: Sheet1
%
% To extend the code for use with different selected data or a different
% spreadsheet, generate a function instead of a script.

% Auto-generated by MATLAB on 2014/05/22 00:21:19
close all;clc;clear all;
%%Import the data
[~, ~, raw] = xlsread('C:\matlab\settotal.xlsx','Sheet1','A2:AV1713');
raw(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),raw)) = {''};

%%Replace non-numeric cells with NaN
R = cellfun(@(x) ~isnumeric(x) && ~islogical(x),raw); % Find non-numeric cells
raw(R) = {NaN}; % Replace non-numeric cells

%%Create output variable
data = reshape([raw{:}],size(raw));

%%Allocate imported array to column variable names
des1e = data(:,1);
des1t = data(:,2);
des1c = data(:,3);
des2e = data(:,4);
des2t = data(:,5);
des2c = data(:,6);
des3e = data(:,7);
des3t = data(:,8);
des3c = data(:,9);
des4e = data(:,10);
des4t = data(:,11);
des4c = data(:,12);
deb1e = data(:,13);
deb1t = data(:,14);
deb1c = data(:,15);
deb2e = data(:,16);
deb2t = data(:,17);
deb2c = data(:,18);
deb3e = data(:,19);
deb3t = data(:,20);
deb3c = data(:,21);
deb4e = data(:,22);
deb4t = data(:,23);
deb4c = data(:,24);
ds1se = data(:,25);
ds1st = data(:,26);
ds1sc = data(:,27);
ds2se = data(:,28);
ds2st = data(:,29);
ds2sc = data(:,30);
ds3se = data(:,31);
ds3st = data(:,32);
ds3sc = data(:,33);
ds4se = data(:,34);
ds4st = data(:,35);
ds4sc = data(:,36);
ds5be = data(:,37);
ds5bt = data(:,38);
ds5bc = data(:,39);
ds6be = data(:,40);
ds6bt = data(:,41);
ds6bc = data(:,42);
ds7be = data(:,43);
ds7bt = data(:,44);
ds7bc = data(:,45);
ds8be = data(:,46);
ds8bt = data(:,47);
ds8bc = data(:,48);

%%Clear temporary variables
clearvars data raw R;

    end

[Geoff Hayes]

Do you mean to say that all of the data in your data set appears as NaNs when you import it, or just a subset of the data? I imported your data and did observe some NaNs, but these seemed to correspond with empty or blank cells within the first sheet of the Excel document, so the appearance of NaNs makes sense. Are you observing something different?

Answer 1

[dpb]

Starting at row 1389 you've got holes in your data -- xlsread fills those in as NaN by default. There are a lot more holes later on...

Why don't you just use xlsread directly?

[d,t]=xlsread('C:\matlab\settotal.xlsx');

will give you the numeric values in array d and the header text in t w/o any other hoopla needed. Still have the NaN for the missing locations, but that's owing to your data, not xlsread

Lines in the spreadsheet are as follows at line 1389ff (truncated after column 5--

1389  0.036120115  47.97346083  0.01559  0.035450712  75.52568539  ...
1390  0.036146386  47.9745615  0.01559                ...
...