Use the Data Importer

Last modified: November 1, 2024

Introduction

Data is constantly exchanged between various systems inside and outside an organization. The most commonly used file formats for data exchange are Microsoft Excel and comma-separated value (CSV). These files contain data in a tabular grid of rows, columns, and delimiter-separated values.

This how-to teaches you to do the following:

Create a Data Importer document using a sample representative file (Excel and CSV)
Create a (non-persistable) entity in your domain model
Import data using the custom Import data from file activity

Prerequisites

Download the Data Importer extension from the Marketplace and add it into your app. This module also requires:

Studio Pro 10.4 or above
A file document (for more information, see File Manager)

Data Importer Document

The Data Importer extension allows you to import data from Excel and CSV files directly into your app. Create a Data Importer document to define which columns to import and a non-persistable entity (NPE) to hold the imported data, along with source-to-target mapping. During the Data Importer document creation, you can preview the data and choose which columns you want to import and edit the name of resulting entity.

The Data Importer document can be used along with the Import data from file custom activity. Use this activity in a microflow to import data from an Excel or CSV file.

Creating a Data Importer Document

Right-click the module you want to add the Data Importer document to and click Add other > Data Importer.

Name the document, then click OK, and the new Data Importer document opens.

Previewing Excel Data

Click Select a local file to import an Excel file (.xls or .xslx).

Select or drop the file in the Select Source File field. An Excel workbook can have single or multiple sheets; you can choose which sheet to import data from and specify the header row and starting data row.

Sheet Name – name of the worksheet from where data needs to be imported; if the Excel has multiple worksheets, their names will appear in the dropdown
Header Row No. – row number of the file header; the default is 1
Read Data From Row No. – starting line for reading data; the default is 2

Click Preview Source Data & Entity to view the data from the file. The first 10 data rows from the source file are shown in the data preview section. If there are less than 10 data rows in the sample file, only the available rows are shown. The column names correspond to the attribute name within the entity, and the sheet name is used to define the entity.

All the columns are automatically selected (checked) for import. You can uncheck the columns you do not want to use. At the bottom of the table, you see the target data type of the attribute, which is based on the cell-type defined in the Excel file’s first data row. If any data types are incorrect, check the cell-type of the first data row and adjust the definition accordingly.

Column names that do not adhere to Mendix naming conventions will be autocorrected. For Number cell-types, the target Mendix type is mapped to Decimal to accommodate to integers and decimals.

Previewing CSV Data

Select or drop the CSV file in the Select Source File window. CSV import supports multiple combinations of separator/delimiter, quote, and escape characters. It also supports importing files where the header row is absent.

Specify the values for all four configurations (Delimiter, Quote Character, Escape Character, and Add Header Row):

Delimiter (Separator) – current supported delimiters are comma, semicolon, pipe, and tab; the default is comma
Quote Characters – current supported quote characters are single and double quotes; the default is double quotes
Escape Characters – current supported escape characters are backslash, single, and double quotes; the default is double quotes
Add Header Row – specify if you want to add a header row or if the header row is already part of the CSV file; the default is the header row already included in file

Click Preview Source Data & Entity to view the data from the file. The first ten rows from the source file are shown in the data preview section. The file name is used to define the entity (NPE), but this can be edited. The column names correspond to the attribute name within the entity.

All the columns are selected (checked) by default. You can uncheck the columns you do not want to import. At the bottom of the table, you can see the target data type of the attribute, which defaults to String.

Column names that do not adhere to Mendix naming conventions will be autocorrected.

For example, for the following source data (CSV), the separator is specified as Comma and Quote, and the Escape Character is Double Quote and Header. This is already part of the input file.

The data preview and resulting entity are seen below:

Editing an Entity

You can edit the entity in the Entity Preview section. The Data Importer supports various ways to:

Edit the name of resultant entity
Edit the name of the attribute (or attributes) of the entity
Edit the data type of a given attribute

Click Edit at top-right corner of Entity Preview. This will render a pop-up window where you can change the name of the entity. You can also change the name of the attribute; Original Name is the name of the column from input file and Attribute Name will be the new name that you want to assign to this column. You can also change the data type of this attribute by selecting a relevant value from the drop-down as shown below.

Once you are satisfied with the changes, click OK to save or Cancel to discard your changes.

The Edit Entity feature is useful for CSV import, as all the columns of a CSV file are marked as String by default, so you can change the data type if necessary. The following table shows the source-to-target data conversion matrix:

Input CSV File

Source Type	Target- String	Target- Int	Target- Long	Target- Decimal	Target- Boolean	Target- DateTime
String	Yes	Partial	Partial	Partial	Partial	No

Input Excel File

Source Type	Target- String	Target- Int	Target- Long	Target- Decimal	Target- Boolean	Target- DateTime
String	Yes	Partial	Partial	Partial	Partial	No
Boolean	Yes	No	No	No	Yes	No
Decimal	Yes	Partial	Partial	Yes	No	No
DateTime	Yes	No	No	No	No	Yes

Partial - If source data is valid and within range, it will be converted into the target data type.

Enum is not supported as a target data type
Runtime exceptions can occur if the input data cannot be converted into desired the target data type for various reasons (for example, invalid data, data truncation, casting etc.)

Creating an Entity

When you are done editing the entity, click Create Entity > OK. This will create the entity in your domain model. You will also see a confirmation message that an entity has been created in the domain model and is ready to use.

When the entity is created, you can view the mapping of the source columns to the target entity attributes.

The Data Importer document creation is complete and can be used to import data in a microflow.

Building your App

The newly-created Data Importer document allows you to periodically import data from an Excel or CSV file that is generated by another app or system.

Custom Activity

The Import data from file activity is found under Integration activities in the Toolbox. Double-click to view its properties:

The Input section includes:

File – name of the file from which you want to import data
Data Importer document – the Data Importer document created at the end of the design time flow

The Output section includes:

Return Type – set to the list of NPEs defined in the Data Importer document
Variable name – auto-populated to the EntityName list

Build the Pages

The Import data from file custom activity needs an input file to import data from. The example below builds a page where a System.FileDocument is uploaded and fed to the custom activity.

Open the home page and add a button and name it Upload Customer Data.
Double-click the button and in the Events field under the On click drop-down, select Create object to create a System.FileDocument entity.
Pass the control to a new page (UploadCustomerData) where the file is uploaded.
On the UploadCustomerData page, include a data view for the FileDocument and include a ‘File Manager’ to assist with a file upload.
Open the Toolbox and add a Call microflow button.
Click New and name the microflow Import Customer Data. You also see FileDocument in the parameters section; make sure this box is checked to include it as a parameter and click OK.

Configuring the Import data from file Activity in a Microflow

The steps below are shown using an Excel input file with its corresponding Data Importer document. You can substitute an Excel document with a CSV document to import data from CSV input files.

In the created microflow, drag the Import data from file activity into it. You can find this activity in the Toolbox under Integration activities.
When the Import data from file activity is added into microflow, you see three errors in the console:

To address these errors, double-click the activity and in the File field, choose the input file that is passed from the file upload page to this microflow as a parameter.
In the Data Importer document field, click Select and choose the Data Importer document you want to use.

After selecting the Data Importer document, the Return type and Variable name auto-populates. You can change the name of the output variable if you wish.
Click OK. The custom activity is configured and all the errors will resolve.
Add an Aggregate list activity and configure it to count the size of the ‘CustomerList’, which is returned from the previous activity.
Configure a Show message activity. You can use a template message and a parameter, such as in the example below.
Set ‘$CustomerList’ as the return value from the Import data from file activity to be used later. Your completed microflow should look like the image below.
Deploy your app locally. Browse and upload an input file, which is similar to the file that was used as a template while creating Data Importer document.
Check that you see a message that states Imported xx rows from input file into a list of NPEs.

You have successfully configured and used the Data Importer extension. You can extend this as per your requirements. For example, you can convert the list of NPEs into persistable entities by providing a message definition, or use each loop construct and individually create and commit entities into your database.