Connector Editor


Creating and Modifying Connectors

Connectors are the data gathering mechanism in Modelshop. Connectors import data from external sources and place it into datalists, while validating, cleansing, and transforming the data as needed. This page documents how to create and modify a single connector for a particular datalist, configuring it precisely for your needs. Modelshop provides an import wizard, which simplifies the process. However, the wizard does not offer access to all of the features that are available in the connector editor. After using the import wizard, you may still wish to refine the connector's configuration as described below.

How to Create a Connector

The purpose of a connector is to feed data into a datalist. You must create a datalist, if it is not defined yet. Click on the down arrow next to the "Create new item" button above the tree view. Screenshot Click on "Data list" in the pop-up menu. The datalist editor will appear in the main panel. Screenshot The datalist name is initialized to "Datalist1". You may change the name to something more appropriate for your data. Just click on the little pencil icon next to the datalist name. After you change the name, click on the "Create new item" button again, and this time select "Connector" from the drop-down memu. The connector editor will appear in the main window. Screenshot The connector name is initialized to the datalist name, followed by "Connector". You may change the name, if you wish, by clicking on the pencil icon next to the name. Then click on the down arrow in the drop-down box labeled, "Connector Type". The options are:

Connector Type Options

Option Purpose
Text File Retrieve data from a text file. This includes data stored as comma separated values (CSV), as well as fixed-length fields.
Excel Retrieve data from an Excel file. Modelshop can process files in Excel 97 - Excel 2003 binary format (xls), and XML-based format (xlsx).
Database Retrieve data from a relational database management system.
XML File Retrieve data from an XML file. A data class and JAXB context are required.
Web Service Retrieve data from a web service.

The Text File Connector

Select the "Text File" option from the drop down list. The settings for text files will appear in the Connector Properties page. Screenshot

Text File Connector Properties

Option Purpose
Load All Data Sources Import the data from all of the files shown in the list of sources. This option usually applies to text file or XML connectors. You may upload as many as desired. All of them will contribute to the datalist.
Load Action Indicates how to reconcile data imported from the source with data that is already stored in the datalist. The options are described in the table below.
Reload updates Automatically import the data whenever the connector's configuration changes, or when not fully loaded.
Add new fields When importing data into a datalist that has defined fields, the connector will create additional fields for columns in the data source that are not already represented in the datalist.
Remove missing When importing data into a datalist that has defined fields, the connector will remove fields that do not match any of the columns in the data source.
Redetect all fields When importing data, the connector will remove all of the existing fields in the datalist, and replace them with the fields detected in the data source.
Keep data types When importing data into a datalist that has defined fields, the connector will preserve the data type of an existing field as long as it is compatible with the type of data in the corresponding column in the data source.
Add file tag The connector will create additional fields in the datalist where it places the name of the file and the line number the data came from. This option is usually utilized together with "Load All Data Sources".
File Type Indicates whether the fields or columns in the file are separated by a special character, or occupy a fixed number of characters.
Delimiter tokens The character or characters that may separate one column of data from the next. Typically, the character is a comma (,).
Quote character The character that distinguishes "quoted" information, which is to be taken completely without regard for its contents. Typically, this either a double quotes ("") or single quotes ('').
Header Row # Designates the first row where the column headers are stored. The first row is number 1.
Num. of rows The number of rows where column headers are stored. The connector begins searching for data following the last row of column headers.
Footer Nom. of rows The number of rows where footer or summary data is stored. The connector ignores the number of rows specified here at the end of each data source.

Load Action Options

Option Purpose
Don't load Do not load any data from the data source when there is already data in the datalist.
Append data Append the data from the data source to the existing data in the datalist.
Merge on key Fill new fields in the exising entities with data from the data source where the designated keys have matching values.
Remove and replace Remove the existing entities from the datalist, and replace them with data from the data source.

After you have configured the options described above, click on "Upload new file". Use the file selection dialog to select the data file. After you click on the Open button, Modelshop will import the file and analyze its contents. The file's name will appear under Sources. Screenshot If you find that you have uploaded the wrong file, you may delete it by clicking on "Delete file". Then click on "Upload new file" to upload another file.

The connector editor provides several pages in addition to the Settings page you have been using up until this point. There are pages named "Fields mapping", "Preview / Analysis", "Source - Connector", etc. Click on "Preview / Analysis". The preview page will appear. Screenshot This page presents a portion of the data from the file you just uploaded in the same way as it would appear in a datalist view. In addition to the preview, there are two other ways to examine the data: raw data and statistics. Click on "File Raw Data" to display the data in its raw form. Screenshot Click on "File Statistics" to display a page of statistical information about the data. Screenshot Each row in this display represents one of the fields or columns from the data source. Each column in the display provides one of the statistical metrics for the fields. The statistics only appear in the places where they are relevant. The rest of the matrix is empty.

Statistical Metrics

Name Purpose
Count The number of rows in the data
Missing The number of elements that are null, blank, or empty
Invalid The number of elements of the wrong type for the field
Sum The sum of the numeric values in the field
Mean The mean of numeric values in the field
Standard Deviation The standard deviation of numeric values in the field
Variance The variance of numeric values in the field
Minimum The minimum value of all the elements in the field
25th Percentile 25% of the numeric values in the field are less than or equal to this value
50th Percentile 50% of the numeric values in the field are less than or equal to this value
75th Percentile 75% of the numeric values in the field are less than or equal to this value
Maximum The maximum value of all the elements in the field
Unique Values The number of distinct elements in the field
Least Common Value The least common value of all elements in the field
Least Common Frequency The number of elements with the least common value
Most Common Value The most common value of all elements in the field
Most Common Frequency The number of elements with the most common value

These tools may help you determine whether or not the data is organized in the way that you expected. They may also provide clues about potentially defective data. If necessary, you may have to adjust the settings in the connector editor and import the data again. If so, then return to the settings page by clicking on "Settings", and modify the parameters as needed. The "Detect Layout" button will change color, indicating that you must detect the file layout again in order to put the new settings into effect. Screenshot If necessary, you may have to modify the data and import it again. If so, then click on "Reload data". You may also upload additional files, if you wish. Just click on "Upload new file", and select another file using the file selection dialog. The connector editor will list all uploaded files under "Sources". Screenshot Since "Load All Data Sources" is not selected, the check box in the Active column indicates which file is currently the one whose data is to be imported. Click on "Preview / Analysis". Now you will see both files listed in the panel to the left of the preview. The active one appears on top. Screenshot You may click on any of the other files listed there, and see a preview of each one. However, if they do not share the same settings, their previews may not appear as you expect. The same thing applies to file statistics. The raw data view will always be accurate.

When you are satisfied that the settings are correct and the data is ready for import, click on the save button in the toolbar. Screenshot Then click on the edit button to exit edit mode. Screenshot Modelshop will display the connector view, which offers the same features as the preview/analysis page in the connector editor. Screenshot To see the datalist view, click on the datalist name in the tree view to the left. Screenshot

The Excel Connector

After creating the connector, select the "Excel" option from the drop down list. The settings for excel files will appear in the Connector Properties page. Screenshot

Excel Connector Properties

Option Purpose
Load All Data Sources Import the data from all of the files shown in the list of sources. This option usually applies to text file or XML connectors. You may upload as many as desired. All of them will contribute to the datalist.
Load Action Indicates how to reconcile data imported from the source with data that is already stored in the datalist. The options are described in the table below.
Reload updates Automatically import the data whenever the connector's configuration changes, or when not fully loaded.
Add new fields When importing data into a datalist that has defined fields, the connector will create additional fields for columns in the data source that are not already represented in the datalist.
Remove missing When importing data into a datalist that has defined fields, the connector will remove fields that do not match any of the columns in the data source.
Redetect all fields When importing data, the connector will remove all of the existing fields in the datalist, and replace them with the fields detected in the data source.
Keep data types When importing data into a datalist that has defined fields, the connector will preserve the data type of an existing field as long as it is compatible with the type of data in the corresponding column in the data source.
Add file tag The connector will create additional fields in the datalist where it places the name of the file and the line number the data came from. This option is usually utilized together with "Load All Data Sources".
File Type Indicates whether the fields or columns in the file are separated by a special character, or occupy a fixed number of characters.
Worksheets, ranges, tables Lists the components of the excel workbook from which you may import the data. Modelshop will populate the drop down list after you upload the file.
Formula Handler Indicates how Modelshop is to process formulas stored in the workbook.
Header Row # Designates the first row where the column headers are stored. The first row is number 1.
Num. of rows The number of rows where column headers are stored. The connector begins searching for data following the last row of column headers.
Footer Nom. of rows The number of rows where footer or summary data is stored. The connector ignores the number of rows specified here at the end of each data source.

Formula Handler Options

Option Purpose
Omit Formulas Ignore formulas. Assume the cells with formulas in them are empty.
Copy Values Evaluate the formulas, if necessary, and copy the resulting values to the datalist.
Copy Formulas Copy the formulas without evaluating them to the datalist. Replace them with the equivalent expressions in groovy, if possible.

After you have configured the options described above, click on "Upload new file". Use the file selection dialog to select the data file. After you click on the Open button, Modelshop will import the file and analyze its contents. The file's name will appear under Sources. Screenshot Click on the drop down list next to "Worksheets, ranges, tables" to see a list of worksheets, named ranges, and tables present in the worksheet. Screenshot You may select any one of these components from which to import the data. The first worksheet in the workbook is selected initially. After selecting the desired component and reconfiguring the settings, if necessary, remember to click on "Detect Layout". Then you may utilize the preview / analysis page to determine whether or not the settings are correct and the data is what you expect. When you are satisfied that the settings are correct and the data is ready for import, click on the save button in the toolbar. Click on the edit button to exit edit mode. Click on the datalist name in the tree view to see the datalist view. Screenshot

The XML Connector

After creating the connector, select the "XML File" option from the drop down list. The settings for XML files will appear in the Connector Properties page. Screenshot

XML Connector Properties

Option Purpose
Load All Data Sources Import the data from all of the files shown in the list of sources. This option usually applies to text file or XML connectors. You may upload as many as desired. All of them will contribute to the datalist.
Load Action Indicates how to reconcile data imported from the source with data that is already stored in the datalist. The options are described in the table below.
Reload updates Automatically import the data whenever the connector's configuration changes, or when not fully loaded.
Add new fields When importing data into a datalist that has defined fields, the connector will create additional fields for columns in the data source that are not already represented in the datalist.
Remove missing When importing data into a datalist that has defined fields, the connector will remove fields that do not match any of the columns in the data source.
Redetect all fields When importing data, the connector will remove all of the existing fields in the datalist, and replace them with the fields detected in the data source.
Keep data types When importing data into a datalist that has defined fields, the connector will preserve the data type of an existing field as long as it is compatible with the type of data in the corresponding column in the data source.
Add file tag The connector will create additional fields in the datalist where it places the name of the file and the line number the data came from. This option is usually utilized together with "Load All Data Sources".
XSD File URL The URL for the schema that describes the XML file's contents.
XSLT File URL The URL for the transformation to be applied before importing the XML data.
Data class name The class name for the object where the data is to be stored after it is imported.
JAXB Context A list of classes required by the JAXB framework so that it can marshal and unmarshal the data.

Configure the settings as desired, and fill in both the data class name and the JAXB context for the kind of data you wish to import. Click on "Upload new file".Use the file selection dialog to select the data file. After you click on the Open button, Modelshop will import the file and analyze its contents. The file's name will appear under Sources. Screenshot As explained above, you may utilize the preview / analysis page to determine whether or not the settings are correct and the data is what you expect. When you are satisfied that the settings are correct and the data is ready for import, click on the save button in the toolbar. Click on the edit button to exit edit mode. Click on the datalist name in the tree view to see the datalist view. Screenshot In this case, the data consists of a single field containing a series of XML data objects. To extract and display data elements from these objects, you will have to define additional fields manually using the datalist editor. These fields will have expressions that reference elements of the XML objects.

Mapping Fields

When you import data, the connector does its best to create fields in the datalist corresponding with the columns in the data source. The connector identifies the type of data stored in each column, and utilizes the column header to construct the field's name. However, the connector cannot foresee everything. For example, suppose you wish to change one of the field names, or you wish to direct the connector to deposit the data in another field the next time it imports data from the same source. How will the connector determine where the data belongs? You may guide the connector using fields mapping.

For this example, let us load the product sales data from a CSV file. After using the import wizard as described above, the datalist appears as shown here. Screenshot Click on the edit button, and the datalist editor appears. Screenshot Change the field names from "Qty" to "Quantity", and from "Amt" to "Amount". Change the data type on the fields that represent monetary amounts from FLOAT to CURRENCY, and delete the format specs for those fields. Click on the save button in the toolbar. Screenshot Click on the connector in the tree view. When the connector editor appears, select "Load All Data Sources", change the load action to "Append data", and select "Keep data types to preserve the modifications made to the datalist". Click on the save button. Screenshot Click on "Fields mapping". Screenshot Here you can see how each of the columns in the data source corresponds with a field in the target datalist. When the field names in the datalist changed, Modelshop automatically adjusted the mapping so that the next time you import from the same data source, the data will go into the correct fields.

Suppose the structure of the data source has changed. The column with the header, "Tax", has changed its name to "State Tax", and there is a new column with the header "Municipal Tax" that contains a municipal tax, where applicable. Add a connector field named "State Tax", and map it to "Tax". Move it up two places by clicking twice on the little up arrow next to the delete button. Click on the save button. Screenshot Click on the edit button to exit edit mode, and click the datalist in the tree view. Screenshot Click on the file upload button in the toolbar above the datalist view. Select the file with the updated information using the import wizard. Screenshot Here you can see that the connector successfully imported the data from the updated excel file. It placed the "State Tax" from the data source in the "Tax" field in the datalist, and automatically created a new field, "Municipal Tax" to store the data from the column with the same name in the data source. You may wish to rename the Tax field, and/or to reposition the new column in the display using the view editor.