The Excel File Import Wizard helps you import data sets from Microsoft Excel XLS, XLSX or XSLM file formats.

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. 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.

The Sheet field is where you select the Excel worksheet you want to import. Alternatively, you can import all sheets. The Orientation field is where you specify whether the data is stored by column or by row.

Note:   Since the data is usually 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.

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 file that contains the 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.

Calendar Time Tab

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 Excel file header are to be read in. Click on the icon under 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.

Enter a parameter name in the Name column. Alternatively, you can enter a cell range from which to read the name in the Row from column, in the to column and in the Column. If the range includes multiple rows, multiple parameter will be read. In the Value: Row and Column columns, specify the cell from which the parameter value will be read.

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) the data type is detected automatically.

In the Data Set No. column, specify to which data sets the read parameters are to be assigned.

Additional Settings Tab

The option 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.

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.

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. 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.

If a 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 Excel File 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 for 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 and recalculate imported data. The settings options are as follows:

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 shows you a summary of your input, 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 data link object for each data set selected, which maintains a link to the Excel file.

•Create one data link for the whole file sets up one 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 Excel 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. The data of all imported spreadsheets will be placed in the same folder. If you select the Hierarchical option, then a subfolder will be created in the folder for the file for each spreadsheet unless you select Create one link per sheet under Data storage.

The Data Range field is optional and can be used to limit the data range that you want to import. This is only possible when the file contains an X data set.

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.

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 Excel File Import Wizard is registered as a lower priority import filter, which means that it always appears in the list box. Select High Priority if you want to link the import schema exclusively to the specified file extension. You can also specify whether the schema should be saved in the open project database or in the personal template database.

Note: The settings you can choose 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 saves the import schemas in the open project database or in your personal template database, which you can manage in the dialog box under 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