Excel adapter

Excel adapter allows CADMATIC eShare to access a Microsoft Excel or a CSV file, retrieve data rows from a specific worksheet, and add retrieved data to object properties, use it to categorize objects based on visual styles or object hierarchies, or to create a history graph based on the values.

You need to create a separate adapter for every Excel or CSV file that you want CADMATIC eShare to access. One adapter can have multiple data source configurations, meaning that CADMATIC eShare can retrieve different types of data sets from a single Excel or a CSV file.

If the Excel or CSV file contains hyperlinks to web pages or files in a file system, eShare detects the links automatically. The user can open the links from the object's properties pane.

The supported file types are XLS, XLSX, XLSM, and CSV.

Creating an Excel adapter

Create an Excel adapter to enable CADMATIC eShare to access a specific Microsoft Excel or a CSV file and read data from a specific worksheet. Note that the adapter also reads data from rows and columns that in Excel are set to be hidden or filtered out.

Prerequisites

The system administrator has configured the path to the folder in the application settings. If the system administrator has not configured the path, eShare does not have the Browse button for selecting the file. However, you can enter the path to the Excel or CSV file manually.

Do the following:

Navigate to the project to edit, and then click Project Admin in the main menu. The project administration view opens.
Click Adapters and Data Sources. The Adapters and Data Sources configuration view opens.
In the Adapters section, click Add.
In the Available Adapter Types section, select Excel Adapter, and then click Create adapter.
In the Adapter section, enter a name and description (optional) for the adapter, and specify whether the adapter should be enabled.
In the Adapter Configuration section, specify the following settings.
- Excel file
  - If eShare has the Browse button, click the button to select the file. eShare lists the available folders, Excel, and CSV files. The supported Excel file types are XLS, XLSX, and XLSM.
  - Select the file and click Update.
  - To remove the selected file, click Clear.
  - If eShare does not have the Browse button, enter the location of the Excel or CSV file on the CADMATIC eShare server (local file system path) or on a network disk (UNC path). You can click the Update button to check that the path is correct and to retrieve sample data from the file.
- Worksheet name – Specify the name of the worksheet to use (Sheet2, for example), or leave empty to use the first worksheet of the file.
- First table row number – Specify the row number from which to start reading data.
- Number of data rows to skip in the end – If the data rows to be read are followed by rows that should be ignored, specify the total number of rows to ignore; empty rows are automatically ignored. 0 (default) indicates that all data rows are to be read.
- Has header row? – Specify whether the first data row should be used as a header row.
  - No – The system uses the default column names from Excel (A, B, C...).
  - Yes – The system takes column names from the first data row.
  Regardless of this setting, you can override the default column names with custom names, either in adapter settings or in data source specific settings.
- Is autoreloading? – Specify whether the Excel or CSV file should be monitored for changes.
  - No – The Excel or CSV file is not actively monitored for changes; an administrator must open the adapter settings and click Update to refresh the data.
  - Yes – The system periodically checks the Excel or CSV file for changes and automatically retrieves data if changes are detected. You should select this option if the Excel or CSV file connects to an external data source and automatically reloads data, or if you otherwise expect the file to change often.
- Columns – Optionally, specify the column and category names to display to users.
- Extraction Rule for Column Parts – Select Add new rule to create a new rule for extracting parts of the columns in the Excel file with regular expressions. Only existing columns in the Excel file can be used as source columns. Select the column in Source column and enter the regular expression in Pattern field. Click the Test button to test the pattern against the data found in the Excel file. Multiple extraction rules can be configured, but it is not required to use the rules.
- Define Derived Columns – Define the columns derived with the extraction rules. Select Add new definition to create a new definition for the derived columns. Enter the name of the new column in the Column Name field. Define the format of the new column in Format field. Possible values for the field are the original columns from the Excel file (displayed with the column number as a prefix), column parts extracted in Extraction Rules for Column Parts section, and free text. After configuring all the definitions for the derived columns, select Update Columns.
  
  The new derived columns appear in a separate Derived Columns section below the Original Columns section.
- Revisioning Type – If the Excel or CSV file contains multiple rows that represent different revisions of the same data, set up the revisioning settings to allow only the latest revision to be imported. First, select the type as either "Major/minor" or "Custom list" and then the other settings as appropriate.
- Revisioning Column – Select the column that contains revision numbers.
- Data – Displays the assigned column names and sample data rows retrieved from the file; click the Update button of the Excel or CSV file field to re-import the data, if needed, and check that data is retrieved as expected.
Click Save.

Related Actions

To use the data source for assigning values to object attributes, see Creating an Excel object attributes data source.
To use the data source for creating visual styles or object hierarchies, see Creating an Excel categorization data source.
To use the data source for creating a history graph, see Creating an Excel historical data source.

Creating an Excel object attributes data source

An object attributes data source retrieves data from an Excel or CSV file and displays it as additional object attributes in the 3D model. From the user's perspective, these attributes are just like any other attributes stored in the 3D model, only their values can change dynamically according to data in the external file.

In the data source settings, you specify which Excel or CSV file column to map to which model attribute; these mappings function as object identifiers, and data is retrieved from the file only if object has all the mapped attributes.

In addition, in the data source settings you can define filters that require specific values to exist in the Excel or CSV file; data is retrieved from the file only if matching rows contain the values specified with filters.

Prerequisites

The columns in the Excel or CSV file have been named so that you know which attribute they can be mapped to.
To create a data source for Smart Points, the required Smart Point Type has been created as described in Creating a Smart Point type, and the Smart Point Type has the External ID option set to ‘Yes’.

Do the following:

Navigate to the project to edit, and then click Project Admin in the main menu. The project administration view opens.
Click Adapters and Data Sources. The Adapters and Data Sources configuration view opens.
On the Adapters list, click the Excel adapter to which to add the data source.
In the Data Sources section of the adapter settings, click Add data source.
Select Excel Object Attributes Data Source, and click Create data source.
In the Data Source section, specify the following settings:

Name – Enter a name for the data source.
Description (optional) – Enter a description for the data source.
State – Specify whether the data source is disabled or enabled (default).

In the Groups allowed to see the data section, select user groups that should be allowed to see the data that this data source provides.
- If no groups are selected, only administrators will see the data.
- Add user groups with Add.
- Remove user groups with the remove button.
- If the All Users group is selected, other group selections will be ignored.
The Adapter Configuration section is read-only.
In the Data Source Configuration section, specify the following settings:

Provide Data for – Select whether to create the mapping to model objects or Smart Points.
- Model Objects – If selected, the mapping between an excel file column and a model attribute needs to be specified. Multiple mappings can be specified. All the mappings must match a model object and an Excel or CSV file row to establish a link.
- Smart Points – If selected, the Smart Point Type and the column ID for linking row data to Smart Points need to be specified. The value in the column must match the external ID of the Smart Point to establish a link.
Mappings – Click Add Mapping to map an Excel or CSV file column to an object attribute.
Data Filters (optional) – Click Add Filter to specify that only rows that contain a specific column value will be considered; select a column, and specify the value to look for.

Example: If column 1 of the Excel file is mapped to the Pipeline attribute, column 2 is mapped to the System attribute, and a data filter specifies that the value of column 4 must be ‘PL’, an object retrieves attribute data from the Excel only if the object has the attributes Pipeline and System, and if the value in column 4 of the Excel file's matching row is ‘PL’.

Columns
- You can specify a data source specific Display Name for each column.
- You can specify a data source specific Display Category for each column.
- Clear the ‘Is Visible?’ check box if you do not want a specific column to be displayed as an object attribute in the 3D model viewer. To select or deselect all columns, click the ‘Is Visible?‘ check box in the header row.
Allow Search Queries – Select Enabled if the data source can be used in search queries.

If search queries are enabled, define Maximum Number of Data Source Queries, which determines the number of queries the data source can be included in. The field can be left empty for no limit. The default is Unlimited.

Click Save.

Results

When the user selects an object in the 3D view and the object's attributes match the data source settings, the property pane displays additional attributes using data retrieved from the Excel or CSV file specified in the Excel adapter.

Creating an Excel categorization data source

Add a categorization data source to an Excel adapter to allow objects in the 3D model to be categorized based on data retrieved from Excel or CSV file. When such categorizations exist, the user can select a suitable category from the visual style or hierarchy drop-down menu of the Model tab, and then view objects that relate to the given category.

In the data source settings, you specify which Excel or CSV file column to map to which model attribute; these mappings function as object identifiers, and data is retrieved from Excel or CSV file only if object has all the mapped attributes. In addition, you can define filters that require specific values to exist in the Excel or CSV file; data is retrieved from the file only if matching rows contain the values specified with filters.

In the categorization settings you can use the values retrieved from the file to display each value as a separate category or you can group values to create fewer, more appropriate categories.

Prerequisites

The columns in the Excel or CSV file have been named so that you know which attribute they can be mapped to.

Do the following:

Navigate to the project to edit, and then click Project Admin in the main menu. The project administration view opens.
Click Adapters and Data Sources. The Adapters and Data Sources configuration view opens.
On the Adapters list, click the Excel adapter to which to add the data source.
In the Data Sources section of the adapter settings, click Add data source.
Select Excel Categorization Data Source, and click Create data source.
In the Data Source section, specify the following settings:

Name – Enter a name for the data source.
Description (optional) – Enter a description for the data source.
State – Specify whether the data source is disabled or enabled (default).

In the Allowed for User Groups section, select the Is Allowed check box of the user groups that should be allowed to see the data that this data source provides.

If no groups are selected, only administrators will see the data.
If the All Users group is selected, other group selections will be ignored.

The Adapter Configuration section is read-only, it displays the adapter configuration.

Next, specify the Data Source Configuration settings.

Caching – This setting defines if data source categorization is always fetched from the data source or if a cached categorization is used.
- Disabled – (default) – The data source categorization is always fetched from the data source.
- Enabled – The data source categorization is fetched from the data source when eShare is started or when a data source is configured. After that caching is renewed in intervals defined in Caching Interval field. The value is given in minutes.
Visual Style defines whether the categories created by this data source configuration are to be displayed in the visual style drop-down menu.
- State – When this setting is enabled (default), the user can select the category from the visual style drop-down menu, and the 3D view highlights objects with value-specific colors.
- Conflict resolution – This setting specifies where an object should appear if it matches several categories: in the first category, the last category, or the special Multiple Categories category.
Hierarchy defines whether the categories created by this data source configuration are to be displayed in the hierarchy drop-down menu.
- State – When this setting is enabled (default), the user can select the category from the hierarchy drop-down menu, and the Models tree lists objects in attribute value specific nodes.
- Conflict resolution – This setting specifies where an object should appear if it matches several categories: in all categories, the first category, the last category, or in the special Multiple Categories category.
Queries define the data that must be found in the Excel or CSV file for categories to be created. Click Add query and then specify the following settings.
- Mappings – Click Add Mapping to map an Excel column to an object attribute (their values must match for the link to be established).
- Data Filters – Click Add Filter to specify that only rows that contain a specific column value will be considered; select a column, and specify the value to look for.
- Value column – Select the Excel or CSV column from which to take values for creating the categorization. The name of the column will be the default name of the category.

Categories defines how the data is categorized. The values in the Value column must match exactly the values found in Excel or CSV file, unless you use value ranges or regular expressions as described below.

The Case sensitivity setting specifies if the value column in the defined categories is expected to be case-sensitive when matching the values to the found values. Case sensitivity does not affect fields that use ranges or regexes (they are always case sensitive). For categorizations created in earlier releases, case sensitivity is disabled by default.

The Show to user setting specifies what categories are displayed to the user in the visual style and hierarchy drop-down menus.
- If set to 'Listed categories', the user will only see the categories that are defined in this view.
- If set to 'All categories', the system will create a category for each value that is not defined in this view, and the user will see both manually configured and automatically generated categories. Because these dynamic categories are created on-the-fly and not saved, the color that is assigned to them in visual style might change if the values change.
First, you can click Refresh if you want to see the categories that are created based on the queries you just defined. Then, edit the categorization as needed.

In the simplest scenario, the values retrieved from Excel or CSV file are used as categories as is. Optionally, you can enter a suitable Display Value for each category, define the category color (only relevant if the categories are used as visual styles), and specify the relative order of the categories.

You can specify the same Display Value to multiple Excel or CSV values, to include the values in the same category.
Multiple Categories settings will be used for objects that fit in multiple categories.
Uncategorized settings will be used for objects that do not belong to any category.

Or, you can use the Value field to manually define the categorization as follows.

Enter a specific value.
If the value is not present in the Excel or CSV file, clicking Remove Unused or Refresh will remove the category.
Enter the equals sign (=) and a specific value.
Clicking Remove Unused or Refresh will keep the category, even if the value is not present in the Excel or CSV file.
Define a range of values that will be included in the same category.

Define the range using the format from ... to. The first value (from) is included in the range, but the second value (to) terminates the range and is not included in it. Either value can also be omitted to leave one end of the range open. (Spaces around the three dots will be ignored.) Examples:

a ... m matches the letters from a to n

0 ... 3 matches values such as 0, 1.5, and 2.999

... 2017-10-15 matches any date before 2017-10-15

2017-10-15 ... matches 2017-10-15 and any date after it
Define a regular expression that matches the required values. Start and end the regular expression with the slash character (/). Examples:

/[ABC]\d{1,4}/

/2017-10-\d+/

Click Save.

Results

The user can select data source specific visual styles and/or object hierarchies from the Model tab of the 3D model viewer.

Creating an Excel historical data source

A historical data source retrieves history data for objects or groups from an Excel or CSV file and displays it as a history graph in the properties pane of the object or group. From the user's perspective, these attributes are just like any other attributes stored in the 3D model, only their values can change dynamically according to data in the external file.

Prerequisites

The columns in the Excel or CSV file have been named so that you know which attribute they can be mapped to.
To create a data source for Smart Points, the required Smart Point Type has been created as described in Creating a Smart Point type, and the Smart Point Type has the External ID option set to ‘Yes’.

Do the following:

Navigate to the project to edit, and then click Project Admin in the main menu. The project administration view opens.
Click Adapters and Data Sources. The Adapters and Data Sources configuration view opens.
On the Adapters list, click the Excel adapter to which to add the data source.
In the Data Sources section of the adapter settings, click Add data source.
Select Excel Historical Data Source, and click Create data source.
In the Data Source section, specify the following settings:

Name – Enter a name for the data source.
Description (optional) – Enter a description for the data source.
State – Specify whether the data source is disabled or enabled (default).

In the Allowed for User Groups section, select the Is Allowed check box of the user groups that should be allowed to see the data that this data source provides.

If no groups are selected, only administrators will see the data.
If the All Users group is selected, other group selections will be ignored.

The Adapter Configuration section is read-only, it displays the adapter configuration.

Next, specify the Data Source Configuration settings.

Provide data for – Select whether to create the mapping to model objects or Smart Points.
- Model Objects – If selected, the mapping between an excel file column and a model attribute needs to be specified. Multiple mappings can be specified. All the mappings must match a model object and an Excel or CSV file row to establish a link.
- Smart Points – If selected, the Smart Point Type and the column ID for linking row data to Smart Points need to be specified. The value in the column must match the external ID of the Smart Point to establish a link.
Mappings – Click Add Mapping to map an Excel or CSV file column to an object attribute.
Data Filters (optional) – Click Add Filter to specify that only rows that contain a specific column value will be considered; select a column, and specify the value to look for.

Example: If column 1 of the Excel file is mapped to the Pipeline attribute, column 2 is mapped to the System attribute, and a data filter specifies that the value of column 4 must be ‘PL’, an object retrieves attribute data from the Excel only if the object has the attributes Pipeline and System, and if the value in column 4 of the Excel file's matching row is ‘PL’.
Time Column – Select which column's values are used as the time value for the graph, and whether the value is represented in Local time or UTC.
Data Column – Select which column's values to use as the data value for the graph. The data can be numeric or enumerated.
Display Name (optional) – You can specify a custom display name for the graph.
Display Category (optional) – You can specify a custom display category for the graph.
Unit (optional) – You can specify a data unit for the graph.
Is Enum – If set to Yes, and data in the configured data column is enumerated type, the Detected Enum Values table will list the distinct values found in the data column. The fetching and detecting of these values is not done automatically and the Refresh button has to be pressed every time a new type of enum values appear or older ones disappear from the data column.

The data in the Detected Enum Values table can be modified by dragging the values to the desired level. The enum values will be presented in the same order in the resulting graph's y-axis as they are in the table. Meaning that the top most value in the table will be the highest value in the graph and the last value in the table will be the lowest value in the graph.
Click Save.

Results

The user can view a graph of history data in the properties pane for the object or group in the Model tab of the 3D model viewer.