Preventing Matlab from truncating timestamp when importing from excel

Question

I am importing an excel file of timestamps into Matlab. It seems that whenever I have a timestamp at the turn of midnight i.e. '13/5/2015 12:00 PM', my matlab will read it as only '13/5/2015' only.

Excel samples
'13/5/2015 12:00 PM'
'13/5/2015 12:01 PM'

Imported to Matlab using [NA1, NA2, Raw] = xlsread('excel.xls');

Raw = {
'13/5/2015';
'13/5/2015 12:01 PM'}

This actually prevents me from using datenum of the same formatin. How do I go about preventing Matlab from truncating timestamp when import from excel?

rayryeng rayryeng · Accepted Answer · 2015-05-15T06:20:51

You could always add in the time yourself by finding those entries that don't end in a letter and add 12:00 AM at the end of the string. This can then be used for datenum. To do that, we can use regular expressions and search for cells that don't have any letters at the end, then for each of these cells, add 12:00 AM at the end.

Something like this:

ind = ~cellfun(@isempty, regexp(Raw, '\d*$'));
strings = Raw(ind);
padded_strings = cellfun(@(x) [x ' 12:00 AM'], strings, 'uni', 0);
Raw(ind) = padded_strings;

The first line of code is a bit obfuscated, but easy to explain. Let's look at the nested command first:

regexp(Raw, '\d*$')

regexp is MATLAB's regular expression command. Regular expressions seek to find patterns in strings. What you do here is specify a string, or a cell array of strings and the goal of regexp is to find the locations of where that pattern matched within each string. In this case, what I'm doing is finding those strings that ended in numbers. If I don't find such a pattern, then the result would be empty. Therefore, if you provide a cell array of strings, you would get another cell array as the output where each element tells you the indices of where the pattern was found.

As such, if we obtain an index for a string, that means that it ended in numbers and if it's empty, then it ended in something but numbers. I use cellfun to iterate through the regexp result and return a logical vector that determines whether each cell array came up empty or had something in it. However, I want the opposite, which is why I take the inverse (~). This output I can now use to slice into the Raw cell array and pick out those strings that ended up with numbers at the end.

That slicing is done in the second line of code. Once I get out these strings, I run another cellfun (third line) call that appends a 12:00 AM string at the end of the string and I set the uni=0 flag as the output is a cell array and no longer a simple numeric/logical array. Once I have this output cell array from the third line, I use the indices from the first line of code to slice back into the original cell array and place these padded strings inside.

Once I run the above code on your example cell array, I get this:

Raw = 

    '13/5/2015 12:00 AM'
    '13/5/2015 12:00 PM'

Preventing Matlab from truncating timestamp when importing from excel

1 Answers