Converting string cell array to dates

Hi,

I need to upload a large (100mb)comma separated .dat file in matlab 2008b. The first column of the file represents the date and time (yyyy-mm-dd HH:MM:SS.S). When I use the code below, the data upload fine and the first column comes out as "yyyy-mm-dd HH:MM:SS.S" in the cells. I am trying to convert this to serial dates using datenum? What I've tried so far resulted in this error message:

??? Error using ==> datenum at 174
DATENUM failed.

Error in ==> Load_and_graph_V5 at 6

Is the problem related to the " " at the begining and the end of the date?

datenum(T{1},formatIn)

    fid = fopen('TOA5_TGA.RawData_47_2016_11_21_0000.dat', 'r');

    T = textscan(fid, '%s%f%f%f%f%f%f%f%f%f%f%f%f%f', 'Delimiter',',','HeaderLines',4);

    fclose(fid);

    formatIn = 'yyyy-mm-dd HH:MM:SS.S';

    datenum(T{1},formatIn)

Any help or suggestions would be appreciated!

Thanks

[per isakson]

Try

formatIn = 'yyyy-mm-dd HH:MM:SS.FFF';

[Luc]

I just tried this and I get the following error message:

??? Error using ==> datenum at 174 DATENUM failed.

Error in ==> Load_and_graph_V5 at 6 datenum(T{1},formatIn) Caused by: Error using ==> dtstr2dtnummx Failed on converting date string to date number.

Any other idea??

[dpb]

Yeah, show us a small subset of the actual data and how it is stored instead of just describing it; including whatever it is that made you make the comment regarding quotes. Yes, if the date string does include a double-quote you'll have to include it in the formatting string or remove it; if it's simply a single quote (') surrounding it when displayed at the command line for a value, that's just a Matlab display convention for cellstr and isn't part of the string itself.

But, we can't see your terminal from here so we don't know...

[per isakson]

"Any other idea??" &nbsp

• "FFF, Millisecond in three digits" &nbsp might not be available in R2008b. Check the documentation.
• Your data string doesn't honor the format you claim.

with R2016a

>> sdn = datenum( '2016-11-25 16:15:39.4', 'yyyy-mm-dd HH:MM:SS.FFF' )
sdn =
     7.366596775393519e+05
>> datestr( sdn, 'yyyy-mm-dd HH:MM:SS.FFF' )
ans =
2016-11-25 16:15:39.400

[dpb]

"FFF, Millisecond in three digits" might not be available in R2008b."

It's in R14 which predates R2008X by quite some time...

>>  datestr(datenum( '2016-11-25 16:15:39.4', 'yyyy-mm-dd HH:MM:SS.FFF' ), 'yyyy-mm-dd HH:MM:SS.FFF') 
ans =
2016-11-25 16:15:39.400
>> version
ans =
7.1.0.246 (R14) Service Pack 3
>>

I don't have anything between R14 and R2012b, but surely can't imagine it retrogressed in between...

I think there's some other formatting problem going on here we can't see because don't have the actual file or data from the file which is your second alternative above--pretty sure that's the issue; question is just how that we can't tell w/o the actual data instead of just a description. (As so often the case, that can't see the problem means good chance OP doesn't recognize how that is so, otherwise would've fixed it).

[per isakson]

• That rules out "not available in R2008b"
• "I just tried this and I get the following error message:" &nbsp Tried what? &nbsp ":SS.S" makes me unsure.
• Another idea: Corrupt line in the middle of the file. "large (100mb)comma separated .dat file"

[dpb]

Indeed, OP is markedly short on detail; since the comment followed your suggestion regarding .FFF instead of .S, one would presume that's what he/she did, but no way to know for certain.

The latter is also quite possible; this one doesn't have the particular symptom but I've run into it with the AM/PM form where one or more instances may be missing it which causes just such a failure.

I've beefed to TMW before on the lack of help the error from datenum provides; it could at least echo the string it's trying to convert at the time of failure it would seem...

[Luc]

Alright! I'd like to upload an original .dat file but the problem is that they are too big to be attached here. I've attached a .mat file instead. I'll be happy to provide any other file if needed.

[dpb]

One pretty effective technique is to use bisection to locate the position within the file that is the culprit. Start by saving first half (roughly, doesn't have to be exact, exact) and see if that fails/succeeds. Rinse and repeat on the section that does fail and you'll shortly have a small enough section to isolate the offending line.

[Luc]

Thanks for the comments. I managed to get it to work by removing the '"' and focusing on a small set of data. The problem seems to be linked to the fact that every 10 rows, the seconds don't have decimals.

'2016-11-22 00:00:00.1'
'2016-11-22 00:00:00.2'
'2016-11-22 00:00:00.3'
'2016-11-22 00:00:00.4'
'2016-11-22 00:00:00.5'
'2016-11-22 00:00:00.6'
'2016-11-22 00:00:00.7'
'2016-11-22 00:00:00.8'
'2016-11-22 00:00:00.9'
'2016-11-22 00:00:01'

Is there a way to add decimals where they are missing?

[Walter Roberson]

NewCell = regexprep(YourCell, '(:\d\d)$', '$1.0', 'lineanchors')

[Luc]

Great! It works now. Thanks everyone for your help!

[dpb]

I'll just note that perhaps could fix the problem in the place where the file was created initially to ensure consistent format.

I've bit^h^h^h complained to TMW previously over how fragile the time conversion format parsing is--it seems to me there should be no reason for it to fail there; the value is unambiguous. It'd be a reasonable enhancement request to suggest it not fail or, if that really is difficult owing to the way the C-based i/o formatting strings function, at least echo the line that caused the problem in the error.