TH NextGen - Tag Builder: Data Import

Next to the usual data sources such as your historian, TrendMiner also supports the importation of data from a csv-file.

The data import tags can be found in the tag builder menu. Unlike the other calculated tags the data import tags are not saved in the work organizer.

• Data Import submenu
• File requirements
• Editing imported tags
• Deleting imported tags
• Restrictions
• Example: How to prepare and format a csv-file

Data Import Submenu

The data import functionality requires you to upload a .csv-file containing your data.

File-picker: Click this field to browse and select your local csv-file to be uploaded in TrendMiner. More details on the file formatting can be found in the sections below.

You can find an overview of your uploaded tags by clicking the "imported tags" button instead of the "create new" button in the "tag builder" menu.

Csv-file Requirements 

Your csv-file must be labeled and formatted in a certain way to ensure that TrendMiner reads your data correctly.

• File extension: Provide your file with the proper .csv-extension for importing multiple tags at once.
• Formatting: The file should meet the structure as specified below:
  • Row1: column 2 to 101: tag names as to be shown in TrendMiner. The tag name should be unique. You cannot overwrite an existing tag unless you are the creator of that tag.
  • Row 2: columns 2 to 101: Description optional.
  • Row 3: units of measurement (optional)
  • Row 4: tag type (analog, discrete or string):
    
    • Analog: Used for numeric value, linear interpolation
    • Discrete: numeric value, stepped interpolation
    • Digital: text value, stepped interpolation.
  • Column1: timestamp (e.g. 2022-01-01T15:05:00+01:00)
    • Timestamps default to UTC unless a timezone is provided in the format.
  • Column 2: tag-value
    • This field may be left blank. TrendMiner interpolates only the filled-out timestamps.
  • Separator: , (comma)
• File-size: The file-size of the csv-file cannot exceed 45 MB.
• File encoding: UTF-8

Note: The number of tags a user can upload is unlimited, but only a maximum of 100 tags per file is allowed.

Editing imported tags

Imported tags can be edited by re-uploading a file using the same tag name. Note that this only works if you are the owner of the existing tag you want to overwrite.

Re-uploading a file will overwrite the existing data and will not append any data. If you need to append data you will have to include the previous data in the file again. In case you need to do this regularly you may want to look at setting up a separate data source (e.g. a SQL database) instead of using the import feature.

Deleting imported tags

Unlike the other tag builder functionalities, imported tags are not present in the work organizer. In order to delete your imported tags, you can delete them directly from the provided list when clicking "Imported tags" on the Tag builder overview.

Restrictions

• The file-size of the csv-file cannot exceed 45 MB.
• Only 100 tags can be included per file.
• Deleting calculations tags will only soft delete them. Soft deleted tags will not be available anymore to users, but the tag name is still reserved and cannot be re-used. Only owners of the created tag can delete the tag.
• Imported tags can only be overwritten and not appended.

Example: How to prepare and format a csv-file

The following steps explain how to create a csv-file using Microsoft Excel™. An example file can be downloaded here. Make sure that you use a dot mark (full stop) to separate decimals instead of a comma. This can be done through your region settings and language or directly as a configuration when exporting from platforms like Excel. Alternatively, you can perform a find and replace action in at the end, in a text-editor, to replace the separator and decimal signs.

• Copy your time series data into an Excel file using the formatting described above.

Note: Excel may auto-format some of the cells depending on your local settings. You must ensure the correct format is used to enable the correct handling of data. Deviations from the stated format will result in an incorrect or failed import.

This import option supports time zones. The following examples are valid timestamps:

• 2019-07-30 01:10:30
• 2019-07-30T08:15:30-0500
• 2019-07-30T13:10:30Z
• 2019-07-30T08:15:30-05:00
• 2019-07-30T14:14Z
• 2019-07-30T14:15:55.125Z
• 2019-07-30T14:15:55.125487514Z

Note: Adding a timestamp with no timezone info will be interpreted as UTC.

Note: When importing integer values (like batch IDs), very large numbers (> 16777216) will be subject to rounding issues. We advise to import batch IDs as strings.

1. Select your range of dates (column A).
2. Right click.
3. Select "Format cells...".
4. Select the "Custom" category.
5. Input the custom date format as: 'yyyy-mm-dd hh:mm:ss'
6. Click the "OK" button.

7. Select your range of data (column B to ...).
8. Right click.
9. Select "Format cells..."

10. Select the "General" category.
11. Click the "OK" button.

12. Open the "File" menu.
13. Select "Export".
14. Select "Change file type".
15. Select "CSV (Comma delimited".
    

The resulting file should now adhere to the requirements needed for TrendMiner. The format of your csv-file should look like the screenshot below when opening it in a text editor.