Instructions

The next steps will guide you on how to use this component. In the end, you will be able to insert data into the database using an Excel file.

Requirements:

• To insert data in bulk in the database, it is necessary to know in which table we want to insert the data.
• If the table is in a different catalog than the main catalog used by the application, it is necessary to know its name.
• If the table where we want to insert the data is in an external database, it is necessary to know the name of the connection and the database as well.

General information about Parameters details of BulkInsert action:

• RecordList - Record List | Mandatory
  • List of records compatible with the entity where we want to insert the multiple records. Records must be objects.
•  TableName - Text | Mandatory
  • Physical table name of the entity where we want to insert the multiple data. This name is found in the "Entity" table of the application.
•  ColumnsToIgnore - Text | Not Mandatory
  • When importing from Excel we may want to ignore some columns. To do this, we insert the column name in this field without spaces between them, separating only by commas. When importing, no data will be placed in those columns.
  • In SQL Server, Ids are always created automatically.
  • In Oracle, if we want custom Ids just put them in the imported Excel file.
  • However, if we want to create Ids automatically we have to put the name of the field that contains the identifiers in the columnstoIgnore before creating.
•  ReferenceColumns - Text | Not Mandatory
  • Comma separated list of columns that are of a reference type and should have OutSystems null values converted into database null values.
  • Not used in Oracle and MySQL.
• UseTableLock - Boolean | Not Mandatory
  • Here we have an option to lock the table while someone is entering data. Useful if there are several people working on the same application.
•  Catalog - Text | Not Mandatory
  • Name of the database where we want to insert the data. This input must be filled in when the database is different from the main database.
•  ExternalDatabaseConnection - Text | Not Mandatory
  • Name of the external connection of the database where we want to insert the multiple data. This input must be filled in when the database is accessed through an external connection previously created in the Service Center.

Below is a general demonstration on how to insert data in bulk, using an Excel file, based on our demo.

1. Create the dependencies that allow the usage of the component.

The action of the component allows us to quickly insert large quantities of data records into a database table.

To enable it establishes a dependency in your application according to the picture below.

Manage Dependencies.

2. Insert an upload widget and create the variable to receive the file.Place an Upload widget inside the content where you want to implement the upload feature. Create two variables (one as text, the other as binary data) to fill the inputs of the upload widget. This Upload widget will receive the Excel file, so later we can retrieve it.

See an example below based on our demo.

Upload widget on the screen.

3. Send an Excel file to your database.

Go to the Data tab and create an Entity to store the data, see an example below based on our demo. This entity will be the entity used to insert the data. 

Entity being used.

 Still in the Data tab, create a structure with the same attributes of the entity previously created, as can be seen in the picture below. The purpose of this structure is to support the content transformation from the file to the database.

Structure being used.

Now, go to the Logic tab to start the flow. Create a server action with six input parameters and one local variable, this will allow you to make several changes when uploading data to the database. This server action will import the Excel file and store the data into the database.

Input Parameters and variable detail:

• Excelfile as Binary
• ColumnsToIngore as text
• ReferenceColumns as text
• UseTableLock as boolean
• Catalog as text
• ExternalDatabaseConnectionName as text
• DataSet as Record List of the entity created

See below for an example based on our demo.

ImportSpreadsheetFile action + DataSet detail.

At this point, we are ready to build the flow. First, we want to receive the Excel file and transform it into a record list, to do so, we simply use the ExcelToRecordList widget and fill the inputs with the structure previously created and the variable that holds the uploaded file, as can be seen below.

ImportSpreadsheetFile flow + ExceToRecordList detail.

Now we need to use the ListAppendAll to insert the data received from the ExcelToRecordList into the DataSet, the variable that will be converted into an object to use as input in the BulkInsert action. 

Fill the input parameters as in the screenshot below, based on our demo.

Continuation of ImportSpreadsheetFile flow + ListApeendAll detail.

The last thing to do in order to be able to insert the data in bulk is to get the Physical Table Name, for that we will create another server action. That server action will have an output as text. See below for an example of the server action.

GetTableName action + TableName detail.

Go to manage dependencies on (System) and check the Entity, as can be seen below. This will allow us to get the Entities defined in Service Studio.

Manage dependencies.

On the flow of this action, we want to get the Entity from the system and apply two filters in order to the Physical Table Name. After that assign the current table name to the output of the action.

The filters are:

• Entity.Is_Active = True
• Entity.Espace_Id = GetOwnerEspaceIdentifier() and Entity.Name = "ExampleData"

See below a screenshot based on our demo.

GetTableName flow + entity detail.

Finally, we can finish the first flow created, by dragging and dropping the server action previously created and using the BulkInsert action.Fill in the inputs as can been seen in the screenshot below, so at this moment you may have something like this: 

Continuation of ImportSpreadsheetFile flow + BulkInsert detail.