Exporting Siemens S7 Data Blocks to Excel via TIA Portal

Exporting Siemens S7 Data Blocks to Excel via TIA Portal

1. Problem Statement

Engineers regularly need to round-trip runtime data between a SIMATIC S7 CPU and Microsoft Excel: capture all members of a Data Block (DB), edit the values in a spreadsheet, then write the modified dataset back into the same DB without losing the original symbolic structure. STEP 7 / TIA Portal do not provide a native PLC-to-XLSX channel because the S7 firmware family has no file system, no Excel stack, and no Office automation interface. Any DB-to-Excel workflow must therefore pass through a runtime-capable intermediary that can read the PLC process image, serialize it to a comma-separated or tab-separated text file, and reverse the operation on import.

The accepted practice is to use a Siemens HMI panel (SIMATIC Comfort Panel, SIMATIC WinCC Runtime Advanced/Professional, or a SoftPLC edge device) as the data marshalling layer. The HMI holds the tag database that is mapped one-to-one onto the DB members, exposes a scripting host for file I/O, and can publish the CSV over its integrated FTP server for off-line download.

2. Why Direct PLC Export Is Not Possible

S7-300, S7-400, S7-1200, and S7-1500 CPUs execute deterministic cyclic OB1 / OB35 logic and offer no high-level I/O interface. The loadable file system available on S7-1500 (via DataLogs and Recipe functions) writes internal binary or CSV files, but those files are addressed by their recipe number, not by symbolic name, and the operator cannot edit them with Excel prior to re-load without a project-side utility. The conclusion is the same across all firmware versions: any structured DB export requires an external host with a file system and a Windows scripting engine.

3. Reference Architecture

The minimum viable topology consists of four components:

1. SIMATIC S7 CPU — owns the DB. Tag offsets in the DB are the data source of truth.
2. S7 HMI connection — S7COMM or S7-1200/1500 symbolic connection. All DB members are mirrored to HMI tags.
3. SIMATIC HMI runtime — Comfort Panel TP/TP/IB/KP series, or a WinCC Runtime Advanced / Professional station. Provides VBScript / C-Script host and an integrated FTP server.
4. Windows workstation — hosts Excel, opens the CSV exported by the HMI FTP server, edits, saves, and pushes the file back via FTP.

3.1 Data flow

1. HMI button trigger fires a script that reads each mirrored tag in order and writes a CSV row to \Storage Card SD\Recipes\DB_Name.csv.
2. Operator retrieves the file via the Comfort Panel FTP server (default port 21, user ftpuser set in Control Panel → File Browser).
3. Operator edits the CSV in Excel. The first row must remain the header that matches the tag name and the data type from the symbol table.
4. Operator uploads the modified CSV through the same FTP path.
5. Second HMI script parses the file, applies range checks, and writes the validated values back to the mirrored tags. The PLC then sees the updated DB on the next update cycle.

4. Method 1 — WinCC Comfort Panel Script-Based CSV Export

Comfort Panels ship with a VBScript engine that supports the file system object (FileSystemObject) and SmartTags. Create a global function called Export_DB_To_CSV on the HMI:

' VBScript on Comfort Panel — export all mirrored tags of DB50 to CSV
Sub Export_DB_To_CSV()
    Dim fso, file, path, i, row
    Set fso = CreateObject("Scripting.FileSystemObject")
    path = "\Storage Card SD\Recipes\Inputs_DB.csv"
    Set file = fso.CreateTextFile(path, True, True)  ' Unicode

    ' Header row
    file.WriteLine "Tag;Type;Value;Comment"

    ' Iterate over the symbol-mirrored tags. Each tag must already
    ' be configured in the HMI tag list and linked to the DB offset.
    Dim tags(3)
    tags(0) = "Mot_23_Run"
    tags(1) = "Mot_23_Stop"
    tags(2) = "Mot_23_Trip"
    tags(3) = "Mot_23_Ctrl"

    For i = 0 To UBound(tags)
        row = tags(i) & ";BOOL;" & CStr(SmartTags(tags(i))) & ";from DB50"
        file.WriteLine row
    Next

    file.Close
End Sub

Wire the script to a button event (Press → Call function) and confirm the storage card is write-enabled in the Panel Control Panel. The output file is a true CSV with ; as separator because Excel's German/French locale expects a semicolon — if your operator runs Excel in en-US, switch the separator to , or use Tab to keep the file locale-agnostic.

5. Method 2 — FTP Transfer with SIMATIC NET IT-CP

For S7-300 / S7-400 stations without a Comfort Panel, the SIMATIC NET IT-CP (e.g. CP 343-1 IT, CP 443-1 IT) provides an FTP server directly on the Ethernet CP. The CP can be configured to expose a virtual file system that holds CSV snapshots generated by the PLC using standard S7 file I/O (FB 100 — FTP_CMD). The operator downloads the CSV, edits it, and uses the same FB to push the file back to the CP's inbox folder.

The same approach works on S7-1500 with the integrated PROFINET interface using the File handling functions in the Web server or via a custom HTTP handler. See the Siemens function manual SIMATIC S7-1500 S7-Programmierung - Datenkommunikation for the canonical FileWriteC / FileReadC block signatures.

6. Method 3 — Symbol Table and UDT-Based DB Design

The fastest export cycle happens when the DB is already structured to mirror the I/O symbol table. In TIA Portal:

1. Open PLC tags → Default tag table and add one tag per digital input and output with the same symbolic name that appears on the wiring diagram (e.g. Mot_23_Run BOOL I0.0).
2. Create the destination DB (DB50 for inputs, DB51 for outputs) and add members that re-use the exact same names. TIA Portal will display them as Inputs_DB.Mot_23_Run.
3. Drop a simple SCL assignment block in OB1 to copy process image to DB:

   // SCL — OB1, copy inputs to Inputs_DB
   "Inputs_DB".Mot_23_Run  := "Mot_23_Run";
   "Inputs_DB".Mot_23_Stop := "Mot_23_Stop";
   "Inputs_DB".Mot_23_Trip := "Mot_23_Trip";

For repeated equipment (multiple motors, valves, drives), define a UDT (User-Defined Type) once and instantiate it inside the DB as an array:

// SCL — UDT definition
TYPE "UDT_Motor"
  STRUCT
    Run   : BOOL;
    Stop  : BOOL;
    Trip  : BOOL;
    Ctrl  : BOOL;
    Speed : INT;
  END_STRUCT;
END_TYPE

// SCL — DB50 layout
DATA_BLOCK "Inputs_DB"
  STRUCT
    Motor : ARRAY[1..32] OF "UDT_Motor";
  END_STRUCT
END_DATA_BLOCK

With this layout, the HMI script can iterate over Motor[1].Run through Motor[32].Speed using a numeric suffix, and Excel receives a flat 32-row × 5-column table that engineers can pivot, filter, and re-import.

7. Method 4 — Re-Importing the Edited CSV to the PLC

The reverse direction uses the same HMI scripting engine. The script reads the CSV with FileSystemObject.OpenTextFile, validates each row against the HMI tag list, and pushes values via SmartTags(name) = value:

Sub Import_CSV_To_DB()
    Dim fso, file, line, parts, i
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set file = fso.OpenTextFile("\Storage Card SD\Recipes\Inputs_DB.csv", 1, False, -1)

    file.ReadLine  ' skip header
    Do While Not file.AtEndOfStream
        line = file.ReadLine
        parts = Split(line, ";")
        If UBound(parts) >= 2 Then
            On Error Resume Next
            SmartTags(parts(0)) = CBool(parts(2))   ' or CInt / CDbl based on column 1
            If Err.Number <> 0 Then
                HMIRuntime.Trace "Bad value for " & parts(0) & vbCrLf
                Err.Clear
            End If
            On Error Goto 0
        End If
    Loop
    file.Close
End Sub

Bind this script to a Load button and gate it with a confirmation dialog. The PLC picks up the new values through the same HMI tag connection, typically within one cycle (100 ms default on Comfort Panels).

8. STEP 7 Variable Import Wizard (Offline Round-Trip)

If the goal is not runtime data but rather generating or maintaining the DB structure from an Excel sheet, TIA Portal provides an Import command on the PLC tags table and the DB editor:

1. Build a CSV/XLSX with the columns Name, Path, Data type, Address, Comment.
2. Right-click the DB or tag table and choose Import → From file.
3. Map the spreadsheet columns to the wizard's logical fields.
4. Confirm. TIA Portal will create all members in one transaction.

This is the fastest way to bulk-generate hundreds of UDT instances from an engineer's I/O list. The wizard expects the file to be UTF-8 without BOM and uses ; or \t as separator.

9. Verification and Validation

After every round-trip, validate the DB with the following checklist:

1. Open a Watch table in TIA Portal online mode, force all tags from the DB and confirm they match the spreadsheet's source column.
2. Cross-check that the HMI's tag list is online and shows green check marks next to each mirrored tag.
3. Trigger a fresh export from the HMI and diff against the original CSV using a tool such as WinMerge or fc /U.
4. Verify the project compiles after import: any name change in the symbol table will break HMI tag bindings and the compiler will flag unresolved references in the cross-reference view.

10. Troubleshooting Matrix

11. Performance and Capacity Notes

A Comfort Panel script that writes 200 HMI tags to CSV will run in under 250 ms on a TP900 and well under 100 ms on a TP1500. File size on disk is roughly 12 bytes per row for a 32-bit integer including separators, so a 5 000-row recipe occupies < 70 KB and fits easily on the internal flash. The limiting factor is rarely CPU; it is the human review loop in Excel.

For very large DBs (over 50 000 members) use the DataLog function on S7-1500 instead of CSV, or stream the data in chunks of 1 000 rows and use the WinCC Runtime trace buffer to detect any dropped frames.

12. Reference: Manufacturer Resources

• Siemens Industry Online Support — canonical entry point for TIA Portal, WinCC, and SIMATIC NET manuals.
• Weintek FAQ 131 — Double-click to convert DB files to Excel — third-party HMI workflow for DB-to-XLSX file association.