Excel2RecordList Outsystems11

Hey guys,

I'm using outsystems 11 and I want import the data from Excel file so i looking for the documentation for excel2RecordList, i have to know what rules of mapping excel columns to entity attributes. Where i could find this documentation?

Naren wrote:

https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Logic/Implementing_Logic/Web_Logic_Tools/Excel_To_Record_List-You can find out here.

Or
You can find one of the  thread of Excel To RecordList which might be useful
https://www.outsystems.com/forums/discussion/26480/import-excel-file-and-update-my-table-records/


Hi Naren,

Thanks for your reply.

However there is nothing about how the OutSystems mapping excel columns to entity attributes. For example if the number of excel columns is not equals the number of entity attributes, more than or less than what will happen in each situation?  Also what will happen if each entity attributes name is not equals each columns name? What kind of error will be throw ?

The Excel to record list will create a list of records according to an entity or a structure definition. If your Excel do not exactly conform to an existing entity structure and you want to do a specific mapping you can do the following:

1. Create a structure that matches your Excel format and headers (under the data tab, right click on the structures folder and select Add Structure). Be careful with decimals and integers that have blank cells. I find it best to always import all my excel data as text and then convert it in step 4 below. (See this article on the reasons this might be a good idea)

2. Create your server action containing the Excel to record list. Create a record variable of the target entity type. Assign the structure you created in step 1 as the Record definition of the Excel to record list action.

3. Create a for each loop with the Excel to record list output as the record list.

4. Use an assign action to map the current record to your entity variable. If you used text as type for all attributes of the structure, then you use type conversions when mapping (TextToInteger, TextToDecimal, etc.). This means that you can map columns with different header names, skip assigning some columns from the Excel or calculate some values.

5. Add the CreateEntity action with your entity variable as source and loop back to the ForEach block

6. Connect your Server Action to a button, call it from a screen action or set up a timer depending on what you wish to use it for.

I hope this helps you to do what you need to have done.

Solution

Hey DING,

Excel to RecordList requires a record definition(Structure of data you want to load)

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

Solution

Mattias Rundberg wrote:

The Excel to record list will create a list of records according to an entity or a structure definition. If your Excel do not exactly conform to an existing entity structure and you want to do a specific mapping you can do the following:

1. Create a structure that matches your Excel format and headers (under the data tab, right click on the structures folder and select Add Structure). Be careful with decimals and integers that have blank cells. I find it best to always import all my excel data as text and then convert it in step 4 below. (See this article on the reasons this might be a good idea)

2. Create your server action containing the Excel to record list. Create a record variable of the target entity type. Assign the structure you created in step 1 as the Record definition of the Excel to record list action.

3. Create a for each loop with the Excel to record list output as the record list.

4. Use an assign action to map the current record to your entity variable. If you used text as type for all attributes of the structure, then you use type conversions when mapping (TextToInteger, TextToDecimal, etc.). This means that you can map columns with different header names, skip assigning some columns from the Excel or calculate some values.

5. Add the CreateEntity action with your entity variable as source and loop back to the ForEach block

6. Connect your Server Action to a button, call it from a screen action or set up a timer depending on what you wish to use it for.

I hope this helps you to do what you need to have done.


Hi Mattias,

Thanks for your reply.

I know how to do that, just have no idea about what will happen if client upload an excel file with unexpected columns and how could I determine that excel file only have expected columns no more or less .

Naren wrote:

Hey DING,

Excel to RecordList requires a record definition(Structure of data you want to load)

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

Hi Naren,

Thanks for your reply.

Yes that’s what I want to get clear.

Tomorrow I will verify your answer and think about mark your reply as solutions! Thank you very much.