Hi all,


Is it possible to import data from Excel file and insert the data into the correspondg entity in the runtime?


I want to create a button "Choose File" then select the Excel File, and the insert the data into an entity but all in the runtime!

Hi Mohamed,


Yes, it's possible.

You can use the native action from outsystems, ExcelToRecordList, and iterate using for each, and, for each record create the new record.

Or, for a customized excel, you can use the Advanced Excel component to iterate for each line from excel reading a specific cell, writing a outsystems record using assign and using that record in a createorupdate.

https://www.outsystems.com/forge/component-overview/355/advanced-excel


Or, if you want make a bootstrap, exists a option to make them.

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Bootstrap_an_Entity_Using_an_Excel_File

Regards

Hi Mohamed,

It's possible. You have the Excel to Record List that allows you to convert an Excel binary into an OutSystems Record List, and then you can cycle this Record List and create records with the processed data.

There's a couple of questions with overviews of the process you'd have to follow, like this one or outright code examples such as this one.

Hi Mohamed,

Yes, it's possible! 

You just need to create the screen, add the widget to upload a file, add a button to submit and in the screen action of that button, you build the logic for that excel using the wdget "ExcelToRecordList" and select the entity in "record definition" and the content in the "file content".

Then you just have to use the output of that widget to create the data in your entity.


Hope that helps,

MP

Miguel Palrão wrote:

Hi Mohamed,

Yes, it's possible! 

You just need to create the screen, add the widget to upload a file, add a button to submit and in the screen action of that button, you build the logic for that excel using the wdget "ExcelToRecordList" and select the entity in "record definition" and the content in the "file content".

Then you just have to use the output of that widget to create the data in your entity.


Hope that helps,

MP


What is the widget to upload a file?

Mohamed Emam wrote:

Miguel Palrão wrote:

Hi Mohamed,

Yes, it's possible! 

You just need to create the screen, add the widget to upload a file, add a button to submit and in the screen action of that button, you build the logic for that excel using the wdget "ExcelToRecordList" and select the entity in "record definition" and the content in the "file content".

Then you just have to use the output of that widget to create the data in your entity.


Hope that helps,

MP


What is the widget to upload a file?

hi,


you can use file upload widgets for this.


Hi Mohamed,

You have this:

Or this:


Best regards,

MP

Pedro Costa wrote:

Hi Mohamed,


Yes, it's possible.

You can use the native action from outsystems, ExcelToRecordList, and iterate using for each, and, for each record create the new record.

Or, for a customized excel, you can use the Advanced Excel component to iterate for each line from excel reading a specific cell, writing a outsystems record using assign and using that record in a createorupdate.

https://www.outsystems.com/forge/component-overview/355/advanced-excel


Or, if you want make a bootstrap, exists a option to make them.

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Bootstrap_an_Entity_Using_an_Excel_File

Regards

Is bootstrap option available for External Database Entities?


No

But, you can make your own bootstrap logic

If you have permissions to write to the table, I think you will have no problem.


1. Create a structure with the excel columns names


2. Add excel file to resources


3. Create the server action converting the excel file from resource, and the Record Definition set as created structure. Iterate the result and for each record, mapping using assign and call CreateOrUpdate function


4. Create a timer calling the created server action and schedule set "When Published" (whenever you publish this will run the timer), you can change the schedule for what you prefer.

Hi,

Adding it to the question acquirement,Is it possible to modify the excel column header before assigning it to the outsystem structure.

My requirement is , my imported excel will have dynamic header name, The data type  will be 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 does not match.

I want to modify the header name and then assign to structure.


Tanuj

Tanuj Agarwal wrote:

Hi,

Adding it to the question acquirement,Is it possible to modify the excel column header before assigning it to the outsystem structure.

My requirement is , my imported excel will have dynamic header name, The data type  will be 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 does not match.

I want to modify the header name and then assign to structure.


Tanuj


Not sure if you can create a structure at runtime? 

Another option might be to have a generic JSON structure, and covert the excel file to JSON and then import into the generic JSON structure. You would then have logic to map the JSON to your entity. 

All being said I haven't attempted this but think it would work.