Datetime format using tabularTextDatastore

I am importing a large csv file into a data table and I get the following warning:

Warning: Successfully read the date/time text using the format 'MM/dd/uuuu', but their format is ambiguous and could also be 'dd/MM/uuuu'. Specify a format character vector to avoid ambiguity.

Is there a way to set a some global or pass through another variable to get it to stop setting the warning. ds = tabularTextDatastore(fileName,'DatetimeType','datetime','ReadVariableNames',1,'NumHeaderLines',0);

Looking in the tabularTextDatastore help there was no other variable to pass into the function that I could figure out.

[Peter Perkins]

I think dpb has it right (as always). It's worth mentioning that if any of the timestamps had been from a day-of-month beyond the 12th, you would not see this warning, because the ambiguity would be resolved.

[dpb]

Well, that's heartwarming Peter, but as much as I'd like to claim infallibility, it really isn't so... :)

[Nicolas Marx]

Hi, same issue here, I'd like to clearly state the format to avoid ambiguity. I have dates with dd>12 and still get this alert

[dpb]

Possibly worth submitting the dataset to TMW support as a "quality of implementation" issue; if the data are interpreted correctly it certainly isn't a bug but if the warning is generated on case it shouldn't be, one would presume TMW would like to know.

However, the doc for datastore object properties states for 'TextscanFormats' that if specific formats aren't provided, " then datastore determines the format of the data fields by scanning text from the first nonheader line in the first file of the datastore." Consequently, I'd guess the warning is based on that scan and not a global revamping of the message after the conversion is complete; the warning is prepared assuming conversion will succeed; then if it fails the error. But, that's a guess, I've not tried to read code nor even tried any empirical testing to try to deduce the logic.

As for specifying the format, just use

ds.TextscanFormats = {'%{MM/dd/uuuu}', ...};

for the fields for all variables. You can first display the existing formats and then update those want to change.

If don't read every variable in the file/datastore, there's the 'SelectedFormats' property that is one format string for each selected variable as alternate way to specify.

[Matthew Gold]

Thanks for the help. I remember trying with what you suggested but when i went back to the code this morning it wasn't there. I think i am just living with the warning. I am the only one using this code at this point so it doesn't have to be pretty.

[dpb]

Attach a (smallish) example of the data and I'll see what happens here. I think for sure the data set should include the first data lines of your actual case.

Answer 1

[dpb]

Looks like you set the 'SelectedFormats' property to match the variables.

TabularTextDatastore Properties
...  
SelectedFormats — Formats of selected variables
cell array of character vectors
Formats of the selected variables to read, specified as a cell array of character 
vectors, where each character vector contains one conversion specifier. The variables 
to read are indicated by the SelectedVariableNames property. The number of character 
vectors in SelectedFormats must match the number of variables to read.

You can use the same conversion specifiers that the textscan function accepts, 
including specifiers that skip literal text. However, you cannot use a conversion 
specifier that skips a field. That is, the conversion specifier cannot include 
an asterisk character (*).