DIAdem

cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

DataPlugin: xlsx timestamps after saving to TDMS

Solved!
Go to solution
Highlighted

Hi all,

 

I have gotten some very strange behaviour by DIADem, and I could even reproduce it in Versions 2014, 2015 and 2017. The Problem is as follows:

 

In an Excel-File there are timestamps in local time (CET/CEST), the cells have Date/Time format in Excel. When I load this file into the DataPortal, everything seems to be correct. The Timestamps are as they are in Excel. Then, after saving as TDMS, emptying the DataPortal and loading the saved TDMS-file, the timestamps are off.

 

The Excel file consists of multiple worksheets, each sheet has two Date columns. Begin and End.

Some timestamp-channels are corrected by summertime, while others are not. Reproduction shows, that always the same timestamp-channels are corrected.

When comparing the directly from xls loaded data with the tdms data, Diadem shows that the timestamps from tdms are one hour ahead, but when I subtract each channels the difference sometimes disappears.

 

Example for only one dataset that appeared, the sheets contain more dates:

Excel:

Sheet 1:

Begin: 08.09.2016  23:25:00

End: 10.09.2016  03:30:00

Sheet 2:

Begin: 04.04.2016  12:00:00

End: 04.04.2016  19:30:00

 

Excel loaded to DataPortal yields the same values. Saving to TDMS, loading the TDMS:

 

From Sheet 1:

Begin: 09.09.2016  00:25:00

End: 10.09.2016  04:30:00

From Sheet 2:

Begin: 04.04.2016 13:00:00.0000

End: 04.04.2016 19:30:00.0000

 

So, End of Sheet 2 does not change, while all others do.

 

Additionally, when loading TDMS, End from Sheet 2 is also changed, but when calculating the difference to the original channel or just "right click on the channel -> copy", without any other action like pasting, End from Sheet 2 changes back to the original value.

 

I hope my description is comprehensible, as the whole thing is very confusing to me.

 

Best regards,

NoName123

0 Kudos
Message 1 of 10
(1,033 Views)

Could you provide an example (xlsx file).

How do you load your xlsx file to DIAdem. (Using a plugin, script, ...)

 

0 Kudos
Message 2 of 10
(1,011 Views)

Hi Andreas,

I've attached a stripped down sample with anonymised data. The two Date-Columns "Prod.-Beginn" and "Prod.-Ende" are the ones in question.

The Excel is loaded with a custom Data-Plugin, in which just some of the columns are selected.

0 Kudos
Message 3 of 10
(1,003 Views)

Could you check if the following code does not show the behavior on your machine.
It just creates some data in DIAdem saves and reloads it.

 

option explicit
data.root.Clear

dim grp1 : set grp1 = data.Root.ChannelGroups.Add("grp1")
dim start1 : set start1 = grp1.Channels.Add("begin",DataTypeChnDate)
dim end1 : set end1 = grp1.Channels.Add("end",DataTypeChnDate)
start1.Values(1) = CreateTime(2016,9,8,23,25,0,0,0,0)
end1.Values(1) = CreateTime(2016,9,10,3,30,0,0,0,0)

dim grp2 : set grp2 = data.Root.ChannelGroups.Add("grp1")
dim start2 : set start2 = grp2.Channels.Add("begin",DataTypeChnDate)
dim end2 : set end2 = grp2.Channels.Add("end",DataTypeChnDate)
start2.Values(1) = CreateTime(2016,4,4,12,0,0,0,0,0)
end2.Values(1) = CreateTime(2016,4,4,19,30,0,0,0,0)

dim targetPath : targetPath = TmpDrv & "check_time.tdms"

DataFileSave targetPath, "TDMS"

DataFileLoad targetPath, "TDMS"
0 Kudos
Message 4 of 10
(999 Views)

I tried and the code works fine, so seems like the problem is stemming from the Excel file or the Data-Plugin? Any further ideas?

 

As a workaround, I thought of importing the date-columns as numbers and then switching them to be Date-channels after they are loaded.

 

Oh, and by the way, I did not mention it in the original post, but I did not only reproduce it in different Diadem versions, but also on different machines.

0 Kudos
Message 5 of 10
(996 Views)

I tried to reproduce but not capable to do so.

 

  • Create a XlsX plugin using the Excel wizard
  • Use the attached script

I am checking it in germany. So we have also an offset and DST.

Is there something special in your systems?

 

Option Explicit
data.Root.Clear

dim filepath : filePath = "C:\Users\GrKrantA\Downloads\produktionsmengen 2016.xlsx"
DataFileLoad filePath, "XlsToTdms"
DataFileSave TmpDrv & "XlsToTdms.tdms", "TDMS"
DataFileLoad TmpDrv & "XlsToTdms.tdms", "TDMS"

View.NewLayout()
View.Sheets("Sheet 1").Areas("Area : 1").DisplayObjType = "ChannelTable"
Dim oMyDisplayObj
Set oMyDisplayObj = View.Sheets("Sheet 1").Areas("Area : 1").DisplayObj
oMyDisplayObj.DynamicMode = "None"
oMyDisplayObj.Columns.Add "[1]/Prod.-Beginn"
oMyDisplayObj.Columns.Add "[3]/Prod.-Beginn"
oMyDisplayObj.Columns.Add "[1]/Prod.-Ende"
oMyDisplayObj.Columns.Add "[3]/Prod.-Ende"
oMyDisplayObj.Columns.Add "[2]/Prod.-Beginn"
oMyDisplayObj.Columns.Add "[4]/Prod.-Beginn"
oMyDisplayObj.Columns.Add "[2]/Prod.-Ende"
oMyDisplayObj.Columns.Add "[4]/Prod.-Ende"
WndShow "VIEW", "MAXIMIZE"
0 Kudos
Message 6 of 10
(981 Views)

Thank you for your help so far. I'm in germany, too.

The last code-snippet works as expected. So I should check my Data-Plugin Code for the flaws. I'll let you know.

 

0 Kudos
Message 7 of 10
(972 Views)

Ok, unfortunately, when using the original xls-file and a DataPlugin created with the wizzard, the same error occurs. I have also checked another file (from 2015), and the outcome is identical. As before, only a few worksheets within the xls file are faulty, but for that file these worksheets are not the same as within the first tested xls file.

 

So, instead of looking for the DataPlugin, maybe the preprocessing of the file is crucial? We perform sanity checks from Diadem in the Excel-files, like overlapping dates etc. Within these checks all date-columns are handled the same: a date and a time column are put together to the resulting datetime-column. Then the whole range (column with dates without the header) gets the numberformat = "yyyy-MM-dd hh:mm".

 

And that's where I get lost, why are some columns treated differently when loaded into Diadem  than others? At which point adds Diadem the one hour of daylight saving to the timestamp? 

 

Edit:

I created another test-file, completely from scratch. Used the Excel-Assistant to create a DataPlugin and saved the data afterwards as tdms. The error occurs in all columns I created. Hence I would argue it's not completely random. Attached are my DataPlugin and the test files. Maybe this would be helpful?

0 Kudos
Message 8 of 10
(963 Views)
Solution
Accepted by topic author NoName123
10-05-2017 03:26 AM

If I load the TDMS file it matches the content of the XLSX file.

So I am a little bit lost.

 

One idea left:

  • Someone is modifing the timezone, DST settings before saving to tdms

Does the effect also show up if you use TDM instead of TDMS.


Background: The tdms format is defined to store UTC (no DST) time. DIAdem works with local time. This means, that timestamps are converted in the moment that they are saved to file or loaded from the file. This is done by accessing the operating system time zone settings. Because Excel also works with local time stamps moving xlsx/TDMS pair to different time zone causes values to differ. The TDM formatt will not show this effect because it is storing local time like Excel.

Message 9 of 10
(928 Views)

Thank you for investigating, the TDM solved the issue for me.

 

Although it would be really interesting to know why 3 different computers, two others not set up by me, would reproduce the behaviour while yours is not. Even creating the xlsx file and saving to tdms is done on the same machine without change in DST settings. First I thought this could be an issue because I'm working in a VM and I had time synchronization issues in a VM before, but the other 2 machines are running natively in windows. With a little more time on my hand I will try to get to the bottom of this, but for now I'm satisfied with using TDM.

0 Kudos
Message 10 of 10
(920 Views)