Importing Historic Data
Geo SCADA Expert 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} )
Reason for Logging definition
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
OPC Quality definitions
Geo SCADA Expert uses OPC standard quality definitions for the many quality database fields. By using the relevant mask you can get at the quality, status and limit bits.
Geo SCADA Expert uses standard OPC HDA quality definitions and values for the Quality parameter:
| Decimal | Hexadecimal | Description |
|---|---|---|
| 0 | 0x00 | Bad |
| 64 | 0x40 | Uncertain |
| 192 | 0xC0 | Good |
Other Quality values might include:
| Decimal | Hexadecimal | Description |
|---|---|---|
| 4 | 0x04 | Configuration Error |
| 8 | 0x08 | Not Connected |
| 12 | 0x0C | Device Failure |
| 16 | 0x10 | Sensor Failure |
| 20 | 0x14 | Last Known Value |
| 24 | 0x18 | Comm Failure |
| 28 | 0x1C | Out of Service |
| 32 | 0x20 | Waiting for Initial Data |
| 68 | 0x44 | Last Usable Value |
| 80 | 0x50 | Sensor Not Accurate |
| 85 | 0x55 | Low: Engineering Units Exceeded |
| 86 | 0x56 | High: Engineering Units Exceeded |
| 88 | 0x58 | Sub-Normal |
| 216 | 0xD8 | Local Override |
For more information about Quality data fields (which are exposed as status attributes in ViewX), see Quality.
1
The following code illustrates a Visual Basic use of this method to import a single value to a single point using Server Automation:
Dim objServer = New ScxV6DbClient.ScxV6Server
objServer.Connect("system name", "username", "pwd")
Dim obj = objServer.FindObject("Array Group.AG01")
Dim His = obj.Aggregate("Historic")
His.Interface.LoadDataValue(1, 192, #7/3/2016 5:22:23 PM#, 13)
obj = Nothing
objServer.Disconnect()
2
The following code (written using VBScript) illustrates the same example code used as a script within ViewX:
sub ImportValue()
Set obj = Server.FindObject("Array Group.AG01")
Set His = obj.Interface.Historic
His.LoadDataValue 1, 192, #7/3/2016 5:22:23 PM#, 13
end sub
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 Visual Basic use of this method to import an array of five values 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 Double() {13, 14, 15, 16, 17}
Dim PointTimeStamps = New Date() {#2016-07-03 16:23:23#, #2016-07-02 16:24:23#, #2016-07-02 16:25:23#, #2016-07-02 16:26:23#, #2016-07-02 16:27:23#}
Dim PointQuality = New Integer() {192, 192, 192, 192, 192}
Dim PointReason = New Byte() {1, 2, 3, 4, 5}
His.LoadDataValues(PointValues, PointTimeStamps, PointQuality, PointReason)
obj = Nothing
Svr.Disconnect()
End Sub
2
The following code (written using VBScript) illustrates the same example code used as a script within ViewX:
sub ImportValues()
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(#7/3/2016 4:23:23 PM#, #7/3/2016 4:24:23 PM#, #7/3/2016 4:25:23 PM#, #7/3/2016 4:26:23 PM#, #7/3/2016 4:27:23 PM#)
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 extended version of the historic properties as an array of values to any point in your database. The method 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 parameters 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). (For more information about master station state, see Master Station Alarm Limits in the Geo SCADA Expert Guide to Core Point Configuration.)
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 the data. When creating the file, be aware of the following:
- Store the CSV file on the Geo SCADA Expert server that is currently running as the Main server. This requirement applies even if you intend importing the data using VBScript that is running on a Mimic on a client.
- Ensure that each line of the CSV file specifies a single record and has the format:
YYYY,MM,DD,HH,MM,SS,Value[,Quality]
(where 'Quality' is optional (see below))
1
The following code illustrates a Visual Basic 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:\AG01HistoricTest1.csv")
obj = Nothing
Server.Disconnect()
End Sub
2
The following code (written using VBScript) illustrates the same example code. However, in this case, it is 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 'State' (and 'MS State' if the Master Station Alarm Limits feature is enabled) of each historic record are calculated automatically.
- The 'Reason For Logging' for each historic record is fixed as 0 ('Current Data').
- The 'Quality' for each historic record is optional. If the 'Quality' element is omitted, the 'Quality' of the historic record defaults to 192 ('Good'). For example:
2016,07,03,16,25,23,15.2,192.