Importing or reading a column with both text and numerical data using textscan

This might be a fun one. I want to import large quantities of data that looks like the MKE.txt file attached. It is weather data from ASOS, so some columns are text and others are numerical. Column 4 has both. If it rains a milimeter, it will read "1.00", and if there is no rain, it will read "M" for missing. I'm stuggling to set up the textscan function in a way that will allow me to analyze the numerical values and treat the Ms as Nan values or Zeros.

Here is what I have so far.

filename='MKE.txt';
delimiter = ',';
startRow = 2;
endRow = 50;
formatSpec2='%s %s %f %f %s';
fileID = fopen('MKE.txt','r');
dataArray = textscan(fileID, formatSpec2, endRow-startRow+1, 'Delimiter', delimiter, 'MultipleDelimsAsOne', true, 'HeaderLines', startRow-1, 'ReturnOnError', false);
dMKE=datenum(dataArray{2})

Since the 4th column has "M"s where textscan is expecting numbers, I get this error:

"Error using textscan

Mismatch between file and format string.

Trouble reading 'Numeric' field from file (row number 1, field number 4) ==> M,M\n"

I have also tried changing the %f to %s for the 4th column and using the last line of code to convert whatever was imported to the useable data I'm looking for.

formatSpec2='%s %s %f %s %s';
fileID = fopen('MKE.txt','r');
dataArray = textscan(fileID, formatSpec2, endRow-startRow+1, 'Delimiter', delimiter, 'MultipleDelimsAsOne', true, 'HeaderLines', startRow-1, 'ReturnOnError', false);
dMKE=datenum(dataArray{2})

dataArray{4}(isstring(dataArray{4})==1)=NaN;

This reports the error:

"Conversion to cell from double is not possible."

Thank you for your help and advice.

Answer 1

[dpb]

Not too much grief... :)

t=readtable('MIKE.txt');                                   % read raw data
t.p01m=cellfun(@str2double,strrep(t.p01m,'M','0'));        % convert to 0; use 'NaN' if is really missing
t.station=categorical(t.station);                          % Station is categorical variable by rights

You'll have to choose how to handle the multiple conditions; I just left as the strings...

[Kassie Paul]

This worked! Thank you so much!