date time

[Brad_Turpin]

Actually, what you posted in your other thread is the source code for your DataPlugin.  I'm asking for data files.

[Brad_Turpin]

Hi All,

I got the data set, and here's the source code of the DataPlugin that puts those 3 DateTime channels together.  It uses the Channel.Formatter object (for the milliseconds channel) which released with DIAdem 2012, so this won't work in older DIAdem versions.

OPTION EXPLICIT

Sub ReadStore(File)
  Dim j, jMax, Block, Group, TimeChannel, Channel, ChanNames
  File.Formatter.Delimiters = ","
  File.Formatter.LineFeeds = vbCRLF
  File.Formatter.TimeFormat = "YYYY-MM-DD hh:mm:ss"
  Set Group = Root.ChannelGroups.Add(File.Info.FileName)
  Call GetHdrInfo(File, ChanNames)
  jMax = UBound(ChanNames)
  Set TimeChannel = Group.Channels.AddProcessedChannel("DateTime", eTime, eAddProcessor)
  Set Block = File.GetStringBlock()
  FOR j = 1 TO jMax
    Select Case j
      Case 1, 2, 3
        Set Channel = Block.Channels.Add(ChanNames(j), eTime)
        IF j = 3 THEN Channel.Formatter.TimeFormat = "fff"
        Call TimeChannel.Channels.Add(Channel)
      Case ELSE
        Set Channel = Block.Channels.Add(ChanNames(j), eR64)
        Call Group.Channels.AddDirectAccessChannel(Channel)
    End Select ' j
  NEXT ' j
End Sub


Sub GetHdrInfo(File, ChanNames)
  Dim j, jMax, Delim
  Delim = File.Formatter.Delimiters
  ChanNames = Split(Delim & File.GetNextLine, Delim)
  jMax = UBound(ChanNames)
  IF jMax < 3 THEN Call RaiseError()
  FOR j = 1 TO jMax
    ChanNames(j) = Trim(Replace(ChanNames(j), """", ""))
  NEXT ' j
  IF LCase(Trim(ChanNames(1))) <> "date" THEN Call RaiseError()
  IF LCase(Trim(ChanNames(2))) <> "time" THEN Call RaiseError()
  IF LCase(Trim(ChanNames(3))) <> "millisecond" THEN Call RaiseError()
End Sub ' GetHdrInfo()

Brad Turpin

DIAdem Product Support Engineer
National Instruments

[sg000]

I'm trying to replicate this to a Excel Workbook Dataplugin. Same data, workbook instead of CSV ascii.

Anyways, I've got everything except merging the milliseconds again. I tried using the 

Channel.Formatter.TimeFormat = "fff"

but it doesn't like that: "Object doesn't support this property or method: "TimeFormat".

I took a wizard workbook dataplugin and merged in the case statements from your code above, Brad.

The whole thing with the problm area is noted below.

Why wouldn't this work with workbook, but it does ascii?  The Channel object should be identical, right?

What I cannot do with workbook is provide Formatter.TimeFormat = "YYYY-MM-DD hh:mm:ss" at the top. workbook.formatter isn't available.

Sub ReadStore(Workbook)
  Dim oSheet, oGrp, oChn, oBlock, sGrpName, sChnName, lColumns, lIndex, sType, sTypeGuess, clOriginCol, clOriginRow, TimeChannel
  If Workbook.Sheets.Count<1 Then Call RaiseError()
'  Workbook.Formatter.TimeFormat = "YYYY-MM-DD hh:mm:ss"
  Set oSheet = Workbook.Sheets(1)
  'Identify file by cell address and value.
  'This can be adapted to your format.
  If "Date"<>CStr(oSheet.GetCellValue(1, 1)) Then Call RaiseError()
  'Loop for worksheets
  For Each oSheet In Workbook.Sheets
    'Get minimum column and row index.
    'This can be adapted to your format.
    clOriginCol = oSheet.MinPosition.Column
    clOriginRow = oSheet.MinPosition.Row
    'Get channel group name and add a group.
    'This can be adapted to your format.
    sGrpName = oSheet.Name
    Set oGrp = Root.ChannelGroups.Add(sGrpName)
    'Add channel group properties.
    On Error Resume Next
    Call oGrp.Properties.Add(CStr("Name"), oSheet.Name)
    On Error GoTo 0
    If Not SkipSheet(oSheet) Then
    'Get cell block.
    'Row/Column index can be adapted to your format.
    Set oBlock = oSheet.GetCellBlock(2+clOriginRow-1, clOriginCol)
    'Calculate the number of columns.
    lColumns = oSheet.MaxPosition.Column-clOriginCol+1
    Dim eaChnDataType : ReDim eaChnDataType(lColumns) ' channel datatype
    Dim asDlgChnName  : ReDim asDlgChnName(lColumns)  ' channel name
    'Loop for columns.
    For lIndex = 1 To lColumns
      'Set default channel datatype, channel name can be adapted to your format.
      Select Case lIndex
        Case 1, 2, 3
          eaChnDataType(lIndex) = eI32  'eI32 is for auto-detection only, use it by default. If you want fixed datatype, you can specify it later.
          asDlgChnName(lIndex) = oSheet.GetCellValue(1+clOriginRow-1, clOriginCol-1+lIndex)
        Case ELSE
          eaChnDataType(lIndex) = eI32  'eI32 is for auto-detection only, use it by default. If you want fixed datatype, you can specify it later.
          asDlgChnName(lIndex) = oSheet.GetCellValue(1+clOriginRow-1, clOriginCol-1+lIndex)
      End Select 'lIndex
    Next
    'eI32 is for auto-detection only, for fixed datatype, please directly specify eR64/eTime/eString here
    On Error Resume Next
    'Create and add channels.
    On Error GoTo 0

    Set TimeChannel = oGrp.Channels.AddProcessedChannel("DateTime", eTime, eAddProcessor)

    For lIndex = 1 To lColumns
      sChnName = asDlgChnName(lIndex)
      sType = eaChnDataType(lIndex)
      'Add DirectAccessChannel to cell block.
      If eaChnDataType(lIndex)=eNoType Then sChnName = "____ignored____" : sType = eString
      If sType=eI32 Then  'eI32 is for auto-detection only, try getting the actual datatype
        Dim lRowIndexTemp
        For lRowIndexTemp = 2+clOriginRow-1 To oSheet.MaxPosition.Row
          sType = oSheet.GetCellType(lRowIndexTemp, clOriginCol-1+lIndex)
          If sType<>eNoType Then Exit For
        Next
        If sType=eNoType Then sType=eI32
      End If

      Select Case lIndex
        Case 1, 2, 3
          stype = eTime
          Set oChn = oBlock.Channels.Add(sChnName, eTime)
'***** Problem line
          IF lIndex = 3 THEN oChn.Formatter.TimeFormat = "fff"
'*****
          Call TimeChannel.Channels.Add(oChn)

        Case ELSE
          If sType=eI32 Then  'eI32 is for auto-detection only, use the auto-detection of cell block
            Set oChn = oBlock.Channels.Add(sChnName)
          Else
           Set oChn = oBlock.Channels.Add(sChnName, sType)
          End If
          If eaChnDataType(lIndex)<>eNoType Then
            Call oGrp.Channels.AddDirectAccessChannel(oChn)  
            'Add channel properties.
            On Error Resume Next
            On Error GoTo 0
          End If

      End Select 'lIndex

    Next  'column
  End If' If Not SkipSheet(oSheet)
  Next  'worksheet
End Sub 'ReadStore

'Determine whether a worksheet should be skipped.
'The function can be adapted to your format
Function SkipSheet(Sheet)
 SkipSheet = True
 If Sheet.MinPosition.Row>0 And Sheet.MinPosition.Column>0 Then SkipSheet = False
End Function

[Brad_Turpin]

Hi sg,

Are the various columns of type numeric or datetime in Excel?  Can you send over a sample Excel file?

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[sg000]

Thanks for the reply.

Column 1 and 2 are Date and Time respectively.

All others including column 3 (Milliseconds) are general numeric.

Sample xls attached.

[Brad_Turpin]

Hi Scott,

There's no way in a DataPlugin DirectAccessChannel to parse the string in a numeric Excel column according to a datetime pattern.  There are two ways to go here.  The most efficient way, which is best for large data files, is to parse the DateTime columns down to the second with DirectAccessChannels and put them together with a PocessedChannel, then load the "Millisecond" column as a numeric channel and add that channel to the DateTime channel with a script:

Call Data.Root.Clear
Call DataFileLoad(DataFilePath, "Scott_XLS")
Set Group = Data.Root.ActiveChannelGroup
Call ChnAdd(Group.Channels(1), Group.Channels(2), Group.Channels(1))
Call Group.Channels.Remove(2)

If you have small data sets, then the alternative would be to read the values row by row in the DataPlugin and construct each DateTime value all the way down to the millisecond level and add those directly to the DateTimeChannel.Values() collection.  Let me know if you prefer this method.

Brad

[sg000]

Thanks Brad.

Of course the script cannot explicitly call the data file, the names and folders change constantly, so to do with block I really need to do this all with a 2nd user action. ick.

I'll have to judge how big is too big (50 channels, anything between 100 and 100k rows) and how long it takes to do row by row I suppose. I tried it, with the file you provided it doesn't seem to take much longer than what I was dealing with on the .csv version.

Looks like I'll be adding a request to Idea Exchange for TimeFormatter support.  This is sort of a bummer. 🙂

--Scott

[sg000]

---

@Brad_Turpin wrote:

There's no way in a DataPlugin DirectAccessChannel to parse the string in a numeric Excel column according to a datetime pattern. 

I should note, that Formatter.TimeFormat DOES come up in the code completion, so one would have presumed it would work. But alas.... 🙂

[Brad_Turpin]

Hi Scott,

Here's the "slow" implementation that does all the datetime parsing in the DataPlugin.  I hope it's fast enough for your data files...

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[sg000]

That worked great. Thank you Brad. I don't think it was much slower than the csv script. A bit more front end before I see the gree process bar in the lower right do anything, but the processs bar doesn't run for very long, and the csv script was just about the opposite.