Excel to RecordList in Outsystems

The requirement is, 

my imported excel will have a dynamic header name, The data type will be the same but the header name can vary.

So when I assign the ExcelTORecordList to my structure, it fails to bind the value as the structure attribute name and column header name do not match.

How can I get records for dynamic header names which are not present in out entity attributes or structure attributes and insert them into an entity?

I don't think this is possible with the Excel to Record List since OutSystems only support Typed structures. I would rather look into options to retrieve your data as csv or json and then parse those with an extension that outputs a Typed structure.

Do you need to keep the header names or will you normalize those in your function? If normalize, perhaps you can use an Azure Logic App (or similar) to change the header names to your normalized values. 

If all that fails then an alternative could be by going back to the business and discuss this problem and agree to a standardized data form.

Hello Aravind,

In case this is the file you are uploading manually then you can change the header names to match the structure attribute names. However it seems that is not the case, then you can go with below approach.

As long as the position/sequence of column is not going to change, then you can maintain a static entity with header names same as in your structure. Then before you perform ExceltoRecordList, you can use another forge component https://www.outsystems.com/forge/component-overview/355/advanced-excel to fetch the header row from excel sheet and then replace the header names with names stored in static entity and then perform ExcelToRecordList

Thanks,

Junaid

Junaid Ahmed Syed wrote:

Hello Aravind,

In case this is the file you are uploading manually then you can change the header names to match the structure attribute names. However it seems that is not the case, then you can go with below approach.

As long as the position/sequence of column is not going to change, then you can maintain a static entity with header names same as in your structure. Then before you perform ExceltoRecordList, you can use another forge component https://www.outsystems.com/forge/component-overview/355/advanced-excel to fetch the header row from excel sheet and then replace the header names with names stored in static entity and then perform ExcelToRecordList

Thanks,

Junaid

Hi Junaid, 

Thanks for your reply,

We can't change the static entity while runtime.

also The link you sent is not working.


Vincent Koning wrote:

I don't think this is possible with the Excel to Record List since OutSystems only support Typed structures. I would rather look into options to retrieve your data as csv or json and then parse those with an extension that outputs a Typed structure.

Do you need to keep the header names or will you normalize those in your function? If normalize, perhaps you can use an Azure Logic App (or similar) to change the header names to your normalized values. 

If all that fails then an alternative could be by going back to the business and discuss this problem and agree to a standardized data form.

"retrieve your data as csv or json and then parse those with an extension that outputs a Typed structure."

How we can achieve this?


Hi,

The first part (retrieve data as...) is a procedure change. That is something that you with your customer/supplier need to discuss. The second part would be something that I would solve with Integration Studio and parsing the data with JSON.net or something.

But I also think that the solution from Junaid would be a much quicker solution. I just tried his link and it's working. You can also search for Advanced Excel on the Forge yourself and it should popup. 

Best, 

Vincent

Aravind M wrote:

Junaid Ahmed Syed wrote:

Hello Aravind,

In case this is the file you are uploading manually then you can change the header names to match the structure attribute names. However it seems that is not the case, then you can go with below approach.

As long as the position/sequence of column is not going to change, then you can maintain a static entity with header names same as in your structure. Then before you perform ExceltoRecordList, you can use another forge component https://www.outsystems.com/forge/component-overview/355/advanced-excel to fetch the header row from excel sheet and then replace the header names with names stored in static entity and then perform ExcelToRecordList

Thanks,

Junaid

Hi Junaid, 

Thanks for your reply,

We can't change the static entity while runtime.

also The link you sent is not working.



Aravind, you need not change your structure which is why I suggested you to modify excel header as per your structure. The forge component I suggested should work, there is one more but it is showing being deactivated which is why I did not suggest

https://www.outsystems.com/forge/component-overview/1044/excel-package

Solution

I found the solution,

Created a structure for that excel attributes and defined record definition as that structure. 

then loop that structure list and insert it in entity.

Solution