Exporting WinCC Tag and Alarm Archives to Excel A Technical Guide

Overview: WinCC Archive Export Problem Statement

Siemens WinCC stores process values from the TagLogging editor and messages from the AlarmLogging editor in Microsoft SQL Server databases. Operators and engineers frequently need to open these archives in Microsoft Excel for reporting, trending, and root-cause analysis. Unlike the legacy ProTool/ProSave environment, where a single button click exported historical data to Excel, WinCC requires either a dedicated add-on product or a custom data access path through the WinCC OLE DB provider.

For a typical mid-sized project with approximately 63 archive tags and 1,300 active alarm messages, three practical options are commonly considered:

1. PM-OPEN-EXPORT – a low-cost add-on focused on one-click export of TagLogging and AlarmLogging data to CSV/XLSX.
2. WinCC/IndustrialDataBridge – a flexible bridge that copies or transforms archive data to ODBC targets including SQL Server, Oracle, Excel, and CSV.
3. WinCC/DataMonitor – a web-based reporting and analysis server with an Excel add-in for ad-hoc queries.

Each option is licensed separately, has different engineering effort, and fits a different user skill profile. The selection path below helps determine the right product based on tag count, alarm volume, scripting skills, and budget.

WinCC Archive Database Architecture

Before configuring any export tool, it is important to understand how WinCC structures its historical data. Both TagLogging and AlarmLogging write to a SQL Server instance named \WinCC (default instance) using the WinCC_CC_<ProjectName>_<TimeStamp>_<RunTimeIndex> naming convention, in addition to the runtime databases CC_AlArm_<TimeStamp>_<RunTimeIndex> and CC_AlOp_<TimeStamp>_lt;RunTimeIndex>.

The TagLogging process value archive is queried through the WinCC OLE DB provider, registered as WinCCOLEDBProvider.1. The connection string used in SQL queries is:

Provider=WinCCOLEDBProvider.1;Catalog=CC_OpenArch_03_05_27_14_11_47R;
Data Source=.\WinCC;Mode=Read;

The Catalog parameter points to the running archive database; the Mode=Read flag guarantees the OLE DB consumer does not lock archive writers. Tag values are retrieved with the Tag:R, Tag:R,Conditions,Value, or Tag:R,'2024-01-01 00:00:00.000','2024-12-31 23:59:59.999' syntax. A typical query for a 64-bit floating-point tag looks like:

SELECT * FROM
(EXEC dbo.TAG_R 'ArchiveName','TagName','2024-01-01 00:00:00.000','2024-01-31 23:59:59.999')
ORDER BY Timestamp ASC;

Alarm archives are queried with the ALARMVIEW:R function and standard SQL SELECT statements against the dbo.MSARV view. The complete recordset of 1,300 active alarm messages, when limited to a 24-hour window, is typically between 5 MB and 40 MB of raw data – small enough to be held entirely in memory and written to a single XLSX workbook.

Prerequisites

• WinCC V7.0 SP3 or later, or WinCC Professional (TIA Portal) V13 SP1 or later. The legacy PM-OPEN-EXPORT product is officially supported up to WinCC V7.4; for newer versions IndustrialDataBridge is the recommended path.
• Microsoft SQL Server 2008 R2 / 2012 / 2014 / 2016 / 2019 Express or Standard, installed either locally with the WinCC instance or on a dedicated archive server.
• Microsoft Excel 2010 or later on the engineering or operator station that will generate the reports.
• Read access to the WinCC archive database. The SIMATIC HMI SQL user group is created automatically during WinCC installation and is sufficient for read-only access.
• For IndustrialDataBridge: a licensed 6AV6362-2ACxx-0AH0 (single) or 6AV6362-2ADxx-0AH0 (16-point) authorization.
• For DataMonitor: a licensed 6AV6362-2DAxx-0AH0 (3 clients) or 6AV6362-2DBxx-0AH0 (10 clients) authorization plus the DataMonitor server installed on the WinCC server.

Option 1: PM-OPEN-EXPORT (Lowest Cost, Least Flexibility)

PM-OPEN-EXPORT is a compact add-on that bolts onto the WinCC V6/V7 project tree. After installation, two new icons appear in the WinCC Explorer: Export Configuration and Export Viewer. The configuration tool lets the engineer define export jobs that target the TagLogging process value archive, the AlarmLogging alarm archive, or both.

Configuration Steps

1. Install PM-OPEN-EXPORT from the WinCC options DVD or the Siemens A&D Mall download portal.
2. Open WinCC Explorer, right-click the project, and launch Export Configuration.
3. Create a new job, name it e.g. Daily_Report, and select the Tag Archive tab.
4. Add the desired archive tags by clicking Add Tag; the dialog filters WinCC process tags by archive name.
5. Set the time range using absolute or relative definitions. The relative format REL:-1D;REL:0D means "yesterday 00:00 to today 00:00"; the absolute format is ABS:'2024-01-01 00:00:00.000';ABS:'2024-01-02 00:00:00.000'.
6. Switch to the Alarm Archive tab and select up to 16 message classes (e.g. Errors, Warnings, System). The default includes all active classes.
7. Choose the output target. Supported targets are .csv and .xls (BIFF8 format). Set the path to a network share if multiple operators will run the export.
8. Configure scheduling. Options include Manual (operator-triggered), Daily, Weekly, and On Archive Switch (i.e. when WinCC rolls the archive segment).
9. Save and close the configuration. Distribute the project to the runtime via the standard WinCC project duplicator.

Runtime Operation

Operators invoke the export by double-clicking the Export Viewer icon on the WinCC desktop, selecting the configured job, and clicking Execute. For a project with 63 archive tags and 1,300 active alarms, the typical export time on a WinCC server with 8 GB RAM is 4 to 18 seconds for a 24-hour window.

Option 2: WinCC/IndustrialDataBridge (Recommended Mid-Range)

IndustrialDataBridge (IDB) is a configuration-based ETL (extract-transform-load) tool that ships as a WinCC option. It can read WinCC archives, SQL Server tables, OPC DA, and OPC UA sources, and write to ODBC databases, CSV, Excel, and XML. The configuration is stored in a central project and distributed to the IDB runtime service.

Configuration Steps

1. Install IDB on the WinCC server. After installation, launch IndustrialDataBridge Configurator from the Windows Start menu.
2. Create a new Provider (source). For WinCC process values choose the WinCC OLE DB provider. The connection string is auto-populated from the active WinCC project.
3. Create a new Consumer (target). For an Excel file, select the ODBC Consumer and configure a Microsoft Excel ODBC driver (*.xls, *.xlsx). For a relational database, select SQL Server or Oracle ODBC.
4. Define a Group containing one or more Links. A Link is a data path between a source field and a target column.
5. Build the SQL query against the WinCC OLE DB source. The following example returns the last 7 days for a single tag:

SELECT * FROM
(EXEC dbo.TAG_R 'ProcessArchive','TankLevel','2024-01-01 00:00:00.000','2024-01-08 00:00:00.000')
WHERE Value >= 0;

6. Map source columns to target columns. IDB supports type conversion, string formatting, and the REPLACE() / SUBSTRING() functions inside transformations.
7. Define a Trigger. IDB supports time-based triggers (e.g. every 60 seconds), event-based triggers (linked to a WinCC tag change), and manual triggers. For a daily report, set the trigger to 0 0 6 * * ? (Quartz cron) – every day at 06:00.
8. Validate the configuration with Tools > Check Configuration. The configurator reports missing field mappings, invalid SQL, or unreachable providers.
9. Save and Deploy the project to the IDB runtime. The runtime service SINECIDB_Runtime starts automatically and processes scheduled jobs without operator interaction.

Excel Output Configuration Details

To write to a multi-sheet XLSX, the ODBC consumer is configured with the path C:\Reports\Daily_<YYYYMMDD>.xlsx and a sheet name such as ProcessValues$. The Microsoft Excel ODBC driver requires the file to exist beforehand; the recommended approach is a template XLSX with the headers and formatting pre-applied. IDB will append rows below the header row, starting at the first empty cell.

For 63 tags across a 24-hour window with a 1-second acquisition rate, the typical row count is approximately 5.2 million. The Excel ODBC driver is not designed for that volume; in this case, the consumer should be changed to CSV and a separate VBA macro inside Excel can import the CSV. If the project is downsampled to 10-second intervals the row count drops to 544,000, well within the XLSX 1,048,576-row worksheet limit.

Option 3: WinCC/DataMonitor (Web Reports with Excel Add-in)

DataMonitor is the highest-end option and is intended for web-based trending, dashboards, and Excel reports accessible from any browser on the plant network. It installs as an IIS-hosted web service on the WinCC server and includes a Microsoft Excel add-in that exposes the WinCC archive as a queryable data source.

Configuration Steps

1. Install DataMonitor from the WinCC options DVD.
2. Run the DataMonitor configuration wizard. It creates the WinCC DataMonitor website in IIS (default port 80) and the DataMonitorServer service.
3. Create a DataMonitor User with read-only rights to the WinCC project.
4. Open the Excel add-in (DataMonitor tab in the ribbon). Click Login and enter the DataMonitor server URL (e.g. http://wincserver/dm) plus user credentials.
5. Click Insert Trend or Insert Alarm. The add-in places a configurable pivot table in the active worksheet.
6. For static exports, choose Reports > Excel Workbook Templates on the DataMonitor web page. The template is stored on the server at C:\WinCC\DataMonitor\Reports and can be edited in Excel.

Direct SQL Access (Zero-License Option for Engineers)

If a small project has only 63 tags and 1,300 alarm messages, the most cost-effective approach is often to skip the licensed options and query the WinCC database directly with either Excel's Get Data > From Database wizard or a small VBScript. This is feasible when the engineer has read access to the SQL Server instance and basic familiarity with ADO.

Excel "Get Data from SQL Server" Method

1. Open Excel, go to the Data tab, choose Get Data > From Database > From SQL Server Database.
2. Enter the server name YOURSERVER\WinCC and choose Windows Authentication if the user is in the SIMATIC HMI group.
3. In the Navigator, expand the running archive database and select the dbo.ARCHIVE view (or the dbo.MSARV view for alarms).
4. Click Transform Data to open Power Query, filter the date range, and pivot the result.
5. Click Close & Load to insert the data into the worksheet. Set Refresh to "Every 1 hour" for near-real-time updates.

VBScript Method (For Scheduled Reports)

Save the following script as WinCC_Export.vbs and schedule it with Windows Task Scheduler. The script queries the WinCC OLE DB provider and writes a CSV file that Excel can open directly.

Option Explicit
Const adOpenStatic = 3
Const adLockReadOnly = 1

Dim conn, rs, fso, ts, sql
Set conn = CreateObject("ADODB.Connection")
Set rs   = CreateObject("ADODB.Recordset")
Set fso  = CreateObject("Scripting.FileSystemObject")
Set ts   = fso.CreateTextFile("C:\Reports\DailyReport.csv", True, True)

conn.Provider = "WinCCOLEDBProvider.1"
conn.Properties("Data Source").Value = ".\WinCC"
conn.Properties("Catalog").Value    = "CC_OpenArch_03_05_27_14_11_47R"
conn.Properties("Mode").Value       = "Read"
conn.Open

sql = "SELECT * FROM (EXEC dbo.TAG_R 'ProcessArchive','*','" & _
      DateAdd("d", -1, Now) & "','" & Now & "')"
rs.Open sql, conn, adOpenStatic, adLockReadOnly

ts.WriteLine "Timestamp;TagName;Value;Quality"
Do While Not rs.EOF
    ts.WriteLine rs("Timestamp").Value & ";" & _
                 rs("TagName").Value  & ";" & _
                 rs("Value").Value     & ";" & _
                 rs("Quality").Value
    rs.MoveNext
Loop
rs.Close: conn.Close
ts.Close
WScript.Echo "Export complete."

Cost and Licensing Comparison

Selection Decision Matrix

For the original problem statement – 63 archive tags, 1,300 active alarms, user with limited VB experience and no ADO/SQL background – the following decision path is recommended:

Step-by-Step: PM-OPEN-EXPORT Verification (Recommended Baseline)

1. After configuration, save the project and Activate WinCC Runtime.
2. Open the Export Viewer on the runtime desktop.
3. Select the configured job and click Execute.
4. Open the output XLS or CSV in Microsoft Excel. Confirm the following:
   • Row count matches the time range × tag count (no missing rows).
   • Time stamps are in the configured local time zone and the format is yyyy-MM-dd HH:mm:ss.fff.
   • Quality code column is populated with valid values (0x80 = Good, 0x40 = Uncertain, 0x00 = Bad).
   • For alarm exports, the State column toggles correctly between Came In, Went Out, and Acknowledged.
5. If the file is empty, check the Windows Event Log under Applications and Services Logs > Siemens Automation > WinCC > Export for SQL errors.

Step-by-Step: IndustrialDataBridge Verification

1. Open IndustrialDataBridge Configurator, select the deployed project, and click Check Configuration. All checks should report green.
2. Open the IDB Runtime Manager (Start > Siemens > IndustrialDataBridge > Runtime Manager). Confirm the service SINECIDB_Runtime is in state Running.
3. For an immediate test, right-click the group and choose Execute Once.
4. Open the target file. The expected outcome is identical to the PM-OPEN-EXPORT verification above, with the additional check that the trigger schedule is in Scheduler > Next Run at the configured time.
5. Review the IDB log file at C:\ProgramData\Siemens\Automation\IndustrialDataBridge\Logs for SQL warnings or transformation errors.

Troubleshooting Matrix

Performance and Capacity Planning

For 63 archive tags, the worst-case data rate depends on the configured acquisition cycle. Use the following formula to estimate daily row count:

Rows/day = 63 tags × (86400 s / AcquisitionCycle_s) × CompressionFactor

A typical CompressionFactor of 0.10 (i.e. WinCC's built-in compression algorithm stores only significant changes) yields 54,432 rows per day for a 1-second cycle. A 30-day report is therefore 1.6 million rows, exceeding the XLSX row limit. The practical solutions are:

• Switch the consumer target to CSV or a SQL database; XLSX is the binding constraint, not Excel itself.
• Use the WinCC archive compression feature (TagLogging > Properties > Compression) to reduce the effective row count by 70–90 %.
• Aggregate at query time using SELECT AVG(Value), DATEPART(HOUR, Timestamp) FROM ... GROUP BY DATEPART(HOUR, Timestamp) to produce hourly rollups that fit comfortably in XLSX.

For the alarm archive, 1,300 active messages per shift are typical for a mid-sized plant. The alarm long-term archive is split into daily segments; the segment size is approximately 1.5 MB per 10,000 messages. Over 90 days of retention, the expected database growth is 35 MB, well within the SQL Server Express 10 GB limit.

FAQ