Text Data Import Wizard

09.03.2021

The Text Data Import Wizard assists you when importing data sets from text files.

The wizard guides you through four steps to complete the import process:

Step 1 - File Settings

Here you specify how the data in the file is organized and formatted. The wizard attempts to determine the correct file settings automatically and provides the number of columns detected using the current settings. Check these settings before proceeding to the next step.

The content of the file is displayed in a preview pane so that you can review the settings. Very large files are not displayed completely, but can still be imported in their entirety.

In the Select saved import schema field (No template). However, you have the option to select a previously saved import schema as a template, adjust the settings and save it on the last screen of the wizard under the same name or a different name.

In the Character encoding field, specify the character set used to encode the data. UTF encoding is detected automatically if it is noted through the use of a byte order mark (BOM) at the beginning of the file. Otherwise, the operating system code page is set as the default. You can usually tell if the setting is incorrect if special characters are displayed incorrectly.

The Orientation field is where you specify whether the data is stored by column or by row.

Note:   Since most text data formats are organized by column, we are covering this particular scenario here. For data organized by row, you can swap "row" and "column" in the following text.

Use the Column delimiter field to specify how the file columns have been separated. Use the Only one delimiter per column option to specify whether the delimiter should be used only once or as often as required. Text tables are often formatted with empty space, such as in the case of a series of space characters and tabs. In this case, select Tab or space as the Delimiter and deselect the option Only one delimiter per column. Some text tables, however, are formatted so that only one tab character is used as the delimiter. Two sequential tabs then indicate an empty field. In this case, select Tab as the Delimiter and select the option Only one delimiter per column.

In the Decimal symbol field, specify the decimal symbol used for the floating point values. Any existing thousands separators will be detected and filtered out automatically.

Under Data Range specify where the actual data in the file starts and ends.

Step 2 - Other Settings

Header Lines and Data Area Tab

Here you specify from which row the Names, Units and Comments are to be read. If you leave a field empty, the particular attribute will not be read. You can specify the same line number for the Names and Units you can specify the same row number if the unit was specified after the name. In this case an additional list box appears in which you can specify exactly which format is in use.

X Data Set Tab

Here you specify whether an X data set is to be read in or calculated. The X data set is then assigned to all data sets to be read in, which are then Y data sets. In a text file that contains time series, for instance, the column with the time values would be the X data set.

Select Specify X data set via data set number if the X data set is present in the file. For the Number enter the column number as it is shown in the header of the Contents table. If every Y column has its own X column, under Use every nth column as an X data set enter the grid in which they appear in the file.

Selecting Custom X data set allows you to calculate an X data set with linearly increasing values. Use this option, for instance, when the X values were not stored in the file, but the sampling rate or sampling interval are known. In this case, the option Import as signal is set automatically. You only need to enter either the sampling interval or the sampling rate which will then automatically calculate the other value. Select from parameter if the attributes required for the calculation are present in the file as named parameters.

In the Timestamp field you can enter the calendar time value which FlexPro can then use to convert from absolute to relative time and vice versa. You can also set from parameter to read the timestamp from the file.

Notes:
If you import the data as text data link objects the Y and X data are always combined as signals.

The data set numbers that you may have to specify on this and other tabs are then identical to the column numbers as shown in the header of the table contents when only the data of a column are assigned to each data set. However, this is not always the case. When reading calendar time values with the date and time, it may be that two columns are assigned to a data set.

Calendar Time Tab

On the Calendar Time tab, enter whether calendar time data appear in the text file, i.e. time values with the date and/or time. In the Format field specify the existing format. In most cases the format is detected automatically. The only time you have to manually adjust the format is when the data cannot be interpreted clearly (e.g. "12-12-12”). The letters used stand for the following: D= day, M= month, Y= year, h= hour, m= minute, s= second. The sequence MMM is the abbreviated text for the month. The text abbreviations of the date format set in the Windows operating system, the German text abbreviations JAN, FEB, MAR, APR, MAI, JUN, JUL, AUG, SEP, OKT, NOV, DEC, the English text abbreviations JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC and the text abbreviations of the selected language are supported. In addition to the short timestamp form, the long form, such as January 1, 2016, is also supported.

For all formats that include seconds the formats display seconds separated with decimal separators and fractional digits, or milliseconds separated with an additional colon. In the Time zone field, specify the time zone in which the calendar time values were recorded.

Select the option Account for Daylight Saving Time if the dates selected for the time zone should account for a possible time change due to Daylight Saving Time.

Parameters Tab

On this tab you can specify as an option how parameters stored in the text file header are to be read in. Click on the add entry symbol of the table to create an entry. You can edit the entry settings by clicking on the respective fields in the table.

In the Type column you can specify how the parameters appear in the file. Complete line Select this option if only the parameter values are present without names and there is one value per line. In this case you can specify the name of the parameter in the Name/Title Line No., otherwise it is automatically given a name. Select Multiple columns, separated by column separators, if the parameter values are arranged in table format. If a title column is present with the parameter names, then enter the number in the Name/Title Line No. field. Otherwise, the parameter is automatically given a name. Select Name-value pair if a parameter with the name and value is present for each row. In the Name-Value Pair Separator field, specify how the name and value were separated. Select the type Name-value pairs, separated by column separator if multiple parameters with a name and value are present for each row. Even in this case you need to specify the name-value pair separator.

In the Data Set No. column, specify to which data sets the read parameters are to be assigned. Select Via column position if the parameters are present in exactly the same number of columns as the data and are to be assigned by column.

Use the fields Line No. From and To to specify the range in the text file from which the parameters should be read. If the parameters are arranged in a table, the title row is not included in this case.

In the Data Type column you can optionally specify the data type in which the parameter is to be imported and saved. If you select (Automatic), FlexPro first attempts to interpret the imported value as a floating point value. If this fails, the parameter is imported as a string.

Additional Settings Tab

If a line starts with a delimiter, the delimiter is ignored if you select the Ignore delimiters at the beginning of a line option. Delimiters and additional text at the end of a line are ignored when you select Ignore invalid columns at the end option. Otherwise, one more column containing void values will be imported.

If you select the Automatically remove quotes option, strings and numeric data enclosed in quotation marks will be imported without the quotation marks. In this case a string may also contain delimiters. If a string is present surrounded by quotation marks, the quotation marks will need to be doubled in the string. During import, the duplicate quotation marks will be discarded. Examples: "A test" is read in as A test. "Unit ""Nm""" is read in as Unit "Nm".

You should select the Allow merging of names option if, for instance, the date and time will be imported as a column, but there are two separate entries "Date" and "Time" in the title row.

The Store equidistant numerical values saving space specifies that linearly increasing or decreasing numerical values are stored parametrically in the form (number of values, starting value, increment). This is often the case with X data, for instance. You should disable this option if measurement data are present that only deviate slightly from linearity, since FlexPro could erroneously interpret them as linear.

The setting Maximum number of characters per line specifies the maximum number of characters to be imported per line.

You can use the Data reduction factor box to reduce the data upon import. The value 2 means, for instance, that only every second line will be imported.

Use the list boxes Floating point type and Integer type to determine the resolution in which FlexPro saves the converted floating point values or integers.

With some file formats, the milliseconds of calendar time columns are not available as fractional digits of seconds, but are instead in separate columns. For example: 1.1.2017 12:00:00; 100 or 1.1.2017 12:00:00; 0.1. In the Specify milliseconds in box specify whether milliseconds are specified in the unit milliseconds or seconds. For instance, if you select Seconds for instance, the value 0.1 is read as 100 ms. If you select Milliseconds then the value 100 is read as 100 ms.

When importing numerical data, all strings that cannot be interpreted as a floating point value are imported as void values. The result of this is that all strings typically used for void values, such as ? or #NV, are interpreted as void values. Some data formats, however, define one or more floating point values as void. If this is the case, you need to enter the relevant values in the Void floating point values field separated with semicolons. The comparison is made numerically, that is, it makes no difference whether you enter, for example, 1E-3 or 0.001.

Use the Empty/void cells list box to specify how empty cells or cells with void values should be handled. If you select Delete, void values will not be imported. In this case you can reduce the number of values in a data set.

Note:  If the number of values in a data set and in an assigned X data set are not equal, the data set cannot be imported as a signal or signal series.

Some text file formats repeat the header information after a certain number of data rows. You can use Pattern to identify void lines to filter these lines of header information out. For this purpose, enter a pattern that must match the line to be filtered out. The "*" character stands for any string of characters and can also be empty. "?" stands for any character. In the input box on the right, enter the number of lines that are to be skipped, including those that match the pattern.

FlexPro mainly recognizes the file format by the file name extension. Particularly in the case of text data, this assignment is not always obvious, however. For instance, the file extensions .txt or .dat are used for a wide variety of formats. If you register several import schemas for the same file extension, then for each data import a list box appears from which you have to select the appropriate schema. You can avoid this by having a string of characters in the first line of the file that clearly identify the data format. The File format identifier field is where you enter a pattern which has to match the line with the version information. Enter this line number in the box to the right of it. The first line of the FlexPro text data format, for instance, includes FlexPro Text Data File Version 5.0 at UTC+1.0. The following pattern would be suitable here: FlexPro Text Data File Version 5.0*.

If a text file only contains a single line, you can specify in the Read scalar values as field whether you want to import them as a data series with one value or as a scalar value.

You can use Automatic data set recognition to specify whether FlexPro should analyze the complete file or only a certain number of rows in order to determine the file format.

Step 3 - Data Set List

Using the settings that you configured on the previous tab, the Text Data Import Wizard analyzes the file and lists all data sets found inside it.

If you select Detect data sets automatically the data sets will be imported in the format that you see displayed in the table. If you save your settings on the next tab as a schema, you can then use it for files that have more or fewer columns than the one that you just selected in order to create the schema.

If you select Assign data sets manually, then the number of data sets that are imported using the schema is pre-defined. On the other hand, it is possible in this mode to specify the settings for each data set separately. Use this mode for more complex cases, such as when importing matrices or calendar time formats that span several columns.  This mode also allows you to import data formats that contain several X data sets, import hexadecimal digits and recalculate imported data. The settings options are as follows:

Column

Description

Active

Here you select the data sets that are to be imported.

No.

The data set number, which cannot be changed.

Name

The data set name.

Unit

The data set unit.

Component

Here you enter whether the data set contains Y, X or Z data.

X No.

The data set number of the related X data set, if the Y component was selected.

Z No.

The data set number of the related Z data set, if the Y component was selected.

Column From – Column To

Here you specify which column area is assigned to the data set. For calendar time or time span values, several columns can be specified which are then automatically aggregated to a calendar time or time span value.

Example: 3 columns with date, time and milliseconds 1.1.2016;12:00:00;100 are read in as 1.1.2016 12:00:00,100.

In the case of numerical data types, several columns are imported as a data matrix instead of as a data series.

Data Type

Here you specify the data type in which the data appears for the data set.

Comments

The data set comments.

Post Processing

This is an FPScript formula that you can use to automatically recalculate the original data during import. Access to the original data is provided using the variable name data.

Example: Double the amplitude

data * 2

Note: You can process several lines in the list at the same time, such as the X no. of several Y data sets. To do this, click on several lines while holding down the CTRL key. Now click on one of the selected lines while still holding down the CTRL key. The input box appears, and what you enter here is copied to all of the selected lines.

Step 4 - Summary

In the Summary field, the wizard shows you once again a summary of your entries, which you should check carefully.

Copy data and store as data sets; Create one data link per data set; Create one data link for the whole file;

In the Data Storage field, specify whether you would like to copy the data or set up a link:

Copy data and store as data sets sets up data sets in the project database that contain the imported data.

Create one data link per data set creates a text data link object for each data set selected which maintains a link to the text file.

Create one data link for the whole file This option sets up one text data link object, which returns all imported data sets as a list.

If in the Data Structure field you select the option Signal, then the X data set is used as the X component in the Y data sets. Otherwise, it is stored as a separate data set and linked with the Y data sets. You should choose this option if the X data are linearly increasing. If this is not the case, you should store the X data set, particularly in the case of files with several Y columns, in order to save storage space. This option is selected automatically if a text data link is created that returns a list as the result. This also applies if you have defined a custom X data set.

If in the Time field you select the option Absolute, the data is imported as absolute time values with date and time; otherwise, it is imported as relative time values.

In the Subfolders field, select the option Per file if FlexPro should store the data in a folder that has the same name as the file. If under Data Storage you selected the option Create one data link for the whole file, then a text data link object is created instead of a folder. The result is that this data link object returns all selected data sets as a list.

The Data Range field is optional and limits the data range imported.

If you want to save the configured settings as a new import schema, select the option Save as Import Schema and enter a name for the schema. From now on the generated file type is available for fast import as a file type in the Import dialog box. In the For file extension(s) field specify the extensions, separated by semicolons, to which you want to link the schema. The extension of the highlighted file is already pre-selected.

The Priority controls the behavior of FlexPro in case several import filters are linked to the same file extension. FlexPro first checks all higher priority import filters and import schemas. When an import filter is an exact match, it is used. If more than one import filter is found, a dialog box is displayed so you can choose the correct one. FlexPro only searches for a lower priority filter if no higher priority import filter is found. The Text Data Import Wizard is registered as a lower priority import filter, which means that it always appears in the list box. Select High priority when you want to link the import schema exclusively to the specified file extension or when you have specified a data format identification pattern that can be used to check the file data format.

Note: The settings you can choose on this page of the wizard under Data Storage and Data Range are not saved in the import schema. You can set these separately in the Import dialog box every time you import files.

Organizing Import Schemas

FlexPro stores import schemas in your personal template database, which you can manage in the dialog box File > Info > Organizer. Here you have the option to copy an import schema to a different database in order to provide import schemas to all users, for instance. You can also export or import a schema as an XML file.

A file-based import schema saved as an XML file can also be used on other computers. To do this you only have to copy the file to the following folder: C:\ProgramData\Weisang\FlexPro\2021\Templates\Import\Schema.

See Also

Data Import

Data Link Object

Share article or send as email:

You might be interested in these articles