As well as the usual data sources such as your historian, TrendMiner also supports the importation of data from a csv-file. The csv-format is supported due to its ease of use and construction (e.g. through Excel).
- csv-file requirements
- Preparing csv-files
- Importing csv-files
- Restrictions
- Example of a valid csv-file
csv-file requirements
Your csv-file has to 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:
- row 1, column 2 to 101: tag names
- row 2, column 2 to 101: description
- Row 3: units
- Row 4: type (analog, discrete, string)
- Analog: numeric value, interpolated
- Discrete: numeric value, stepped
- Digital: (typically) text value, stepped
- column 1: timestamps (e.g. 2020-01-20T15:05:00+1.00)
- column 2 - X: tag value
- Values separated by a comma ","
Note: The number of tags a user can import is unlimited.
- Tag names: the tag name should be unique. You cannot overwrite an existing tag unless you are the original creator.
- File size: a CSV-file cannot exceed 45 MB.
- Encoding standard employed is UTF-8
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 by clicking the information icon to the right of "Select a CSV file to import:" under the Data import menu in TrendMiner. 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.
- 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. 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: 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.
- Select your range of dates (column A).
- Right click.
- Select 'Format cells...'.
- Select the 'Custom' category.
- Input the custom date format as: 'yyyy-mm-dd hh:mm:ss'
- 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.
How to import a csv-file in TrendMiner
You can import a csv-file into TrendMiner as described below. Make sure that the requirements described above are fulfilled as any deviations may result in incorrect or failed imports.
- Open the tag builder menu
. - Choose the data import submenu.
- Click the 'Choose file' button.
- Open your csv-file.
- Click the 'Import' button.
If you want to edit an imported tag, you can overwrite it by uploading it again with the same tag name. Editing imported tags is only possible for the creator of the imported tag.
Example csv-file
An example file can be downloaded here.
Note: Excel may automatically change your formatting. Therefore it is important to make sure that the formatting requirements as described above are still correct.
Restrictions
- If you delete an imported tag, you can never import a tag with the same name.