Author: Mayura Deshmukh

Links: Enterprise SolutionsBusiness Intelligence and Appliance Solutions

Episode 7 | Creating schema and uploading CSV file to database

Welcome to Episode 7 of the Dell™ US Patent Office (USPTO) Project. In this episode we focus on loading a simple *.csv file into the database table using Dell Boomi.

                                                                                        Figure 1.      Process overview


Before loading the *.csv file into the database, you will need to accomplish the following:

  • Create a database
  • Design a schema for storing patent information
  • Create the tables in the database

Creating a database

Create the USPTO database in SQL Server 2012 using a create database script, or through the Microsoft SQL Server Management Studio console. Click Connect to Instance> Expand instance, then right-click  Databaseand select New Database from the drop-down list.

                              Figure 2.      Creating a database using Microsoft SQL Server Management Studio

Designing a schema

We will be storing just a few fields from the USPTO XML file within the database. To create reports like “Top 10 patent producing companies for a given year” and “Top 10 patent producing cities for a given year”, we are interested in fields such as patent publish date, inventor details, and company details.

                                                                         Figure 3.      Schema design


Creating tables

Create the tables shown in Figure 3 within the database.


Importing a *.csv file into the database table using Dell Boomi

To complete this process you will need to:

  • Create a process component to use to import the *.csv file into the database
  • Create and operate a disk connection
    • Connection
    • Operation
  • Map *.csv to database table
    • CSV profile
    • Database Profile
    • Map CSV and Database Profile
  • Database connector
    • Connection
    • Operation

How to import a *.csv file into your database

Create a process component

Create a process component. For detailed steps on creating a process component, check Episode 3 of this series.

Creating a disk connection

Episode 3 also explains the process of creating a disk connection and operation. 

                                                                                       Figure 4.      Path for *.csv file

                                                                       Figure 5.      Disk Operation for the *.csv file

Map a *.csv file to a database table

In this section you will create two profiles that Dell Boomi will use to interpret the data, one for a *.csv file and one for a database table. Once these are created, you can create a map component to link the fields in the *.csv file to the fields in the database table.

1.    Create a *.csv profile. For detailed instruction that cover this process, see Episode 3 of this series.

2.   Create a database profile.

a.    Click Create Component Profile.

b.    Enter a name and select database menu.

c.     Create a profile with the values displayed in Figure 6.

                                                                       Figure 6.      Option for the Database Profile

d.    Enter a Database Profile name.

e.    Click the Options tab, select Write as the Execution Type.

                                                               Figure 7.      Data Elements for the Database Profile

f.     Click the Data Elements tab, click Statement.

g.    In the right side window select the Standard Insert/Update/Delete within the Type field, and within the SQL Script field, enter the insert statement. Enter ‘?’ to dynamically insert the *.csv file values.

h.    Right-click Fields, then select Add multiple fields.

                                                                                  Figure 8.      Add input parameters

i.      Enter a Field Name and Data Type for each field. These fields are the fields in your table in which you want to insert data.

                                                                Figure 9.      Data Format for the input parameters


j.     Select Save, and then Close.


Map the *.csv and database profiles

1.    Drag and drop the Map shape into the process. The Map Properties window opens automatically. Enter a label and select the (+) symbol to the right of the Map field.

2.    Enter a Name for the Map, and then select Save.

3.    On the left side of the map, select the   symbol, select profile Flat File, and then select the *.csv profile created in the previous step.

4.    Select OK.

5.    On the right side of the map, select  , select profile Database, and then select the database profile created in the previous step.

6.    Select OK.

7.    Click the Boomi Suggest button, and Boomi will automatically suggest mappings between the two profiles as in Figure 9, or simply drag elements on the left to the corresponding elements on the right to connect as in Figure 10.

                                                                            Figure 10.     Boomi Suggested Mapping

                                                                             Figure 11.     CSV to database mapping

8.    Click Save, and then click Close.

Creating a database connector


1.    Drag and drop the connector shape in to the process field; the Connector Action window opens.

2.    Select the Connector as Database and the Action as Send options to insert data into the database.

Creating a database connector

                                                                         Figure 12.     Creating database connector

Database connection

1.    To the right of the Connection field shown in Figure 11 select the (+) symbol to create a new connection.

2.    Enter the required information as in Figure 12 and Table 1.

                                                                           Figure 13.     Database connector details

                                                                                                            Table 1.        



Database Type

MSSQL 7/2000/2005


Username to access the SQL Server database


Password for above user account


SQL Server instance to which you want to connect


1433 (the port number Dell Boomi uses to connect to the SQL Server

Database Name

Name of the database to which you want to connect

Additional options

;domain=your domain name


Note: Enter the ;domain=your domain name in the Additional Options field if you are using Windows Authentication to access SQL Server. If you are using SQL Authentication, the Additional Options field should be blank.       

3.    Select Save and Close

Database operation

1.    To the right of the Operation field in the Connector Action window select the (+) symbol to create a new operation.


                                                                                   Figure 14.     Database operation


2.    Enter following information:



Database Operation

Enter Name (optional)

Connector Action

Send (default when Send is selected as the Connector Action)


Select the database profile created in previous section

3.    Select Save and Close

4.    Select OK in the Connector Action window to create the connector.

Completing and testing the process

1.    In the Shapes menu click Logic and then drag and drop a Stop shape.

2.    Enter a label and select Ok in the Stop Properties Window.

3.    Connect all the components as shown in the figure below.

                                                                                      Figure 15.     Complete process

4.    Name and Save your process.

To test your process,

1.    Click Run a Test button on the upper right side of the window.

2.    Select the Atom you want to use and click the Run Test button.

                                                                               Figure 16.     Select atom to run test

If there are any errors you can check on the Logs tab for the detailed error message. If the test executes successfully data from the *.csv file will be inserted into the table.

                                                                          Figure 17.     Records imported into table

 Additionally, you would get a notification and your process would appear as shown in Figure 16.

                                                                                        Figure 18.     Testing process

Deploying and executing the process

Please view the how-to video in the blog Boomi Getting Started  to deploy and execute the process.