Import from MS-Excel to Record List

When designing the flow of your action, you can use the Excel to Record List element to import the content of an Excel file into a Record List; simply drag and drop the Excel to Record List element from the Action Flow Toolbox onto the canvas.

Specify the Excel file, the Excel sheet (optional), and the Record List definition in the Excel to Record List properties. However, the Excel to Record List element does not open the Excel file automatically for you; to obtain the Excel file use the Upload widget.

You may export a Record List content to an Excel file using the Record List to Excel element.

Excel Sheet Format

The Excel sheet must have a specific format and follow the rules described in this section.

The first row must have the name of the attribute where the data of that column is going to be mapped. Columns that don't have an attribute with the same name are not imported, and all attributes that did not map to any column are left with their default values.

When using a combination of Entities and Structures to define Record List records, it may happen to have attributes with the same name in different Entities/Structures. In this case you have to prefix the attributes in the Excel sheet with their Entity/Structure name followed by a colon (':'), or else a runtime error is generated and no data is loaded.
Starting in the second row, there should be the data to be loaded into the Record List: one record for each row. The values in Excel sheet must be compatible with data types of the corresponding record attributes, otherwise a general exception is thrown and no data is loaded.

Importing Algorithm

The values are loaded from the Excel sheet and inserted into a Record List; records are loaded row by row and the attributes set column by column as follows:

If the number of columns match the number of entity attributes (including the identifier), the columns must have the same order as the entity attributes for the values to be set in the records, otherwise a runtime error occurs.
If the number of columns is different from the number of entity attributes, the values of the record attributes are set by matching the attributes name with the column headers (first row). Entity attributes that don't have a match with a column header are loaded with the default value for their data type.

In both cases, a general exception is thrown if data types of the values in Excel sheet are not compatible with data types of the record attributes or the Excel file is encrypted; in these cases no data is loaded.

The Excel sheet that is loaded depends on what is set in the Sheet name property; the expression is evaluated and the following algorithm is applied:

If, from the evaluation, the resulting sheet name exists in the Excel file, the data from that sheet is loaded into the Record List;
If the expression is not defined or its evaluation result is empty:

If there is a sheet called "Sheet1", the data from that sheet is loaded into the Record List variable. All other sheets are ignored;
Else, the content of the first sheet found in the Excel file is loaded into the Record List variable. All other sheets are ignored.

If the evaluation of the expression does not correspond to any sheet in the Excel file, then a general exception is generated and you must handle it.

Excel Data Types to OutSystems Data Types

When importing data from an excel file to an entity of your application module, there is a map between each excel data type and the accepted OutSystems data types to where those values can be assigned:

Excel Data Type	Accepted OutSystems Data Types
Text	Text, Phone Number, Email, Currency
Integer	Integer, Text, Decimal, Phone Number, Email, Currency
Decimal	Decimal, Text, Currency
Boolean	Boolean, Text
Date	Date, Date Time
Time	Time

Handling Runtime Errors

When importing from an Excel file, some errors may occur at runtime and you should design your application to handle them. Learn more about Managing Exceptions.

Following is a list of the runtime errors raised by the OutSystems Platform and an hint on how you can overcome them:

- Ambiguous binding of the '<columnHeader>' column header; prefix it with the entity name and a colon (':'): in the Excel file, the specified column header matches more than one attribute of the record definition. This happens when you are importing data into a record that is defined using a combination of entities and/or structures that have attributes with the same name.

Edit the Excel file and prefix the conflicting attribute name (in the column header) with the entity name separated by a colon (':'). This will disambiguate the attribute.

- None of the column headers match with the record attributes: none of the column headers found in the Excel file match with the attributes of the record definition. There's a mismatch between the attributes in the Excel column headers and the record definition. You may be choosing a wrong Excel file.

Certify yourself that you chose the Excel file with the right content for the record definition expected by the application.

- Found duplicated '<columnHeader>' column header in the Excel file: the described column header was found in more than one column in the Excel file; each column header corresponds to a record attribute and this binding is ambiguous when you have more than one column with the same header name.

Edit the Excel file and make sure each column has its own distinct header.

- No rows with data were found in the Excel file: the Excel file you chose has no rows containing data to be imported. The Excel file must contain column headers in the first row and the data to be loaded in the followings rows.

Check your Excel file and make sure it contains rows with data to be loaded.

Excel To Record List Properties

Property	Description	Optionality	Default value	Obs.
Name	Text that identifies the element in the action flow.	Mandatory		See rules for naming elements.
Description	Free text that describes the element in the action flow. You can edit the text by directly typing it in the property or in the description editor which opens by clicking	Optional		Useful for documentation purposes. The maximum size of this property is 2000 characters.
File Content	Variable that holds the content of the source MS-Excel file.	Mandatory		The type of the variable must be Binary Data.
Record Definition	List of entities and/or structures that defined the structure that you want to load. You can type the name of the entity or structure, use the Record Editor, or select one of the entities and structures displayed.	Mandatory
Sheet Name	Name of the Sheet in the MS-Excel file whose content you want to import to the list. You can directly type the name of the sheet or use the Expression Editor.	Optional