Generating WinCC Process Archive CSV Reports via OLE DB

Overview

Siemens WinCC stores every archived process tag in a Microsoft SQL Server database (SQL Server 2005, 2008, 2012, 2016, 2019, or 2022 depending on the WinCC version installed). Although the raw tables look like ordinary SQL tables, the timestamp column is a WinCC-proprietary floating-point value counting seconds since 1970-01-01, and the archive is split into compressed (TAG_<Name>_0) and uncompressed (TAG_<Name>) partitions that rotate by time. Direct T-SQL queries therefore return values that look plausible but cannot be plotted, exported, or post-processed without an additional translation layer.

This technical reference shows how to wire a WinCC picture button to a VBScript action that opens the WinCC OLE DB Provider, runs a SQL query that returns the last 10 minutes of one or more tags from the Process Value Archive, converts WinCC time stamps to local time, and writes the result to a CSV file on disk. A parallel section covers the ANSI-C scripting variant, and a final section maps the same workflow to WinCC Runtime Professional (TIA Portal V16–V20) using the documented user-archive OLE DB query pattern.

Prerequisites

• WinCC V7.0 SP3 or later (V7.4, V7.5, or V7.5 SP2 recommended for newer SQL Server editions) or WinCC Runtime Professional V16/V17/V18/V19/V20 inside TIA Portal.
• SQL Server 2005 or higher installed locally or on a reachable server. WinCC Runtime owns the catalog CC_<ProjectName>_<Suffix>R (for example CC_Process_22R).
• A configured Process Value Archive with at least one tag in WinCC Explorer / TIA Portal that is logging at a 1-second or 1-minute cycle.
• WinCC runtime user must have db_owner rights on the runtime catalog. On SQL Server 2005 this is granted with sp_addrolemember 'db_owner', '<WinCCUser>'.
• Graphics designer access to the picture and the button object.
• Local or UNC write access for the WinCC runtime user to the destination folder (for example C:\Reports\).

Process Value Archive Architecture

WinCC writes each Process Value Archive into a pair of SQL tables per partition:

The compressed table is the correct one to query for trend reports because it survives the rotation of _1. Each row carries the columns Timestamp (float, seconds since 1970-01-01 UTC), RealValue (double), Quality (tinyint, 0xC0 = Good, 0x40 = Bad), and Flags (tinyint, 0x80 = substituted value, 0x40 = time-jumped value).

OLE DB Provider Connection Strings

The WinCC OLE DB Provider (WinCCOLEDBProvider.1) is registered automatically by the WinCC installation. Two connection strings cover the typical cases:

For RT Professional the same provider is used. The official Siemens documentation for user-archive queries via MS OLE DB Provider is referenced at the end of this article.

Implementing the CSV Export Button (VBScript)

Open Graphics Designer, select the button object, and navigate to Events → Mouse → Click. Right-click, select "VBS Action", and paste the following script. It opens the OLE DB connection, runs a parameterized query for the last ten minutes of three example tags, converts WinCC time stamps to local time, and writes the result to a timestamped CSV file.

' OnClick event of "Export CSV" button
Option Explicit

Sub OnClick(ByVal Item)
    Dim sConn, oConn, oRS, oCmd
    Dim sSQL, sCSV, sFile, sLine
    Dim fso, oFile, dStart, dEnd
    Dim iQuality
    
    ' --- 1. Build connection string -----------------------------------
    sConn = "Provider=WinCCOLEDBProvider.1;" & _
            "Catalog=CC_Process_22R;" & _
            "Data Source=.\WinCC"
    
    ' --- 2. Compute the 10-minute rolling window ----------------------
    dEnd   = DateAdd("s", Now,      #1970-01-01#)  ' current time in WinCC format
    dStart = DateAdd("n", -10, dEnd)              ' 10 minutes back
    
    ' --- 3. Build parameterized SQL ------------------------------------
    sSQL = "SELECT Timestamp, RealValue, Quality, Flags " & _
           "FROM TAG_PVArchive_0 " & _
           "WHERE Timestamp BETWEEN " & dStart & " AND " & dEnd & _
           "  AND (ValueID = (SELECT ValueID FROM PVDATAMANAGER " & _
           "                  WHERE TagName = 'Plant1\Temp1')) " & _
           "ORDER BY Timestamp ASC"
    
    ' --- 4. Open OLE DB connection ------------------------------------
    Set oConn = CreateObject("ADODB.Connection")
    oConn.ConnectionString = sConn
    oConn.CursorLocation   = 3   ' adUseClient
    oConn.Open
    
    Set oCmd = CreateObject("ADODB.Command")
    Set oCmd.ActiveConnection = oConn
    oCmd.CommandText       = sSQL
    oCmd.CommandTimeout    = 30
    
    Set oRS = oCmd.Execute
    
    ' --- 5. Build CSV header ------------------------------------------
    sCSV = "LocalTime;WinCCTime;RealValue;Quality;Flags" & vbCrLf
    
    ' --- 6. Iterate rows and convert time stamps -----------------------
    Do While Not oRS.EOF
        iQuality = CInt(oRS("Quality"))
        sLine = FormatDateTime(DateAdd("s", CDbl(oRS("Timestamp")), _
                                       #1970-01-01#), vbGeneralDate) & ";" & _
                CStr(oRS("Timestamp")) & ";" & _
                CStr(oRS("RealValue")) & ";" & _
                Hex(iQuality) & ";" & _
                Hex(CInt(oRS("Flags"))) & vbCrLf
        sCSV = sCSV & sLine
        oRS.MoveNext
    Loop
    
    ' --- 7. Write file ------------------------------------------------
    Set fso = CreateObject("Scripting.FileSystemObject")
    sFile = "C:\Reports\PV_Export_" & _
            Replace(Replace(FormatDateTime(Now, vbGeneralDate), "/", "-"), ":", "-") & _
            ".csv"
    Set oFile = fso.CreateTextFile(sFile, True, True)  ' Unicode = True
    oFile.Write sCSV
    oFile.Close
    
    ' --- 8. Cleanup ----------------------------------------------------
    oRS.Close
    oConn.Close
    Set oRS = Nothing
    Set oConn = Nothing
    Set oCmd = Nothing
    Set oFile = Nothing
    Set fso = Nothing
    
    ' Optional: write a status to an internal tag
    HMIRuntime.Trace "CSV export written to " & sFile & vbCrLf & sCSV
End Sub

Rolling 10-Minute Buffer Strategy

The button action above runs only on demand. If the requirement is to keep the last ten minutes hot in memory and write only when the user clicks, use a cyclic VBScript trigger plus an in-memory dictionary. The WinCC V7.5 manual documents the HMIRuntime.BaseTagName pattern for tag arrays; pair it with the Windows Scripting Dictionary object as a sliding window.

' Global declaration at project level (optional)
Dim gBuffer   ' Dictionary: key = "Tag|UnixSec", value = Array(ts, val, q)

' Cyclic trigger fires every 60 000 ms
Sub UpdateBuffer(ByVal Item)
    Dim dNow, dKey, aRow, i
    Dim tags, sTag, oTag, dVal, lQ
    
    If IsEmpty(gBuffer) Or gBuffer Is Nothing Then
        Set gBuffer = CreateObject("Scripting.Dictionary")
    End If
    
    dNow = DateAdd("s", Now, #1970-01-01#)
    tags = Array("Plant1\Temp1", "Plant1\Press1", "Plant1\Flow1")
    
    For i = 0 To UBound(tags)
        sTag = tags(i)
        Set oTag = HMIRuntime.Tags(sTag)
        oTag.Read
        dVal = oTag.Value
        lQ   = oTag.Quality
        dKey = sTag & "|" & CStr(dNow)
        If Not gBuffer.Exists(dKey) Then
            gBuffer.Add dKey, Array(dNow, dVal, lQ)
        End If
    Next
    
    ' Trim entries older than 10 minutes
    Dim dCutoff, k
    dCutoff = dNow - 600
    Dim aKeys : aKeys = gBuffer.Keys
    For i = 0 To UBound(aKeys)
        If CLng(Split(aKeys(i), "|")(1)) < dCutoff Then
            gBuffer.Remove aKeys(i)
        End If
    Next
End Sub

' OnClick: serialise the buffer
Sub ExportBuffer(ByVal Item)
    Dim k, a, sCSV, fso, oFile, sFile
    sCSV = "Tag;WinCCTime;RealValue;Quality" & vbCrLf
    For Each k In gBuffer.Keys
        a = gBuffer(k)
        sCSV = sCSV & Split(k, "|")(0) & ";" & _
                       a(0) & ";" & a(1) & ";" & Hex(CInt(a(2))) & vbCrLf
    Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    sFile = "C:\Reports\Buffer_" & FormatDateTime(Now, vbGeneralDate) & ".csv"
    Set oFile = fso.CreateTextFile(sFile, True, True)
    oFile.Write sCSV
    oFile.Close
End Sub

Variant: ANSI-C Script (WinCC V7 Classic)

For WinCC V7 pictures that prefer C scripting over VBScript, the same workflow is possible with DBPROVIDER functions declared in apdefap.h:

// C-Action on click
#include "apdefap.h"
void OnClick(char* lpszPictureName, char* lpszObjectName,
             char* lpszPropertyName)
{
    // Connection
    dbProviderInit("WinCCOLEDBProvider.1", "CC_Process_22R", ".\\WinCC");
    dbConnect();

    // Query
    double dEnd   = GetSecondsSince1970();
    double dStart = dEnd - 600.0;
    char   szSQL[1024];
    sprintf(szSQL,
            "SELECT Timestamp, RealValue, Quality "
            "FROM TAG_PVArchive_0 "
            "WHERE Timestamp BETWEEN %f AND %f "
            "  AND ValueID IN (SELECT ValueID FROM PVDATAMANAGER "
            "                  WHERE TagName IN ('Plant1\\Temp1'))",
            dStart, dEnd);

    // Open
    dbOpen(szSQL);

    // Iterate with dbGetField...
    FILE* fp = fopen("C:\\Reports\\CExport.csv", "w");
    fprintf(fp, "WinCCTime;RealValue;Quality\n");
    while (dbGetRecord() == DB_OK) {
        fprintf(fp, "%f;%f;%d\n",
                dbGetFieldAsDouble("Timestamp"),
                dbGetFieldAsDouble("RealValue"),
                dbGetFieldAsInt("Quality"));
    }
    fclose(fp);
    dbClose();
    dbDisconnect();
}

RT Professional (TIA Portal V16-V20) Method

In WinCC Runtime Professional, user archives are queried with the same OLE DB pattern. The official Siemens documentation for this workflow is "Query for User Archives (RT Professional)":

Query for User Archives (RT Professional) – TIA Portal V20 documentation

For Process Value Archives in RT Professional, query the table TAG_<ArchiveName>_0 directly via the WinCC OLE DB Provider. The connection string is identical:

Dim sConn
sConn = "Provider=WinCCOLEDBProvider.1;" & _
        "Catalog=CC_<RTProjectName>_R;" & _
        "Data Source=.\WinCC"

To access user archives, swap the table prefix from TAG_ to UA#:

sSQL = "SELECT * FROM UA#ProductionLog ORDER BY ID ASC"

Architecture Overview

Verification Procedure

1. Open WinCC Explorer → "Tag Logging" → "Archives". Verify that the archive referenced in the script exists and that the tag is checked under "Selected".
2. Activate WinCC Runtime (RT) on the engineering station or on the target server.
3. Open the picture containing the export button. Confirm that the button is enabled and that the OnClick event is bound to the VBScript (not to a C action if you pasted VBScript).
4. Click the button. Open the WinCC "Diagnostics" tool (WinCC Explorer → Tools → "ApDiag"). Confirm that no SQL error or access violation is logged.
5. Open the destination folder and check that the CSV file exists, has the expected size, and contains the column header plus at least ten data rows.
6. Open the CSV in Microsoft Excel (data import → semicolon separator). Confirm that the "LocalTime" column is a recognisable date and that "RealValue" is numeric.
7. Run the equivalent query directly in SQL Server Management Studio with the same catalog name to confirm that the WinCC OLE DB Provider returns identical rows:

SELECT TOP 50 Timestamp, RealValue, Quality, Flags
FROM CC_Process_22R.dbo.TAG_PVArchive_0
ORDER BY Timestamp DESC;

Troubleshooting Matrix

Field-Proven Caveats

• Tag aliases in faceplates: When the tag is referenced through a faceplate interface, the TagName in PVDATAMANAGER includes the picture name. Strip the prefix with REPLACE or join through the active picture context.
• Time zone: WinCC stores time stamps in UTC-relative seconds since 1970-01-01. The local conversion in the script applies the OS time zone of the runtime server, not the engineering station. For multi-site deployments, document the offset explicitly.
• Compression granularity: The compressed table returns one row per archive cycle (1 minute, 5 minutes, 1 hour depending on configuration). Do not expect every original sample if you query _0 rather than _1.
• SQL Server 2005 deprecation: SQL Server 2005 is out of mainstream support. Modern installations of WinCC V7.4 and later pair with SQL Server 2014, 2016, or 2019. The OLE DB connection string is identical but the SQL Server Browser service must be running for named instances.
• 32-bit vs 64-bit provider: On a 64-bit Windows Server, the WinCC OLE DB Provider is 32-bit. The VBScript runtime inside WinCC is therefore also 32-bit; do not use a 64-bit-only ADO version.
• Filename collisions: Two clicks within one second overwrite the file silently. Use Replace(FormatDateTime(Now, vbGeneralDate), ":", "-") plus milliseconds if multiple operators are likely to click concurrently.
• Performance: A 10-minute window of three tags is sub-second. A 24-hour window of 200 tags at 1-second resolution can take several seconds; in this case schedule the export to a background thread and post a completion notification back to the HMI.

Standards and Documentation References

The WinCC V7 manual set ("WinCC/Information System V7.5", chapter "ANSI-C and VBScript for actions", "Process Value Archive" and "WinCC OLE DB Provider") is the canonical reference for this workflow. For RT Professional, the TIA Portal online help under "WinCC RT Professional → Access to archive data via WinCC OLE DB Provider" documents the same provider.

FAQ