readtable with datetime, format problem

29 visualizzazioni (ultimi 30 giorni)
I'm reading in an excel file that contains the date/time stamp of a recording in one column. The format of the date/time in the original file is day/month/year hour:min:sec PM. The date is sometimes correctly read, but in instances when the day/month is arbitrary (day is <=12), the month and day is swapped
Example:
Recording was made on December 6, 2022 (saved in excel sheet as 6/12/2022 7:56:39 PM)
I run these lines of code:
%%
opts = detectImportOptions(filepath,'Sheet','NRT Peaks');
opts = setvaropts(opts,"RecordingDate_Time",'InputFormat','d/MM/uuuu h:mm:ss aa','DatetimeLocale','de_BE','DatetimeFormat','d/MM/uuuu h:mm:ss aa');
nrtPeaks = readtable(filepath,opts,'Sheet','NRT Peaks');
%%
nrtPeaks.RecordingDate_Time(1) = 12-Jun-2022 19:56:39
How do I fix this?
The code runs correctly in some instances but not others. I played around with readcell and had the same issue.
  14 Commenti
Rachel Scheperle
Rachel Scheperle il 22 Feb 2024
Thanks for all of your work on this. I'm convinced at this point that the problem is how the excel sheet is being written when the raw data are exported. I know that the file in question was recorded on Dec 6, 2022, so the excel file was written incorrectly, which is a problem upstream of me. Thanks for helping identify/confirm the root of the problem.
Cris LaPierre
Cris LaPierre il 22 Feb 2024
@Stephen23, I was trying to convince myself it wasn't due to different regional settings (see here). However, I've since convinced myself that, once it gets converted to a datenum, it doesn't change, even if opened on a computer with different regional settings. Just had to think through it.

Accedi per commentare.

Risposta accettata

Cris LaPierre
Cris LaPierre il 22 Feb 2024
I think now that the issue is clearer, I'll propose another solution.
The challenge now is that some of your files save the RecordingDate_Time as text (if the day is >12), while others store it as a datenum (if the day is <=12). That means there isn't a single import option you can use that will work with all your files.
Since you are in a time crunch to start your analysis, I'd propose a new approach. Load your table letting MATLAB decide how to read in that variable, and then convert that column to the correct datetime based on the datatype. Here, I wrote a function called formatTbl for that purpose.
I have used a month format so that there is no confusion between day and month. Feel free to update it. This is not extensively tested. I leave it to you to validate the results.
opts = detectImportOptions("P50-DE-test-2w...-IPG7-e3.xlsx","Sheet","NRT Peaks","TextType","string");
T1 = readtable("P50-DE-test-2w...-IPG7-e3.xlsx",opts);
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T1 = formatTbl(T1)
T1 = 20×11 table
Status NRTNr RecordingDate_Time NPeakLatency__s_ NPeakValue__V_ PPeakLatency__s_ PPeakValue__V_ FrameType FIRFilterCutOffFrequency_Hz_ HighResolutionCorrection N1_P1Amplitude__V_ _________ _____ _____________________ ________________ ______________ ________________ ______________ _________ ____________________________ ________________________ __________________ <missing> 1 6/Dec/2022 7:56:39 PM 325.4 -147.54 813.4 -23.224 "ECAP" "Off" "Off" 124.31 <missing> 2 6/Dec/2022 7:56:54 PM 374.2 -147.2 715.8 -38.251 "ECAP" "Off" "Off" 108.94 <missing> 3 6/Dec/2022 7:57:10 PM 374.2 -148.56 715.8 -37.226 "ECAP" "Off" "Off" 111.33 <missing> 4 6/Dec/2022 7:57:25 PM 374.2 -143.1 715.8 -39.275 "ECAP" "Off" "Off" 103.82 <missing> 5 6/Dec/2022 7:57:40 PM 374.2 -134.56 764.6 -36.885 "ECAP" "Off" "Off" 97.67 <missing> 6 6/Dec/2022 7:58:02 PM 325.4 -132.17 813.4 -35.177 "ECAP" "Off" "Off" 96.99 <missing> 7 6/Dec/2022 7:51:49 PM 374.2 -106.56 667 -39.617 "ECAP" "Off" "Off" 66.94 <missing> 8 6/Dec/2022 7:51:33 PM 374.2 -90.163 715.8 -34.494 "ECAP" "Off" "Off" 55.66 <missing> 9 6/Dec/2022 7:51:18 PM 374.2 -85.382 715.8 -30.054 "ECAP" "Off" "Off" 55.32 <missing> 10 6/Dec/2022 7:51:03 PM 374.2 -78.551 667 -31.42 "ECAP" "Off" "Off" 47.13 <missing> 11 6/Dec/2022 7:50:48 PM 374.2 -70.696 715.8 -31.079 "ECAP" "Off" "Off" 39.61 <missing> 12 6/Dec/2022 7:52:04 PM 374.2 -29.371 667 -15.368 "ECAP" "Off" "Off" 14 <missing> 13 6/Dec/2022 7:53:33 PM 374.2 -21.516 715.8 -8.196 "ECAP" "Off" "Off" 13.31 <missing> 14 6/Dec/2022 7:55:47 PM 374.2 -21.174 618.2 -14.002 "ECAP" "Off" "Off" 7.17 <missing> 15 6/Dec/2022 7:53:48 PM 325.4 -17.418 715.8 -6.147 "ECAP" "Off" "Off" 11.27 <missing> 16 6/Dec/2022 7:54:41 PM 325.4 -16.734 618.2 -8.538 "ECAP" "Off" "Off" 8.19
opts = detectImportOptions("P50-DE-test-6m...-IPG7-e3.xlsx","Sheet","NRT Peaks","TextType","string");
T2 = readtable("P50-DE-test-6m...-IPG7-e3.xlsx",opts);
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T2 = formatTbl(T2)
T2 = 28×11 table
Status NRTNr RecordingDate_Time NPeakLatency__s_ NPeakValue__V_ PPeakLatency__s_ PPeakValue__V_ FrameType FIRFilterCutOffFrequency_Hz_ HighResolutionCorrection N1_P1Amplitude__V_ _________ _____ ______________________ ________________ ______________ ________________ ______________ _________ ____________________________ ________________________ __________________ <missing> 1 23/May/2023 7:32:03 PM 394.2 -79.234 833.4 39.275 "ECAP" "Off" "Off" 118.5 <missing> 2 23/May/2023 7:39:01 PM 394.2 -64.207 882.2 53.278 "ECAP" "Off" "Off" 117.48 <missing> 3 23/May/2023 7:32:19 PM 394.2 -72.745 882.2 44.057 "ECAP" "Off" "Off" 116.8 <missing> 4 23/May/2023 7:32:34 PM 394.2 -80.942 931 34.153 "ECAP" "Off" "Off" 115.09 <missing> 5 23/May/2023 7:32:49 PM 345.4 -81.967 833.4 33.811 "ECAP" "Off" "Off" 115.77 <missing> 6 23/May/2023 7:33:04 PM 345.4 -78.21 882.2 43.374 "ECAP" "Off" "Off" 121.58 <missing> 7 23/May/2023 7:39:51 PM 345.4 -77.868 931 31.762 "ECAP" "Off" "Off" 109.62 <missing> 8 23/May/2023 7:38:30 PM 345.4 -78.21 931 29.03 "ECAP" "Off" "Off" 107.23 <missing> 9 23/May/2023 8:10:37 PM 345.4 -96.311 882.2 18.101 "ECAP" "Off" "Off" 114.41 <missing> 10 23/May/2023 7:35:42 PM 345.4 -97.677 833.4 18.784 "ECAP" "Off" "Off" 116.46 <missing> 11 23/May/2023 7:39:24 PM 345.4 -96.311 882.2 18.784 "ECAP" "Off" "Off" 115.09 <missing> 12 23/May/2023 8:10:52 PM 345.4 -97.677 833.4 4.781 "ECAP" "Off" "Off" 102.45 <missing> 13 23/May/2023 7:33:20 PM 345.4 -96.653 833.4 1.707 "ECAP" "Off" "Off" 98.36 <missing> 14 23/May/2023 8:11:07 PM 345.4 -88.456 735.8 -3.756 "ECAP" "Off" "Off" 84.69 <missing> 15 23/May/2023 7:35:01 PM 345.4 -82.991 784.6 -5.464 "ECAP" "Off" "Off" 77.52 <missing> 16 23/May/2023 7:33:35 PM 345.4 -56.352 735.8 -8.538 "ECAP" "Off" "Off" 47.81
function Tout = formatTbl(Tin)
if strcmp(class(Tin.RecordingDate_Time),"string")
Tin.RecordingDate_Time = datetime(Tin.RecordingDate_Time,"InputFormat","d/M/uuuu H:mm:s","Format","d/MMM/uuuu h:mm:ss aa");
elseif strcmp(class(Tin.RecordingDate_Time),"datetime")
[y,M,d] = ymd(Tin.RecordingDate_Time);
[h,m,s] = hms(Tin.RecordingDate_Time);
Tin.RecordingDate_Time = datetime(y,d,M,h,m,s,"Format","d/MMM/uuuu h:mm:ss aa");
end
Tout = Tin;
end
  1 Commento
Rachel Scheperle
Rachel Scheperle il 22 Feb 2024
This is terrific. Thanks so much. I modified the code slightly because of how I'm looping across files (and I only need year, month, and day) but so far the "if" "elseif" logic and flip-flopping the "d" and "M" inputs for datetime have addressed the issue in the small set of test files.

Accedi per commentare.

Più risposte (1)

Cris LaPierre
Cris LaPierre il 22 Feb 2024
Modificato: Cris LaPierre il 22 Feb 2024
The 'InputFormat' argument is only useful for date strings. Excel applies a display format to dates and times, so what you see is not actually how the data gets imported into MATLAB. In Excel, change the format of your date cells to General to view how the data is actually stored. That date and time should appear as 44901,8310069444
You might want to try setting the Datetime Type to exceldatenum.
opts = detectImportOptions(filepath,'Sheet','NRT Peaks');
opts = setvaropts(opts,"RecordingDate_Time","DatetimeType","exceldatenum",'DatetimeLocale','de_BE','DatetimeFormat','d/MM/uuuu h:mm:ss aa');
nrtPeaks = readtable(filepath,opts,'Sheet','NRT Peaks');
Note that the DatetimeLocale value determines how input text values are interpreted. Since the date is likely imported as a datenum, this setting is not doing anything and can probably be removed without affecting your output.
  5 Commenti
Cris LaPierre
Cris LaPierre il 22 Feb 2024
Modificato: Cris LaPierre il 22 Feb 2024
If I modify the regional settings of the dates on that sheet so that dates are day/month, then it imports correctly. For me, that also meant re-entering the dates, since the Excel datenum was already recorded as Jun 12, 2022.
I modified the display format so that it is obvious the month and day are read in correctly.
opts = detectImportOptions("P50-DE-test-2w-AGF-IPG7-e3_edited.xlsx",'Sheet','NRT Peaks');
opts = setvaropts(opts,"RecordingDate_Time","DatetimeFormat","d/MMM/uuuu h:mm:ss aa");
T = readtable("P50-DE-test-2w-AGF-IPG7-e3_edited.xlsx",opts)
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T = 20×11 table
Status NRTNr RecordingDate_Time NPeakLatency__s_ NPeakValue__V_ PPeakLatency__s_ PPeakValue__V_ FrameType FIRFilterCutOffFrequency_Hz_ HighResolutionCorrection N1_P1Amplitude__V_ __________ _____ _____________________ ________________ ______________ ________________ ______________ _________ ____________________________ ________________________ __________________ {0×0 char} 1 6/Dec/2022 7:56:38 PM 325.4 -147.54 813.4 -23.224 {'ECAP'} {'Off'} {'Off'} 124.31 {0×0 char} 2 6/Dec/2022 7:56:54 PM 374.2 -147.2 715.8 -38.251 {'ECAP'} {'Off'} {'Off'} 108.94 {0×0 char} 3 6/Dec/2022 7:57:10 PM 374.2 -148.56 715.8 -37.226 {'ECAP'} {'Off'} {'Off'} 111.33 {0×0 char} 4 6/Dec/2022 7:57:24 PM 374.2 -143.1 715.8 -39.275 {'ECAP'} {'Off'} {'Off'} 103.82 {0×0 char} 5 6/Dec/2022 7:57:40 PM 374.2 -134.56 764.6 -36.885 {'ECAP'} {'Off'} {'Off'} 97.67 {0×0 char} 6 6/Dec/2022 7:58:02 PM 325.4 -132.17 813.4 -35.177 {'ECAP'} {'Off'} {'Off'} 96.99 {0×0 char} 7 6/Dec/2022 7:51:49 PM 374.2 -106.56 667 -39.617 {'ECAP'} {'Off'} {'Off'} 66.94 {0×0 char} 8 6/Dec/2022 7:51:33 PM 374.2 -90.163 715.8 -34.494 {'ECAP'} {'Off'} {'Off'} 55.66 {0×0 char} 9 6/Dec/2022 7:51:18 PM 374.2 -85.382 715.8 -30.054 {'ECAP'} {'Off'} {'Off'} 55.32 {0×0 char} 10 6/Dec/2022 7:51:02 PM 374.2 -78.551 667 -31.42 {'ECAP'} {'Off'} {'Off'} 47.13 {0×0 char} 11 6/Dec/2022 7:50:48 PM 374.2 -70.696 715.8 -31.079 {'ECAP'} {'Off'} {'Off'} 39.61 {0×0 char} 12 6/Dec/2022 7:52:04 PM 374.2 -29.371 667 -15.368 {'ECAP'} {'Off'} {'Off'} 14 {0×0 char} 13 6/Dec/2022 7:53:33 PM 374.2 -21.516 715.8 -8.196 {'ECAP'} {'Off'} {'Off'} 13.31 {0×0 char} 14 6/Dec/2022 7:55:47 PM 374.2 -21.174 618.2 -14.002 {'ECAP'} {'Off'} {'Off'} 7.17 {0×0 char} 15 6/Dec/2022 7:53:47 PM 325.4 -17.418 715.8 -6.147 {'ECAP'} {'Off'} {'Off'} 11.27 {0×0 char} 16 6/Dec/2022 7:54:41 PM 325.4 -16.734 618.2 -8.538 {'ECAP'} {'Off'} {'Off'} 8.19
Rachel Scheperle
Rachel Scheperle il 22 Feb 2024
I appreciate all of your efforts and glad to know what is at the root of the problem. I will be reaching out to the software engineers of to address how the excel sheet is being written.
Since my part of the process is down-stream of when the excel sheet gets created, I was hoping to find a work around to deal with all of the files we currently have that were exported over the past year.

Accedi per commentare.

Prodotti


Release

R2022b

Community Treasure Hunt

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

Start Hunting!

Translated by