How NOT to read blank Excel cells as NaN when using xlsread?

I have been given several large data files in Excel. The first column is "time" which is at the highest sample rate of all the following parameters. Each next column is a measured parameter that may or may not be at the same sample rate of "Time". If the parameter is a a slower sample rate the cells between the data will be blank. So in column format the data can be blank, blank, blank, data, blank, blank, blank, blank, data, blank, blank, blank, blank, data, blank... Using xlsread the blanks are brought into Matlab as NaN e.g. NaN, NaN, NaN, data, NaN, NaN, NaN, NaN, data, NaN, NaN, NaN, NaN, data, NaN... I then take that column of data and NaNs and run it through a loop and using isnan pull out the data values and then write them using xlswrite to a new compressed Excel file, which is used by another Matlab file. For ~34 such original files of sizes up to 500Mb this can take up to 15 hours to run. And most times that I've run it, it never made it through all of the files. I've had Matlab stop at file 33 with an error message and somtimes it stops without an error message but with a blinking | in the Command Window but not doing anything at all. Is there some way to not read in the blank cells as NaN and only read in the data? Can someone explain why the program just stops? Note of Caution: I'm an old timer who writes Matlab code like I wrote Fortan IV back in 1979. I don't know all the nifty shortcuts.

I found readtable but that is in R2018b and we still have R2017B. This has options to omit blanks. Is rmmissing in R2017b?

I tried using readtable and opts.MissingRule = 'omitrow';uint32 and the opts was not recognized.

The raw data file is huge and contains aircraft state, vibration, structural, handling quality, etc. data. I need to pull out the columns for 10 aircraft state parameters and 30 vibration parameters. I calculate the mean, min, and max of the aircraft state parameters. I calculate mean, min, and max of several rotor harmonic amplitudes for the vibration parameters. These are then output to a new Excel file. Blanks cells and interpolated cells will screw up the FFT calculations.

I used new_data=rmmissing(data) and the code is running like a champ.

[Guillaume]

readtable was introduced in R2013b. I don't think there are many differences between the 2017b and 1018b version of readtable.

If you want matlab to retain the tabular format of the data, then the blanks need to be imported as something. The fact that the something is NaN should not affect the speed of what you do next, so if it is the case, perhaps your code need improving and you ought to give more details.

[Walter Roberson]

R2018a added duration processing in readtable() if I recall correctly.

Answer 1

[Surbhi Pillai]

As evident from your explanation, the sample time is much higher compared to rate at which the parameters are read in the Excel file. Due to this there exist missing values amongst the recorded data. While reading the Excel data using 'xlsread' function, the missing values are represented as NaN in MATLAB.

One simple solution to the problem could be you can use 'rmmissing' function available in MATLAB which removes the missing values from a table or an array.

Say for example:

data=xlsread('data.xlsx');
new_data=rmmissing(data);        %new_data is obtained after removing rows with missing values

The documentation link for 'rmmissing' function is shared below:

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

On the contrary, you can also interpolate or fill the missing values using 'fillmissing' function available in MATLAB. The documentation link for the same is given below:

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

I hope this helps!

[Walter Roberson]

However perhaps instead the appropriate approach might be to use retime()?

[Bereketab Gulai]

retime() is not appropriate for tables...

Try using more variables with rmmissing().

rmmissing(t, 1, 'MinNumMissing',50)

[Walter Roberson]

Well you would table2timetable() and retime() that.