Importing Historic Data
ClearSCADA allows you to import historic data into point and accumulator histories using a set of Historic aggregate methods. The method you use depends on the amount of data that you want to import or insert into the database.
LoadDataValue
This method allows you to import a single value to any point in your database.
LoadDataValue ( Reason {Byte},Quality {Long}, Time Stamp{Time}, Value{Variant} )
OPC Quality definitions
ClearSCADA uses OPC standard quality definitions for the many quality database fields, including 'CurrentQuality' and 'PreviousQuality' point fields (also for 'PutAsideQuality' in ClearSCADA 2007 R1 and later). By using the relevant mask you can get at the quality, status and limit bits.
Values for Reason parameter (Reason For Logging field available in the historic database):
0 = Current Data
1 = Value Change
2 = State Change
3 = Timed Report
4 = End of Period
5 = End of Period Reset
6 = Override
7 = Release Override
8 = Modified/Inserted
1
The following code illustrates a VB 6 use of this method to import a single value to a single point using Server Automation:
Set objServer = New ScxV6Server
objServer.Connect "system name", "username", "pwd"
Set obj = objServer.FindObject("Array Group.AG01")
Set His = obj.Aggregate("Historic")
His.Interface.LoadDataValue ("1", 192, "03 Jul 2016 17:22:23", 13)
Set obj = Nothing
objServer.Disconnect
2
The following code illustrates the same example code used as a script within ViewX:
sub addTwoValues()
Set obj = Server.FindObject("Array Group.AG01")
Set His = obj.Interface.Historic
His.LoadDataValue "1", 192, "03 Jul 2016 17:22:23", 13
end sub
ClearSCADA uses standard OPC HDA quality definitions and values for the Quality parameter:
| Dec | Hex | Description |
| 0 | 0x00 | Bad |
| 64 | 0x40 | Uncertain |
| 192 | 0xC0 | Good |
LoadDataValues
This method allows you to import an array of values to any point in your database.
LoadDataValues ( Value{Array of Variant}, Time Stamp{Array of Time}, Quality {Array of Long}, Reason {Array of Byte} )
A separate array needs to be created for each parameter within the method.
1
The following code illustrates a VB 6 use of this method to import an array of five value to a single point using Server Automation:
Sub Main()
Dim Svr As ScxV6DbClient.ScxV6Server
Svr = New ScxV6DbClient.ScxV6Server
Svr.Connect("system name", "username", "pwd")
Dim obj = Svr.FindObject("Array Importing.AG01")
If obj Is Nothing Then
REM Object not found
Exit Sub
End If
REM object found
Dim His = obj.Interface.Historic
If His Is Nothing Then
REM Object does not have Historic aggregate, not Historic enabled#
Exit Sub
End If
Dim PointValues = New Integer() {13, 14, 15, 16, 17}
Dim PointTimeStamps = New String() {"02 Jul 2016 16:23:23", "02 Jul 2016 16:24:23", "02 Jul 2016 16:25:23", "02 Jul 2016 16:26:23", "02 Jul 2016 16:27:23"}
Dim PointQuality = New Integer() {192, 192, 192, 192, 192}
Dim PointReason = New String() {"1", "2", "3", "4", "5"}
His.LoadDataValues(PointValues, PointTimeStamps, PointQuality, PointReason)
obj = Nothing
Svr.Disconnect()
End Sub
2
The following code illustrates the same example code used as a script within ViewX:
sub addTwoValues()
Set obj = Server.FindObject("Array Importing.AG02")
Set His = obj.Interface.Historic
Dim PointValues
Dim PointTimeStamps
Dim PointQuality
Dim PointReason
PointValues=Array(13, 14, 15, 16, 17)
PointTimeStamps=Array("03 Jul 2016 16:23:23", "03 Jul 2016 16:24:23", "03 Jul 2016 16:25:23", "03 Jul 2016 16:26:23", "03 Jul 2016 16:27:23")
PointQuality=Array(192, 192, 192, 192, 192)
PointReason=Array("1", "2", "3", "4", "5")
His.LoadDataValues PointValues, PointTimeStamps, PointQuality, PointReason
end sub
The 'Reason' parameter is optional. If this parameter is omitted it defaults to 0 (Current Data).
A maximum of 5,165 elements are allowed in the arrays, an error is raised if this number is exceeded.
LoadDataValuesEx
This method allows you to import an extend version all the historic properties as an array of values to any point in your database and follows a similar format to LoadDataValues.
LoadDataValuesEx ( Value{Array of Variant}, Time Stamp{Array of Time}, Quality {Array of Long}, Status{Array of Long}, State{Array of Byte}, Reason {Array of Byte}, MSState{Array of Byte} )
The Status, State, Reason and MSState paramaters are all optional.
The Status parameter is a bit mask of flags that varies between drivers.
To calculate the State from the Value specify the state as 255 (or omit the parameter).
If there is no master-station state (or the feature is disabled) then specify the MSState as 255 (or omit the parameter).
LoadDataFile
This method allows you to use a datafile as a source to import historic data for one or more objects in the database. This method does not have the same restrictions on the amount of data as the LoadDataValues method and is also much faster at loading the data.
LoadDataFile ( Filename{String} )
It is recommend that you use a CSV file as a file format for importing data. When creating the file ensure the following:
- Each line of the CSV file specifies a single record and has the format:
YYYY,MM,DD,HH,MM,SS,Value
1
The following code illustrates a VB 6 use of this method to import a file using Server Automation:
Sub Main()
Dim Svr As ScxV6DbClient.ScxV6Server
Svr = New ScxV6DbClient.ScxV6Server
Svr.Connect("system name", "username", "pwd")
Dim obj = Svr.FindObject("Array Importing.AG01")
If obj Is Nothing Then
REM Object not found
Exit Sub
End If
REM object found
Dim His = obj.Interface.Historic
If His Is Nothing Then
REM Object does not have Historic aggregate, not Historic enabled#
Exit Sub
End If
His.LoadDataFile "D:\\AG03HistoricTest1.csv"
Set obj = Nothing
objServer.Disconnect
end sub
2
The following code illustrates the same example code used as a script within ViewX to import historic data for a single point:
sub AddDataFileAG03()
Set obj = Server.FindObject("Array Group.AG03")
Set His = obj.Interface.Historic
His.LoadDataFile "D:\\AG03HistoricTest.csv"
end sub
The data format within the file should be similar to the following:
2016,07,03,16,23,23,0
2016,07,03,16,24,23,12
- The file must use the ANSI encoding format. Other encoding formats will cause the import to fail.
- The 'Reason' parameters is assumed to be 0 (Current Data) on import.
- The 'Quality' parameter is not included.