Additional Example 3: Reading in Data from Different Files

Download PDF

Updated2024-09-12
5 minute(s) read

The following example shows a DataPlugin for reading in the text file DataPlugin_AdditionalEx3.aex3 and the table calculation file DataPlugin_AdditionalEx3.xls. The DataPlugin script file is located at DataPlugin_AdditionalEx3.vbs

Data Format Description

This text file contains metadata and channel data. The metadata consists of a number of group properties and a text row containing the name of the channels to be read in. When DIAdem saves the group properties, a row contains the name of a property and, separated by the equals sign, the value of this property.

Then the channel data follows: a date/time channel and a number of numeric channels. The settings in the text file are separated by a tabulator. The specified numbers use a point as the decimal symbol. The end of the line is indicated by Carriage Return and Line Feed.

The table calculation file with the same name contains a table which specifies the channel names and the channel units assigned to these names.

Special Features of the DataPlugin

Line feed: CrLf
Separators: <Tab>
Decimal symbol: Point
Reading in the table calculation file
Using the Split function to determine the group property and its value
Using DirectAccess channels to read in channel data

Copy script

Option Explicit 

Sub ReadStore(oFile) 
  ' Set string formatter
  oFile.Formatter.LineFeeds    = vbCrLf
  oFile.Formatter.Delimiters   = vbTab
  oFile.Formatter.DecimalPoint = "."
  oFile.Formatter.TimeFormat   = "DD/MM/YYYY hh:mm"
  
  Dim sExcelFileName, oExcelFile, saSignalUnits
  sExcelFileName = oFile.Info.Drive  & oFile.Info.Directory & oFile.Info.FileName & ".xlsx"
  Set oExcelFile = OpenSpreadsheet(sExcelFileName)
  Call ReadExcelFile(oExcelFile, saSignalUnits) ' Read in the units and the associated names

  ' Create channel group object and set property
  Dim oChannelGroup : Set oChannelGroup = Root.Channelgroups.Add(oFile.Info.FileName)

  Dim bHeaderEmpty, sCurrLine, saHDProperty
  bHeaderEmpty = False
  Do
    ' Read first line
    sCurrLine = oFile.GetNextLine
    If InStr(sCurrLine, "=") > 0 Then
      saHDProperty = Split(sCurrLine, "=")
      Call oChannelGroup.Properties.Add(Trim(saHDProperty(0)), Trim(saHDProperty(1)))
    Else
      bHeaderEmpty = True
    End If
  Loop Until bHeaderEmpty

  ' Read channel header
  Dim saChnName  : saChnName = Split(sCurrLine, vbTab)
  Dim saChnDim() : ReDim saChnDim(uBound(saChnName))
  Call GetChnDim(saChnName, saChnDim, saSignalUnits)
 
  ' Get object for block stored data  
  Dim oBlock : Set oBlock = oFile.GetStringBlock() 
  oBlock.Position = oFile.Position
  
  ' Import all other channels with fast access
  Dim iLoop, elem, oChn
  iLoop = 0
  Set oChn = oBlock.Channels.Add(saChnName(iLoop), eTime)
  ' Add defined channel to DIAdem channel group
  Call oChannelGroup.Channels.AddDirectAccessChannel(oChn)

  For iLoop = 1 to uBound(saChnName)
    ' Create channel in string block (file)
    Set oChn = oBlock.Channels.Add(saChnName(iLoop), eR64)
    If saChnName(iLoop) <> "-" Then
      Call oChn.Properties.Add("Unit_String", saChnDim(iLoop))
      ' Add defined channel to DIAdem channel group
      Call oChannelGroup.Channels.AddDirectAccessChannel(oChn)
    End If
  Next
End Sub
'----------------------------------------------------------------------------
Sub GetChnDim(saChnName, saChnDim, saSignalUnits)
  Dim iLoop, iPos, i, j
  
  For iLoop = 0 to uBound(saChnName)-1
    saChnName(iLoop) = Trim(saChnName(iLoop))
    saChnDim(iLoop) = ""
    
    If uCase(Left(saChnName(iLoop),2)) = "AI" Then
      saChnName(iLoop) = "-"               ' ignored
    Else
      If InStr(saChnName(iLoop), "(") Then ' Unit
        iPos = InStr(saChnName(iLoop), "(")
        saChnDim(iLoop) = Trim(Mid(saChnName(iLoop), iPos+1, InStr(saChnName(iLoop), ")")-iPos-1))
        saChnName(iLoop) = Mid(saChnName(iLoop), 1, iPos-1)
      Else
        If (InStr(saChnName(iLoop), "/") > 0) and not (uCase(saChnName(iLoop)) = "DATE/TIME" ) Then ' delete before / and behind
          iPos = InStr(saChnName(iLoop), "/")
          If Mid(saChnName(iLoop), iPos-1, 1) <> " " Then
            iPos = InStrRev(Trim(saChnName(iLoop)), " ")
          End If
          saChnName(iLoop) = Trim(Left(saChnName(iLoop), iPos-1))
        End If
        ' Search unit
        For i = 0 To uBound(saSignalUnits,2)   'oSheet.MaxPosition.Column
          For j = 1 To uBound(saSignalUnits,1) 'oSheet.MaxPosition.Row
            If (uCase(saChnName(iLoop)) = saSignalUnits(j,i)) or (saSignalUnits(j,i)<>"") and Instr(uCase(saChnName(iLoop)), saSignalUnits(j,i)) Then
              saChnDim(iLoop) = saSignalUnits(0,i)
              Exit For  
            End If  
          Next
        Next
      End If
    End If
  Next
End Sub

'----------------------------------------------------------------------------
Sub ReadExcelFile(oExcelFile, saSignalUnits)
  Dim oSheet : Set oSheet = oExcelFile.Sheets(1)
  Redim saSignalUnits(oSheet.MaxPosition.Row-2, oSheet.MaxPosition.Column-2) ' Ignore empty line or first column, zero-based array
  
  Dim i, j, sText
  For i = 2 To oSheet.MaxPosition.Column
    sText = oSheet.GetCellValue(1,i)
    saSignalUnits(0,i-2) = Trim(sText) ' Units

    For j = 3 To oSheet.MaxPosition.Row
      sText = oSheet.GetCellValue(j,i)
      If not IsNull(sText) Then
        saSignalUnits(j-2,i-2) = uCase(Trim(sText)) ' Signal names which have this unit
      End If  
    Next
  Next
End Sub

DataPlugin Description

The DataPlugin first specifies the general format of the file through the File object. The DataPlugin uses the VBS constant vbCrLf as the linefeed character. The VBS constant vbTab separates single values. A point is the decimal symbol.

The the DataPlugin uses the OpenSpreadSheet function to open the spreadsheet file with the same name and transfers the contents of this file with the ReadExcelFileprocedure to the two-dimensional array saSignalUnits.

The DataPlugin generates a new channel group whose name corresponds to the name of the file to be read in. Then the DataPlugin reads in the metadata line by line. The DataPlugin uses the Split function to check the metadata containing the group properties for the equals sign. The DataPlugin interprets the information in front of the equals sign as group property name and the information behind the equals sign as the value.

The DataPlugin uses the Split function to check the next line, which contains the channel names, for the vbTab separator and stores the substrings in a one-dimensional, zero-based array. The number of arrays specifies the number of channels that the file contains.

The GetChnDim procedure checks the read in channel names and, if they are valid, determines the associated channel unit using the saSignalUnits array. If the name of a channel is not valid, the DataPlugin marks this channel with the "-" unit.

The DataPlugin transfers the other file contents into a StringBlock. If DirectAccess channels are associated with a StringBlock, the order of the values in a row determine which value belongs to which channel. The first value of the row belongs to the first channel, the second value of the row belongs to the second channel and so on. The value limits are specified by the Delimiter property, which in this case is vbTab.

The channel data is imported in a loop structure. The number of loops is specified by the size of the array which contains the channel names. Within the loop, the DataPlugin checks the channel unit and, if it is valid, generates a new DirectAccess channel with the respective name and the associated unit. Because channels always belong to one channel group, the DataPlugin assigns the channels to the new channel group.

Was this information helpful?

Yes No

DIAdem Help

Filters

Content Type

Programming Language

Table of Contents

Additional Example 3: Reading in Data from Different Files

Data Format Description

Special Features of the DataPlugin

DataPlugin Description

In This Section

Additional Comments

Download PDF