How to upload an excel file with data and insert in table?

Hi,

Please help. How to do an upload file in outsystems and insert data from uploaded file in table? 

Hi,

You can bootstrap the file or you can have a page where you can validate first the data before inserting it to you entity.


Bootstrap an Entity using Excel File

Upload Widget


When you use upload widget you can check if the file format of the uploaded file is excel then you can simply use the Excel to Record List just make sure the structure you are using is similar to the content of the excel file

Hi Christian,


Good Day.

You can use "EXCEL PACKAGE" from FORGE, I've created a simple process on how to use it. Hope this will help. Using this process you can manipulate what flow you want as you can read each cells.




Rogelio Guanlao wrote:

When you use upload widget you can check if the file format of the uploaded file is excel then you can simply use the Excel to Record List just make sure the structure you are using is similar to the content of the excel file

Hi Rogelio,


I am planning to use an entitiy that is generic that is not the same with the file header. Is it possible?


Reymar Esguerra wrote:

Hi Christian,


Good Day.

You can use "EXCEL PACKAGE" from FORGE, I've created a simple process on how to use it. Hope this will help. Using this process you can manipulate what flow you want as you can read each cells.




Hi Reymar,


I am planning to use an entitiy that is generic that is not the same with the file header. Is it possible?


Jeffrey Vergara wrote:

Hi,

You can bootstrap the file or you can have a page where you can validate first the data before inserting it to you entity.


Bootstrap an Entity using Excel File

Upload Widget


Hi Jeffrey,


I am planning to use an entitiy that is generic that is not the same with the file header. Is it possible?


Hi,

The excel must to have the headers with the same name of the table, so you can right-click on the table that you would like to up the data


To use a different header, you have to create a structrure with the same names of the headers, and on Assign statement choose the origem and destiny fileds

Hi,

Depends exactly on what you want to do... I will explain the three typical scenarios: 

  1. If your Excel data has no equivalent representation in OutSystems then you can go to the Data tab in Service Studio, right-click on Entities->Database and select "Import Entities from Excel...". This will create a new entity (or entities) that map to the Sheet(s) you have on your Excel file, and will use the info on the Excel file to bootstrap those entities with data.
  2. If you already have the entities and just want to bootstrap data, you can go to the Data tab in Service Studio, right-click on your Entity definition and select Advanced->"Create Action to Bootstrap Data from Excel...". This will create an Action and associated Timer to bootstrap those entities with data from the Excel file.
  3. If you want to dynamically import data when clicking on a button, I'd suggest:
    1. using the Popup_Upload RichWidgets web screen (as the target of a Popup_Editor) to get the Excel file, and then
    2. in the Notify action use the Excel To Record List tool to transform an Excel sheet into a list of Structure instances and process it.

A shortcut to implement the 3. scenario logic is to follow the instructions on the 2. scenario to let the platform generate the bootstrap action and any required structures and then remove the generated timer and the Excel file in the Data->Resources folder, and adapt the generated action to perform the import and any extra logic required in 3.2.

Cheers,
Nuno Verdsca

Additional Information, please see the follow documentation:

Cheers,
Nuno Verdasca

Christian Michael wrote:

Reymar Esguerra wrote:

Hi Christian,


Good Day.

You can use "EXCEL PACKAGE" from FORGE, I've created a simple process on how to use it. Hope this will help. Using this process you can manipulate what flow you want as you can read each cells.




Hi Reymar,


I am planning to use an entitiy that is generic that is not the same with the file header. Is it possible?


Yes, it is possible. Using the method I sent. You just need to explore the flow for you to fully utilize the "EXCEL PACKAGE".


Leandro Correa wrote:

Hi,

The excel must to have the headers with the same name of the table, so you can right-click on the table that you would like to up the data


To use a different header, you have to create a structrure with the same names of the headers, and on Assign statement choose the origem and destiny fileds

Hi Leandro,

My headers from the source is too long for example, see below screenshot. How will I handle it in the structure that I created since the rule is to have a structure equal to the header name of the excel?



Hi Christian, Wow :O ! 

The attributes are limited by 25 characteres!

Using this automatic bootstrap is required that the attributes match with the excel header, unfortunately.

Maybe another Forge Component help you, like 

https://www.outsystems.com/forge/component-overview/636/csvutil or others to manipulates Excel sheets.


Best Regards,

Leandro.