Import datetime with MESZ format

20 Dic 2021
1 Risposta
Risposta accettata
Aggiornato 22 Dic 2021
7 Visualizzazioni (30 giorni)

0 voti

Hi,
I want to import an csv file with the following datetime format:
24.Sep 2021 14:09:00 MESZ
I had another csv with a different datetime format, where I used
Size_Datum_in_char = size(Datum_in_char,1);
Datum_full = [];
for i = 1:Size_Datum_in_char
T = datetime(Datum_in_char(i,1),'TimeZone','local','InputFormat','yyyy-MM-dd''T''HH:mm:ssX');
Datum_full = [Datum_full; T];
end
Where Datum_in_char is the imported matrix of the csv's first row with the datetime stored as char.
Now I tried:
T = datetime(Datum_in_char(i,1),'TimeZone','local','InputFormat','dd.MM yyyy hh:mm:ss XXXX');
but It seams, that I cannot import the "MESZ" with Xes. Any ideas, what I could do?
Thank you.

Accedi per rispondere a questa domanda.

 Risposta accettata

Cris LaPierre
Cris LaPierre il 20 Dic 2021
Modificato: Cris LaPierre il 21 Dic 2021

1 voto

I don't believe MATLAB is able to automatically recognize the timezone code. Importing might be a two step process where the dates are first read in as strings, then the codes are replaced with UTC offsets, which then allows the data to be converted to datetimes.
d='24.Sep 2021 14:09:00 MESZ';
dd = replace(d,'MESZ','+02:00');
D = datetime(dd,'InputFormat','dd.MMM yyyy HH:mm:ss xxxxx','TimeZone','UTC')
See the datetime documentation page to learn more about the Format and TimeZone properties.

6 Commenti
Mostra 4 commenti meno recenti Nascondi 4 commenti meno recenti

Tamara Szecsey
Tamara Szecsey il 21 Dic 2021
Replacing the MESZ is brilliant. Never thought of that. But it's still not working. I used Z instead of xxxxx and then the compiler says
Unable to convert '1.Okt 2021 00:00:00 +02:00' to datetime using the format 'dd.MMM yyyy HH:mm:ss Z'. If the date/time text
contains day, month, or time zone names in a language foreign to the 'en_US' locale, those might not be recognized. You can
specify a different locale using the 'Locale' parameter.
I then added 'Locale','de_DE' into datetime. But now the error message is again:
Unable to convert '24.Sep 2021 10:00:00 +02:00' to datetime using the format 'dd.MMM yyyy HH:mm:ss Z' and locale 'de_DE'.
Cris LaPierre
Cris LaPierre il 21 Dic 2021
Modificato: Cris LaPierre il 21 Dic 2021
Ran in:
Ran in:
Using Z should work as well.
d='24.Sep 2021 14:09:00 MESZ';
dd = replace(d,'MESZ','+02:00');
D = datetime(dd,'InputFormat','dd.MMM yyyy HH:mm:ss Z','TimeZone','UTC')
D = datetime
24-Sep-2021 12:09:00
Would it be possible to share your file? You can attach it using the paperclip icon.
Tamara Szecsey
Tamara Szecsey il 21 Dic 2021
Yes, this works until the for loop reaches October, which is in german format.
I attached the csv with the dates. Should I upload my code too?
Cris LaPierre
Cris LaPierre il 21 Dic 2021
Ran in:
Ran in:
There appears to be a discrepancy is what the offical/expected German abbreviations for the months are and what is being used in your csv file. Namely, the expected German abbreviation for 'Septempber' is 'Sept' instead of 'Sep' (reference).
So it reads September fine using the default (English) locale, and it reads October fine is using the de_DE locale, but it can't do both without some tweaking. See this example:
% English locale
d=["24.Sep 2021 14:09:00";"30.Okt 2021 16:33:00"];
D_eng = datetime(d,'InputFormat','dd.MMM yyyy HH:mm:ss')
D_eng = 2×1 datetime array
24-Sep-2021 14:09:00 NaT
% German locale
D_de = datetime(d,'InputFormat','dd.MMM yyyy HH:mm:ss','Locale','de_DE')
D_de = 2×1 datetime array
NaT 30-Oct-2021 16:33:00
% Change 'Sep' to 'Sept'
dd=replace(d,'Sep','Sept');
D3 = datetime(dd,'InputFormat','dd.MMM yyyy HH:mm:ss','Locale','de_DE')
D3 = 2×1 datetime array
24-Sep-2021 14:09:00 30-Oct-2021 16:33:00
With that discovery, I would probably read your file doing the following. I am assuming all the data is in German timezone.
% Each line is contained in double quotes, so define multiple delimiters
opts = detectImportOptions("TS_input.csv","Delimiter",{'"',','},'VariableNamingRule','preserve');
opts.ConsecutiveDelimitersRule = "join";
opts.LeadingDelimitersRule = "ignore";
opts.TrailingDelimitersRule = "ignore";
% read in Timestamps as strings
opts = setvartype(opts,"Timestamp","string");
% Set columns 2-4 to double
opts = setvartype(opts,2:4,"double");
% Import table
T = readtable("TS_input.csv",opts);
% Reformat Timestamp data to have no timezone, use expected abbreviations
T.Timestamp = erase(T.Timestamp," MESZ");
T.Timestamp = replace(T.Timestamp,'Sep','Sept');
% Convert Timestampt to a datetime
T.Timestamp = datetime(T.Timestamp,'InputFormat','dd.MMM yyyy HH:mm:ss','Locale','de_DE');
% Set Timezone (optional)
T.Timestamp.TimeZone = "Europe/Berlin";
% view
head(T)
ans = 8×4 table
Timestamp Trend Flags Status Value (W-hr) ____________________ ___________ ______ ____________ 24-Sep-2021 10:00:00 1 0 8.8946e+08 24-Sep-2021 10:01:00 0 0 8.8946e+08 24-Sep-2021 10:02:00 0 0 8.8946e+08 24-Sep-2021 10:03:00 0 0 8.8946e+08 24-Sep-2021 10:04:00 0 0 8.8946e+08 24-Sep-2021 10:05:00 0 0 8.8946e+08 24-Sep-2021 10:06:00 0 0 8.8946e+08 24-Sep-2021 10:07:00 0 0 8.8946e+08
tail(T)
ans = 8×4 table
Timestamp Trend Flags Status Value (W-hr) ____________________ ___________ ______ ____________ 30-Oct-2021 16:26:00 0 0 9.0412e+08 30-Oct-2021 16:27:00 0 0 9.0412e+08 30-Oct-2021 16:28:00 0 0 9.0412e+08 30-Oct-2021 16:29:00 0 0 9.0412e+08 30-Oct-2021 16:30:00 0 0 9.0412e+08 30-Oct-2021 16:31:00 0 0 9.0412e+08 30-Oct-2021 16:32:00 0 0 9.0412e+08 30-Oct-2021 16:33:00 0 0 9.0412e+08
Tamara Szecsey
Tamara Szecsey il 22 Dic 2021
Oh, that's weird, because the csv was generated from other sensors. Good to know though.
So you would recomment to import this as a table? I imported the data I need as two matrices.
This is helping me a lot, thank you.
Cris LaPierre
Cris LaPierre il 22 Dic 2021
Ultimately it depends on what you need to do, but my default approach is to create tables. If they are new to you, the most helpful page for me was How to Access Data in Tables.

Accedi per commentare.

Più risposte (0)

Tag

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by