readtable and readtimetable can't load timezone properly?

I have a CSV generated from ThingSpeak, which contains a datetime containing a time zone identifier (CEST in my case). It looks like this:

created_at,entry_id,field1,field2,field3,latitude,longitude,elevation,status
2020-06-06 18:36:17 CEST,1,434,2.12121212121,30.3487192708,,,,
2020-06-06 18:36:33 CEST,2,433,2.11632453568,30.2823285934,,,,
2020-06-06 18:36:48 CEST,3,436,2.13098729228,30.4815006257,,,,
2020-06-06 18:37:03 CEST,4,433,2.11632453568,30.2823285934,,,,

Now, when I edit the file and remove " CEST" from all rows, readtimetable imports the file properly, with no issues. But when I leave it as is, readtable imports the field as text, while readtimetable refuses altogether.

I tried to specify the format manually, as so:

opts.VariableNames = ["created_at", "Var2", "field1", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9"];
opts.SelectedVariableNames = ["created_at", "field1"];
opts.VariableTypes = ["datetime", "string", "double", "string", "string", "string", "string", "string", "string"];
opts = setvaropts(opts, "created_at", "InputFormat", "dd-MM-yyyy HH:mm:ss z", "TimeZone", 'Europe/Warsaw');

With this, readtimetable works but... all fields are turned into NaT.

Now, this is some weird behavior, because using the datetime function with one of those fields copied works flawlessly:

>> date = datetime("2020-06-06 18:36:17 CEST","InputFormat","yyyy-MM-dd HH:mm:ss z","TimeZone","Europe/Warsaw")

date = 

  datetime

   06-Jun-2020 18:36:17

Matlab's importer also can't handle it, when I use the CSV file as the input it wrongly detects the delimiter, once that's corrected and field type set to datetime, any way I tried configuring the input format just fails and results in NaTs. I even attempted to set the format to "yyyy-MM-dd HH:mm:ss 'CEST'", so the time zone gets treated as a string, but it still fails to recognize it as a correct datetime.

Using Matlab R2020a

[dpb]

Did you try detectImportOptions first, then readtable?

I've found often whatever magic it plays when it sets the import options object first, even if you modify it some, it just works when there doesn't seem to be an obvious reason as to why/what compared to without.

Can't test R2020 here...

Answer 1

[dpb]

What worked here for R2019b

>> opt=detectImportOptions('thing.csv','delimiter',',')
opt = 
  DelimitedTextImportOptions with properties:
   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
                EmptyLineRule: 'skip'
                     Encoding: 'windows-1252'
   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'
   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'created_at', 'entry_id', 'field1' ... and 6 more}
                VariableTypes: {'char', 'double', 'double' ... and 6 more}
        SelectedVariableNames: {'created_at', 'entry_id', 'field1' ... and 6 more}
              VariableOptions: Show all 9 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts
        PreserveVariableNames: false
   Location Properties:
                    DataLines: [2.00 Inf]
            VariableNamesLine: 1.00
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview
>> thing=readtable('thing.csv',opt);
>> thing.created_at=datetime(thing.created_at,"InputFormat","yyyy-MM-dd HH:mm:ss z", ...
                                    "TimeZone","Europe/Warsaw",'Locale','pl_PL')
thing =
  4×9 table
         created_at         entry_id    field1    field2    field3     latitude     longitude     elevation       status  
    ____________________    ________    ______    ______    ______    __________    __________    __________    __________
    06-Jun-2020 18:36:17      1.00      434.00     2.12     30.35     {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
    06-Jun-2020 18:36:33      2.00      433.00     2.12     30.28     {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
    06-Jun-2020 18:36:48      3.00      436.00     2.13     30.48     {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
    06-Jun-2020 18:37:03      4.00      433.00     2.12     30.28     {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
>> 

The conversion via datetime that you demonstrated for the single line fails on R2019b w/o the ISO-639 local code.

Possibly using the locale and readtimetable might work.

The HINT in detectImportOptions to not use the dot notation to set parameters in a returned import object is key here -- simply building an object with the content of .delimiter as ',' doesn't work -- it takes parsing the file again with the delimiter identified to get stuff right. The date string with embedded blanks not delimited by "" is the big problem. I got around it above by passing the 'delimiter' named parameter when creating the import object.

[Mars Rap]

Well, even with the delimiter specified, it still was picked up as a text string instead of a date, only once the table (not timetable) is created the string is then converted via the datetime function. Honestly, I can use that for my purposes, but I still find it weird that Matlab doesn't pick it up on its own, especially considering that the CSV is generated by ThingSpeak, which is from Mathworks.

Also, on a side note, Locale can't be specified for readtimetable options:

>> opts = setvaropts(opts, "created_at", "InputFormat", "yyyy-MM-dd HH:mm:ss z","TimeZone","Europe/Warsaw",'Locale','pl_PL');
Error using matlab.io.ImportOptions/setvaropts (line 431)
Unknown Parameter 'Locale'.

[dpb]

" even with the delimiter specified, it still was picked up as a text string instead of a date, ..."

Oh, no argument there; it's a workaround solution worth at least a quality of implementation report if not outright bug report (since can't test R2020 here at moment, can't eliminate there being something else that could be done for a flat declaration).

"Locale can't be specified for readtimetable options:"

It's a 'Name,Value' pair in the input as 'DateLocale','plPL' to readtimetable

Internally, w/ setvaropts it is 'DatetimeLocale' -- don't you just love how consistently inconsistent TMW is in ensuring property names are not unique and consistent? :(

I just discovered that apparently there is the facility for an import options object to be a composite object of differing types -- but no real indication of how one were to build such. It showed up in one of the examples but now I can't seem to find it again...

As also somewhat frustrating, the examples tend to show simple things in great detail; the ones that are more complex have no discussion re: the more esoteric issues and so can't ever find them again except by happenstance. [Poke, poke, ... Ah! There it is...]

The example of Create Timetable from File Using Import Options under readtimetable show that the property opt.VariableOptions is

ans = 
  1x6 heterogeneous VariableImportOptions (TextVariableImportOptions, DatetimeVariableImportOptions, NumericVariableImportOptions) array with properties:

when discovered a date that recognized.

I didn't have time to play around that much more but I'm guessing if one follows the bread crumbs and resets the variable type for the first column to a datetime with the locale that one can eventually beat the import option into submission.

I agree this file would be an excellent submittal to TMW for enhancement request for them to use as a development test case and in their test suite.

Sometimes it's more expedient to just get the data in and then do the necessary cleanup like the conversion here than it is to figure out the inner machinations.

[Mars Rap]

Well, DatetimeLocale it was. With following code:

opts = delimitedTextImportOptions("NumVariables", 9);
opts.DataLines = [1, Inf];
opts.Delimiter = ",";
opts.VariableNames = ["created_at", "Var2", "field1", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9"];
opts.SelectedVariableNames = ["created_at", "field1"];
opts.VariableTypes = ["string", "string", "double", "string", "string", "string", "string", "string", "string"];
opts = setvaropts(opts, "created_at", "InputFormat", "yyyy-MM-dd HH:mm:ss z","TimeZone","Europe/Warsaw","DatetimeLocale","pl_PL");

TT = readtimetable("feeds.csv",opts);

It successfully recognized and imported the datetimes. I find it weird that in default locale (assuming enUS) it refuses to do it, considering datetime function by itself does. Not to mention that the Import window has no way to set locale or anything. Well, at least with this (which is and also isn't a workaround, somehow) it's possible to import it.

Just, as I mentioned in my previous reply, it's weird that ThingSpeak generates a CSV that can't be imported into Matlab without having to mess around with it

[Steven Lord]

I just discovered that apparently there is the facility for an import options object to be a composite object of differing types -- but no real indication of how one were to build such. It showed up in one of the examples but now I can't seem to find it again...

That's a heterogeneous array. If all classes in your class hierarchy share a common base class you can make your base class subclass matlab.mixin.Heterogeneous and then combine them in an array. As an example you could have Triangle and Square both inherit from Shape. Then:

T = Triangle;
S = Square;
P = [T, S] % P is a Shape array even if Shape is Abstract
Q = P(1) % Q is a Triangle array

[dpb]

I don't know what ThingSpeak is altho I hear it mentioned a lot...how did it generate a Warsaw, PO, date locale would be what I would wonder if you were expecting en_US to work?

Also, as documented, not using the detectImportOptions function to build the base import options object is the hard way 'round in my experience--at least let it find and populate what it can(*) and only fixup the (hopefully few) items needed.

Agreed, it's a pain when you don't know a priori that that isn't going to work reliably and then have to go figure out why.

I couldn't get datetime to convert your text string w/o the Locale explicitly set w/ R2019b--if it works reliably w/ R2020 that appears to be a change.

(*) Also at least w/ R2019b, setting the delimiter ',' without rescan of the file didn't fix the import issues much--it still broke the input cellstr at the end of the time field. Mayhaps that behavior has also changed in R2020.

[dpb]

Looks like there should be a lot more discussion of this "feature" in the import options documentation.

The machinations required to manipulate these are mindboggingly complex to get through and as here, "one never knows" just what it is that is the hangup oft times...

I know parsing a general-form text file has an infinite number of possible permutations, but the difficulties in the user interface are frustrating for me at least, and am sure a lot of newbies just give up far sooner than I as being incomprehensible.

[Mars Rap]

I don't know what ThingSpeak is altho I hear it mentioned a lot...how did it generate a Warsaw, PO, date locale would be what I would wonder if you were expecting en_US to work?

ThingSpeak is Mathworks' IoT platform. As for the timezone, at the moment of exporting data to CSV you can select the timezone. I tried using other timezones (american ones, UTC, and so on) but it kept failing with all of them.

[dpb]

OK...'spearminting, I see! :)

One thing is that by all rights the time string should be enclosed in "" given the embedded spaces; that's the proper form for a CSV file. Whether that would make the import go any better or not I don't know but I'd call it a quality of implementation detail in ThingSpeak worth noting.

I had to go search for an ISO table to find the shorthand for Warsaw being PL and not PO, so it caught my attention as being unusual.

I grant the complexity of the problem TMW is trying to solve and I don't really have any solutions to offer but the toolset is pretty dense to wade through even for experienced user who's used to reading manuals from the days of punch cards for complex nuclear design code input decks that could consist of 3,000 cards or more. The newer that expect everything to be sell-explanatory or at most a glance at the prompt line cues are in over their heads pretty quickly.

It does seem as though the input diagnostic tool ought to be able to decipher the time field and self-generate the import object at worst when called. Hence why I suggested it would be a good test case for an enhancement request submittal.