Import data from an Excel file

If you want to easily import data from Excel, you can do it in two ways. By uploading an Excel file to Incontrol or linking an online Excel file. Both have their own advantages. In this article, we will use the same example that we discuss in the article Uploading an Excel file.

Uploading an Excel file

By uploading an Excel file to Incontrol, you can select from the connector which tab you want to import data from. You can link the different columns to an alias and specify by which titles you can search and which ones you want to include in the description. Additionally, you can also fill Dynamic content with linked tabs. You can read everything about this in the article Uploading an Excel File. In this example, we assume an import from an Excel file. The import for an Online Excel works the same in terms of settings in the Form Builder.

Using Online Excel

If you use Office 365, you can also save Excel files on your OneDrive and link these to Incontrol. This means that you can update the Excel in real-time, and the link remains active in this way. This allows you to easily add data to the table. Unfortunately, in this variant, it is not possible to fill Dynamic content or search through multiple columns from your table. Also, descriptions cannot be included in the search results.

Importing data from Excel into your form(s)

To be able to import data into your forms, you need input elements with an alias. If you do not yet know what aliases are, you can read everything about them in this article.

Explanation Form Builder

In the Form Builder, you have added all the elements needed to import the different components. In our example, we have added the basic data of the selected Municipality. Below that, we created a section for the Employees of this municipality. Here, we added a dynamic content. Within the employees, there is also the possibility to add equipment with a dynamic content.

For convenience, we have already given all elements the same aliases as our Excel sheets. For example, the field street name has been given the alias street_name. The dynamic content of the employees has been given the alias employees, and the dynamic content for the equipment has been given the alias equipment.

The front-end of our form looks as follows:

In the following steps, you can read how to link the Excel Connector to your form.

• Add a new row to your form
• Click the button to add a new element and select 'Import Connector'
  • You will now see a pop-up:

• • In the Label and Text, you can add extra information as you are used to in the Form Builder.
  • Under Datasource, select the connector you created earlier. In our case, Import Municipality.
  • Enter a Placeholder, so the end-user knows what they can search for. In our case, we entered Search for the municipality you want to import.
  • Under the Alias mapping section, you can determine which aliases you want to import or map to a specific alias from your form. You can read everything about this under Whitelisting.
  • Use the Test button to immediately test the connector and whether the correct data can be imported. Select a value, and you will directly get an overview of the available fields and aliases that you can use. Also useful for mapping your aliases. In our case, we get the following overview.
   
  • As you can see, the test functionality also provides feedback on the linked sheets you may have set up in the connector. With Excel Online, you will not see this.

    • In the example, we selected the Municipality of Amersfoort

    • From the linked sheet Employees, it found three employees

    • From the linked sheet Equipment, it also found the corresponding equipment of these employees.

    • In the example, you see @employees at the top layer with the term :collection (object). This is the given alias for the dynamic content where you want to load the employees. In the employees, you see @equipment for the dynamic content for Equipment.

• In the pop-up, you can also go to Settings to adjust the design of the element.

• When you're finished, you can close the element and test your form.

Note: To use the data import feature, you need a working internet connection on your device.

In our example, the form works as follows:

Whitelisting

Whitelisting (elements)

In the element, under the Alias mapping section, you'll see a dropdown. Here you have 2 choices:

1. Import all aliases: all matching aliases will be imported.
2. Import named aliases only: all aliases named in the box below this dropdown will be imported.

In the text field below this dropdown, you can name the aliases you want to import if you chose the 2nd option. You note this alias as follows: (If you want to add multiple aliases, you must separate them with an enter).

          @alias

Alias mapping (elements)

Mapping aliases is the process of linking the alias in the data source to the alias in the form. You do this as follows:

In this example, a value linked to @alias was in the document. This value is now imported to the place in the form where @new_alias is noted. Again, you can also map multiple aliases, separated by enters:

          @alias1 >> @new_alias1
          @alias2 >> @new_alias2

It's possible to link one alias to multiple aliases in the form:

          @alias >> @new_alias1
          @alias >> @new_alias2

It's possible to link multiple aliases to one alias in the form:

          @alias >> @new_alias1 @new_alias2

Blacklisting

If you want to import a form with many aliases, but only want to exclude a few, there is also a method for this.

1. Choose 'Import all aliases' in the dropdown
2. Assign the aliases you do not want to import a new alias that does not exist in the form

For example:

          @import_alias >> @doesnt_exist

Since the imported alias is assigned a new alias that doesn't exist, the value behind this alias will not be imported.

Using data

To import data into your forms, you need input elements linked with an alias. You cannot directly use the alias from Excel in the text with, for example, @alias. Just as we normally first create an input element with the 'alias' under settings (alternatively, the alias can also be typed at the top right of the element), the same must be done when you want to use data from an Excel cell somewhere in your form. To illustrate this, below is a screenshot of how the municipality name from the Excel file can be used. The import puts the value @municipality from Excel into the input element, after which you can use @municipality anywhere in your form.

However, the input element does not need to be visible in the draft and/or PDF to be used.