MATLAB: loading data with multiple types in single column

Question

I have some data from a tide gauge I have it at the moment as a .csv file. I would like to load this data into MATLAB as I need to edit it. There are 2 columns that I am interested in, the first is a date & time column in the format [dd/mm/yyyy hh:mm] and the second is a column for tidal elevation. The tidal elevation data are primarily numbers to 3 d.p. however some of the data have letters which are used as flags. I can't use csvread because the date & time format so I changing it to a number in excel (I would prefer to keep it in date time format) but I then couldn't use csvread because it didn't like the letter flags. I tried using readtable which worked (for dates as numbers) however my tidal elevation data is stuck as in a cell as cell2mat doesn't work because I read the elevation data in in string format because of the letters.

I would basically like to know is there an easier way to get the data loaded into MATLAB as what I am doing is a real mess at the moment.

Sample Data:

28/01/1994 22:15    3.312
28/01/1994 22:30    3.057
28/01/1994 22:45    2.793
28/01/1994 23:00    2.541T
28/01/1994 23:15    2.303T
28/01/1994 23:30    2.083
28/01/1994 23:45    1.882

What I've tried:

filename = 'C:\User\Documents\Tide_Data\Fish_all.csv';
fileID = fopen(filename);
data = textread(filename,'%{dd/MM/yyyy HH:mm}D %s');

Badly formed format string, so I changed the date to a number in excel.

data = csvread(filename);

Can't read the letter T so outputs an error.

I had more code which got further before I reached a dead end but I can't reproduce it

You might want to provide some sample data and the code you already have, otherwise we're just guessing randomly at a solution. Just a thought... — excaza
Sorry I posted this at 2:30am tired and frustrated, I'll edit the OP now. — deiniol

Lati Lati · Accepted Answer · 2015-07-01T10:56:22

I would suggest to read the file using textscan and then convert the date & time string to datenum and convert the last column to double if the letter exist or not.

C = textscan(fileID,'%s %s %s');

% allocate
result = zeros(7,2);

for ii = 1:7   
   % current date string
   dateX = [C{1,1}{ii,1} C{1,2}{ii,1}];
   % current number
   numStr = C{1,3}{ii,1};

   if sum(numStr == 'T') > 0
       % remove char
       numStr = regexprep(numStr,'T','');
   end

   % collect
   result(ii,:) = [datenum(dateX, 'dd/mm/yyyyHH:MM'), str2double(numStr)];
end

You can then convert date numbers to any string format using datestr

MATLAB: loading data with multiple types in single column

1 Answers