View Categories

Read/write optimized data blocks (DB) of an S7 PLC (Excel, SQL)

Lesedauer: 2 min

Introduction #

How can DataSuite be used with optimized data blocks (DB) of a Siemens S7 PLC?
Welcome to the guide, in this document you will learn how to set up DataSuite with a PLC (S7-1200/S7-1500) and optimized data blocks.
The data is saved in Excel files, but can also be stored in SQL databases

1. Requirements #

1.1 Hardware & Software #

  • Siemens S7-1200 or S7-1500 PLC with activated OPC UA functionality
  • DataSuite software (version 3 or newer)
  • TIA Portal (for PLC programming and configuration)
  • Licensed OPC UA Runtime for the PLC
  • Microsoft Excel (if Excel files are to be used)

1.2 Network Configuration #

  • Profinet connection between PC (with DataSuite) and the PLC
  • Fixed IP address for the PLC
  • Firewall settings for OPC UA (enable port 4840)

2. Activate OPC UA in the Siemens PLC #

As optimized data blocks cannot be accessed via Put/Get, the PLC must act as an OPC UA server.

2.1 Enable OPC UA Server in the PLC #

  1. Open TIA Portal and load the PLC project.
  2. Go to Device configuration → OPC UA.
  3. Activate OPC UA Server and select standard interface.
  4. (Optional) Change port from 4840 to another port.
  5. Configure security options:
    • Activate at least user name/password authentication.
    • (Recommended) Activate encrypted connection.
  6. Save changes and restart PLC.

2.2 Configuring OPC-UA Variables #

  1. Open the desired data block (DB) in TIA Portal.
  2. Drag the desired variables into the OPC UA server interface (right mouse button → OPC UA → Add).
  3. Load the modified program into the PLC.

3. Connecting DataSuite with OPC UA #

3.1 Add SPS connection #

  1. Open DataSuite and go to PLCs.
  2. Click on Create new PLC.
  3. Select PLC – OPC UA as the connection type.

3.2 Enter OPC UA connection parameters #

  1. Enter the IP address of the PLC.
  2. Set port 4840 (or the port set in the PLC).
  3. Security mode: If activated, select an encrypted connection.
  4. Authentication: Enter the user name and password of the PLC.
  5. Test and confirm the connection.

4. Importing variables from OPC UA #

  1. Open the newly created PLC connection in DataSuite.
  2. Click on Manage variables.
  3. Select OPC UA Import and load the data blocks released in the PLC.
  4. If new variables are added in the PLC, import again.

5. Configure Excel file in the DataSuite #

5.1 Creating a new Excel file #

  1. Open DataSuite and go to Excel files.
  2. Click on Add new file.
  3. Create a new Excel file or use a template.

5.2 Set up Excel file for write access #

  1. Enter file path and name (may contain variables).
  2. Set save options:
    • Overwrite (old file is replaced)
    • Continue (new data is appended to existing file)
    • Append number (create a new file with consecutive number each time)
  3. Set data format (XLSX or CSV).

5.3 Saving Data in an Excel File #

  1. Create a trigger in DataSuite.
  2. Select Action: Excel – Write.
  3. Select the desired file and spreadsheet.
  4. Assign the corresponding cells to the PLC variables.
  5. Save and activate.

5.4 Reading Data from an Excel File #

  1. Create a trigger in DataSuite.
  2. Select Action: Excel – Read.
  3. Select the desired file and spreadsheet.
  4. Assign the Excel cells to the OPC UA variables in the PLC.
  5. Save and activate.

6. Alternative: Database connection (SQL) #

If the data is not to be saved in Excel but in an SQL database, DataSuite can also work with the following databases :

  • Microsoft SQL Server
  • MySQL
  • MariaDB
  • PostgreSQL

6.1 Configure Database Connection #

  1. Go to Databases in DataSuite.
  2. Click on Create new connection.
  3. Select the desired database type.
  4. Enter the connection parameters (host, port, user, password).
  5. Test and save the connection.

6.2 Set up database queries #

  • Write data to database → Action: SQL insert
  • Read data from database → Action: SQL-Select

The tables and columns can be linked dynamically with PLC variables.

7. Troubleshooting & optimization #

7.1 Connection not possible? #

    • Check whether the PLC is accessible (ping to IP address).
    • In TIA Portal:
      • OPC UA Server activated?
      • Correct security options selected?
      • Data blocks added to the OPC UA interface?

    7.2 Values are not being updated? #

    • Open the variable view in DataSuite → Are values being read?
    • Triggers configured correctly? (Check conditions)
    • Excel file not open? (Excel locks files when editing)
    • SQL database queries correct? (Test connection)
    Wie findest du den Beitrag?