Knowledge Base

Dell Business Intelligence Project Using USPTO Data: Episode 7


Links: Enterprise Solutions, Business 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

Prerequisites

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 *.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.
    • Click Create Component Profile.
    • Enter a name and select database menu.
    • Create a profile with the values displayed in Figure 6.


Figure 6.: Option for the Database Profile

  • Enter a Database Profile name.
  • Click the Options tab, select Write as the Execution Type.


Figure 7. Data Elements for the Database Profile

  • Click the Data Elements tab, click Statement.
  • 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.
  • Right-click Fields, then select Add multiple fields.

Figure 8. Add input parameters

  • 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

  • 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.

Field

Description

Database Type

MSSQL 7/2000/2005

User

Username to access the SQL Server database

Password

Password for above user account

Host

SQL Server instance to which you want to connect

Port

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

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:

Field

Description

Database Operation

Enter Name (optional)

Connector Action

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

Profile

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.




Article ID: SLN312607

Last Date Modified: 08/16/2018 05:18 AM


Rate this article

Accurate
Useful
Easy to understand
Was this article helpful?
Yes No
Send us feedback
Comments cannot contain these special characters: <>()\
Sorry, our feedback system is currently down. Please try again later.

Thank you for your feedback.