Writing from a WinCC recordset

Question

I have a working script that reads WinCC DB and writes data into a CSV file with two columns (1 timetag and 1 value).

path = "C:\HMI\Report\Report.csv"
'creating csv file
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(path) Then
    fso.DeleteFile(path)
End If

    fso.CreateTextFile(path)
Set f = fso.GetFile(path)

Const ForWriting = 2
Const TristateUseDefault = -2
 
Set ts = f.OpenAsTextStream(ForWriting,TristateUseDefault)

'''''''''''''''''''

'connection to SQL
Dim Pro                 'Provider 
Dim DSN                 'Data Source Name 
Dim DS                  'Data Source 
Dim ConnString          'Connection String 
Dim MachineNameRT       'Name of the PC from WinCC-RT 
Dim DSNRT               'Data Source Name from WinCC-RT 
Dim Conn                'Connection to ADODB 
Dim RecSet              'RecordSet 
Dim Command             'Query 
Dim CommandText         'Command-Text 

Set MachineNameRT = HMIRuntime.Tags("@LocalMachineName")
Set DSNRT = HMIRuntime.Tags("@DatasourceNameRT")
    pro="Provider=WinCCOLEDBProvider.1;"
    DSN="Catalog=" & DSNRT.Read & ";"
    DS="Data Source=.\WinCC" ' & MachineNameRT.Value & "\WinCC"
    ConnString = Pro + DSN + DS

Set Conn = CreateObject("ADODB.Connection")
    Conn.ConnectionString = ConnString
    Conn.CursorLocation = 3
    Conn.Open
    CommandText="Tag:R,(ProductionTags\Temperature1),'" & StartArchive & "','" & StopArchive & "'" 

'Create the recordset, read the records and set to first redcordset: 
Set Command = CreateObject("ADODB.Command") 
    Command.CommandType = 1 
Set Command.ActiveConnection = Conn 
    Command.CommandText=CommandText 
Set RecSet = Command.Execute 
    RecSet.MoveFirst
Do While Not RecSet.EOF 
    ts.WriteLine (RecSet.Fields("TimeStamp").Value & ";" & RecSet.Fields("RealValue").Value) '<-that's the line in question
    RecSet.MoveNext 
Loop 

' Close all
ts.Close 
RecSet.Close 
Set RecSet=Nothing 
Set Command = Nothing 
conn.Close                  
Set Conn = Nothing 
Set fso = Nothing 
Set f = Nothing 
Set ts = Nothing

I want to write 1 timetag and 3 values, or 3 timetags and 3 values in 4 or 6 columns.

I want to change query as

CommandText="Tag:R,(ProductionTags\Temperature1;ProductionTags\Temperature2;ProductionTags\Temperature3),'" & StartArchive & "','" & StopArchive & "'"

But I can't understand how to write an argument for the Writeline to get the values in the loop to make it 4 or 6 columns.

The `WinCCOLEDBProvider` is not Standard SQL syntax have tagged this question [tag:wincc] to get you a more specific audience. — user692942, Jan 18 '22 at 13:59

score 0 · Answer 1 · answered Feb 01 '22 at 12:44

A little long text, sorry about that and I also like to keep the query simple. No need to make things complicated.

First something about “WinCC Connectivity pack” you are using: “Licensed access to online and archive data of WinCC is enabled with the WinCC / Connectivity Pack. The WinCC OLE DB Provider makes access to the process value and alarm archives possible. Data that is stored, compressed in the database can be read as decompressed data. The WinCC OLE DB Provider also provides analysis functions such as Minimum, Maximum of archive tags for example.”

Since the data is compressed, the returning recordset’s “layout” to the query is fixed:

Fields(0) = VarID
Fields(1) = DateTime
Fields(2) = RealValue
Fields(3) = Quality
Fields(4) = Flags

Therefore when doing a query with more then 1 tag, the returning “list” is just longer and your archive tag is now only a number(VarID). There are also no improvements in performance by doing so that I am aware of. Also, its a limit on how large the recordset can be in vbs.

Seems like you want a table with timestamp as an “index”. Timestamps in WinCCv7 are usually in milliseconds and its a good idea to truncate that part when building data for a table, eliminating unnecessary “gaps” in rows based on a millisecond. Sometimes the data really have a true milliseconds(nano is more rare) resolution based on “time i/o” and things like “AR_SEND” / “OPC UA(telecontrol)” / “WinCC ODK functions”.

My suggestion would be to use temporary objects/array to hold the results of several sql commands executed one after the other, in a boring “for” loop or something similar.

Then match the timestamps(without ms, or with) creating a nice table, without to many “gaps” This table you can print out as an csv file with writeline

Exemples can be found in your local help file: “WinCC Help → Interfaces → WinCC/Connectivity Pack documentation → Examples for Access Using OLE DB Provider → Examples : Analyzing Process Value Archives in WinCC Projects → “

In the example projects found on “support.industry.siemens.com” in the forum and official examples from downloads.

But a complete example with a resulting nice table structure, sorry, not existing as far as I know.

Those who want a table usually uses excel(with plug-ins) or some other specialized reporting tool. (MS SQL reporting services can be used with connectivity pack)

//PerD

Thanks for the reply, I ended just like you said. I do several requests and add the new values of new tags to the same file to the new column. The problem was that I didn't realize that the query with multiple tags that I did actually worked, but data was represented in a way of 2 columns, where values and timetags of the second tag goes after the values of the first tag and values of third tag goes after the second tag's values. But it's soothing to know that there's no better way to make such data imports any better. Thanks. — Aleksandr, Jul 26 '22 at 12:45

Writing from a WinCC recordset

1 Answers1