Page tree
Skip to end of metadata
Go to start of metadata

About this module

This module shows you how to extract data from a .csv file stored on Dropbox to a SQL Server database, using Universal Platform.
Although this task uses Universal Platform functionality at its most basic level, it is a good example to familiarise you with the functions involved.
So what does this module cover? Well, the scenario is this:

Harmony is a member of the HR team and is required to capture all company staff records in a SQL database. As Harmony does not have any SQL database experience but does know how to work with a .csv file, she is happy to add staff information to a .csv file that is then imported to the database. Harmony is also required to ensure that all changes to staff records in the .csv file are updated in the database.
 
Bob from the IT Department has been asked to facilitate importing the data in the .csv file to the database, and to ensure that the .csv file and database is synchronised with any information updates.
 
To assist Harmony, Bob creates a .csv file named HR.csv and saves it to Dropbox for her. Harmony works from a number of different global locations, so Dropbox is considered a great method of ensuring that she always has access to the.csv file she works with. Bob also lets Harmony know what the structure of the .csv file is, so she knows what staff data is included.
 
Your task is to help Bob by following Module 1 to ensure the data in HR.csv is imported to the database.


Module 1 of this tutorial explains how to configure the methods used to extract data in a .csv file from Dropbox to a SQL database, and successfully import the required data. So in this module:

  • You will start by installing the Dropbox connector.
  • You will then configure login credentials for Universal Platform's Dropbox connector.
  • Once your account is set up, you will connect to Dropbox using the Dropbox API.
  • You will then initiate a connection to a SQL database.
  • Once connected, you will use Universal Platform to create a SQL database table using T-SQL.
  • Next you will configure connectors to enable Universal Platform to access Dropbox, the database and the .csv file.
  • You will create the data models of the .csv file and the corresponding database table.
  • You will then use data formats to map the .csv columns to the SQL table columns.
  • In the Dropbox folder is a file named HR.csv that contains eight rows of HR records. Once the connection and database preparation is complete, you will configure a pipeline to move the data from the .csv file into the database table.
  • You will then use the pipeline to fetch the file and then read the file. Reading the file ensures that you have successfully connected to the file.
  • You will complete the task by querying the database table to ensure the data is in the table.


Now that you are familiar with what we are going to do in this module, let's begin!

 Installing the Dropbox connector extension

The Dropbox connector for Universal Platform establishes a connection to Dropbox using the credentials configured in Universal Platform. Before you can connect to Dropbox, you must download and install the connector extension.
Extensions are .uiz files that are available for download from the Universal Platform store. The store provides access to a range of plug-in's you can use with Universal Platform. 
To download the Dropbox connector extension install file:

  1. In your Internet browser, go to www.universalplatform.com.
  2. Sign in to the Universal Platform website.
  3. Once you have signed in, you are re-directed to the Universal Platform store where you can download the Dropbox connector extension install file.
  4. Find Dropbox on the store home page and click FreeTip:If Dropbox is not displayed on the home page, use the Search function to quickly find it.
  5. Confirm the company or personal information and click NextNote:Although the Dropbox connector is free, you are required to complete company or personal information for licensing purposes.
  6. Confirm the licensing details and click Next.
  7. Review the order information and click Place Order.
  8. Click Download to save the Dropbox connector extension install file, Satellite.DropBox.uiz, to a location on your local computer or network.




Before you continue and install the connector extension, you must ensure that Universal Platform is not currently running on your computer.

  1. Close Universal Manager but do not close the browser.
  2. Check the system tray in your task bar to see if Universal Manager is running on your computer. If the application is running, the UP icon  is displayed. To close the application, right-click the UP icon and select Exit.



  3. Return to Universal Platform in your browser.
  4. Open Universal Console in a browser tab.
  5. Select Update. The Update screen is displayed.
  6. Click Add Package.
  7. Browse to and select the Dropbox connector extension install file, Satellite.DropBox.uiz.
  8. Click Open.



  9. Click Review & Apply.
  10. Click Update. A confirmation message is displayed.
  11. Click Update to confirm adding the connector extension. The extension is added and Universal Platform automatically restarts. The installation results are displayed in the Log for you to review and should complete with no errors. If you receive errors, please check your configuration.






To check that Universal Platform installed the extension and restarted successfully:

  1. Open Interface Manager in a browser tab.
  2. Click Connectors.
  3. Click Add and ensure Dropbox is displayed in the list of extensions. If Dropbox is not displayed, the connector did not successfully install.


 Connecting to Dropbox

The next step is to create credentials for the account you want to use when connecting to Dropbox.
In this tutorial you use a Dropbox account configured for training purposes only. 

 
To connect to Dropbox:

  1. Log out of all Dropbox accounts, including any personal ones.
  2. Open Interface Manager in a browser tab.
  3. Select User Credentials. The User Credentials screen is displayed.
  4. Click Add and select Dropbox OAuth Credential.
  5. On the General tab, enter a name for the Dropbox connection credentials in the format: DropboxLogin_TrainingAccount.
  6. Enter the name you use to login in to Universal Platform in the User Name field in the User Credentials section.
  7. Click Save.



    Next you must authorise your credentials:
  8. Click Authorise in the User Credential Parameters section. The Authorise screen is displayed.
  9. Enter the following authorisation keys:
    • Consumer key: vtfj2nsg8z8fpb3
    • Consumer secret: zfkj9xb5amfqj0x
  10. Click Next.
  11. Once the token request is retrieved, click Authorise to open the Dropbox website so you can log in.



  12. On the Dropbox home page, enter the credentials:

    Note:These credentials are for the Dropbox account configured for this tutorial only.

  13. Click Sign in. A message is displayed notifying you that Universal Platform would like to access files and folders in your Dropbox.
  14. Click Allow to confirm permission for Universal Platform to access your Dropbox.



  15. Go back to the Universal Platform Authorise screen. Your access token verifier is displayed.
  16. Click Finish to verify your access token. You are returned to the User Credentials screen in Universal Platform.



  17. Click Save & Close.
 Connecting to a SQL Server database

It is now time to create a connection to the SQL Server database that you want to import data to. The connection includes the database location, name and login credentials. This database is the data hub for all information you import from external files, such as .csv. 
To connect to a SQL database:

  1. Go to the browser tab that has Interface Manager open.
  2. Select Databases. The Databases screen is displayed.
  3. Click Add and select SQL Server Database.
  4. On the General tab, enter a name for the database connection in the format: DBLogin_UPDemo.
  5. Click Save.
  6. Enter the database connection string in the Connection String field in the Database Connection section: 

    Example: Server=database_server; Database=database_name; User Id=Loginname; Password=Password


  7. Click Test. Your test should complete with no errors. If you receive errors, please check your connection string.
  8. Click Save & Close.




You have now successfully created a database connection! The next step is to add a table to the database

 Creating a database table

Now that you have successfully connected to a database, let's add a table to it using a SQL query. This table will store the data you import from external files, such as .csv. 


To add a database table:

  1. Open Data Manager in a browser tab.
  2. Select Queries. The Queries screen is displayed.
  3. Click Add.
  4. On the General tab, enter a name for the query in the format: CreateTable_DataHub.
  5. Click Save.
  6. Select the database connection you created when you completed the Connecting to a SQL Server database task, from the Default Database Connection list in the Defaults section. Example: DBLogin_UPDemo
  7. Click Save.


    You are now going to configure the query statement to create a database table named DataHub and define the columns in the table. The columns are:

    • EmployeeNo
    • FName
    • LName
    • Mobile_Number
    • DateOfBirth
    • Desk_Phone
    • Mobile_Number_guid
    • LName_Guid

  8. Select the Editor tab and enter the following query, written in the SQL Standard Query Language:

    if object_id('dbo.DataHub') is not null
    drop table dbo.DataHub
    CREATE TABLE [dbo].[DataHub]
    ( [EmployeeNo] [int] NULL,
    [FName] [nchar](40) NULL,
    [LName] [nchar](40) NULL,
    [Mobile_Number] [nchar](40) NULL,
    [DateOfBirth] [date] NULL,
    [Desk_Phone] [nchar](40) NULL,
    [Mobile_Number_guid] [varchar](50) NULL,
    [LName_Guid] [varchar](50) NULL ) ON [PRIMARY]
     
  9. Click Run to execute your query and create database table DataHub.
  10. Click Save & Close.




Next you must check your table is successfully created:

  1. Click Queries.
  2. Click Add.
  3. On the General tab, enter a name for the query in the format: CheckTable_DataHub.
  4. Click Save.
  5. Select the database connection you created when you completed the Connecting to a SQL Server database task, from the Default Database Connection list in the Defaults section.
  6. Click Save.
  7. Select the Editor tab and enter the following SQL query: Select * from DataHub
  8. Click Run to execute your query. The results of this query will not return any records yet as your database table does not contain any data. If your database set up is correct, the query should complete with no errors.
  9. Click Save & Close.


Now that you have set up the Dropbox and database connections, you must configure the connectors that Universal Platform will use to access them.

 Creating connectors

A connector is an access instruction that enables you to open a connection to external systems such as Dropbox, a database or a document, so that you can access data in the external location.
To successfully complete the tasks in this tutorial, you must configure the following connectors:

  • Dropbox – this connector enables you to access files in Dropbox.
  • Database – this connector enables you to access the SQL Server database you want to import data to.
  • Document – this connector enables you to read external files, such as .csv, .xlsx and .xml.
 Creating a Dropbox connector

The Dropbox connector establishes a connection to Dropbox using the account you configured in the Installing the Dropbox connector extensionError! No bookmark name given. task. 
To add a Dropbox connector:

  1. Go to the browser tab that has Interface Manager open.
  2. Select Connectors. The Connectors screen is displayed.
  3. Click Add.
  4. Select Dropbox from the list of extensions. The Connector Details screen is displayed.
  5. On the General tab, enter a name for the connector in the format: DropboxConnector_TrainingAccount.
  6. Click Save.
  7. Select the DropboxLogin_TrainingAccount Dropbox connection you created when you completed the Installing the Dropbox connector extensionError! No bookmark name given. task, from the User Credential list in the Connector Parameters section.
  8. Click Save.



  9. On the Methods tab, click All to enable all the methods. In this tutorial you only need the Get File method, but we will enable all methods for future use.
  10. Click Save & Close. The Connectors screen is displayed and the Dropbox connector you created is available in the list of connectors.



Well done, you have completed the connection setup to Dropbox! Now you can use this connector to get files out of the Dropbox folder.
Next, let's set up the Database connector.

 Creating a database connector

The Database connector establishes a connection to a database using the account you configured in the Connecting to a SQL Server database task.

This connector is automatically used by Universal Platform to populate the database table with data from the .csv file, when you run the pipeline. 
To add a database connector:

  1. Go to the browser tab that has Interface Manager open.
  2. Select Connectors. The Connectors screen is displayed.
  3. Click Add.
  4. Select Database from the list of extensions. The Database Details screen is displayed.
  5. On the General tab, enter a name for the connector in the format: Database_UPDemo.
  6. Click Save.
  7. Select the database connection you created when you completed the Connecting to a SQL Server database task, from the Database Connection list in the Connector Parameters section. Example: DBLogin_UPDemo
  8. Click Save.



  9. On the Methods tab, click All to enable all the methods.
  10. Click Save & Close.

 Creating a document connector

The Documents connector contains a Read Delimited method that enables you to read the contents of a .csv file.

This connector is automatically used by Universal Platform later in this tutorial, to process the Read Delimited function and read the data in the .csv file, when you run the pipeline. 


To add a documents connector:

  1. Go to the browser tab that has Interface Manager open.
  2. Select Connectors. The Connectors screen is displayed.
  3. Click Add.
  4. Select Documents from the list of extensions. The Documents Details screen is displayed.
  5. On the General tab, verify the name of the connector is Documents.
  6. Click Save.



  7. On the Methods tab, click All to enable all the methods. In this tutorial you only need the Read Delimited method, but we will enable all methods for future use.
  8. Click Save & Close.



Right, now that all the required connections and connectors are configured, let's take a quick look at what you have done so far.

 What you've done so far

Let's have a quick look at what you have done so far:

 Creating data models

A data model tells Universal Platform what fields are contained in a file that you want to import data from, and the type of data each field represents, such as a string of text or integer.

Data models also tell Universal Platform what fields are contained in the database table that you want to import data to, and the type of data each field represents.
For example, HR.csv contains the field Employee_Number that is the integer data type, and the database table DataHub contains the field EmployeeNo that is also the integer data type. Both of these fields represent the employee's number. As you can see, the field name in the .csv file does not correspond to the field name in the database table. In order to successfully import the employee number from the .csv to the database table, you need to create a data model for each source and then map the fields in the .csv file to the corresponding fields in the database table. Mapping fields is discussed later, in the Creating a data format task.
To successfully complete the tasks in this tutorial, you must configure the following types of data models:

  • .csv – this data model enables you to specify the fields contained in the HR.csv file.
  • Database table – this data model enables you to specify the fields contained in the DataHub database table.

 Creating a .csv file data model

To create a data model:

  1. Go to the browser tab that has Data Manager open.
  2. Select Data Models. The Data Models screen is displayed.
  3. Click Add.
  4. On the General tab, enter a name for the data model in the format: DataModel_CSV.
  5. Click Save.



  6. Select the Model tab.
  7. Click Add Entity to add a data model to the editor.
  8. Select the New Entity data model block on the editor to view the entity details.
  9. Enter the following:
    • Name: HR
    • Title: HR
    • Type: CSV
    • Owner: Your Name
  10. Click Save. Your entity is renamed to HR on the editor.
  11. Select the HR entity.
  12. Select the Attributes tab and add the following:
    • Employee_Number
    • First_Name
    • Last_Name
    • DOB
    • Phone_Number 

      Note:Attribute names cannot contain spaces or special characters, such as @ and '. If you copy-and-paste the attributes, please ensure that any spaces are removed.
  13. Configure the attributes' Type as follows:
    • Employee_Number: Int
    • First_Name: String
    • Last_Name: String
    • DOB: Date Time
    • Phone_Number: String
  14. Click Save & Close.


 Creating a database table data model

Next you must add the schema for the DataHub database table, and luckily there is a trick for this one: there is a shortcut for adding a database table as a schema. 
To use the shortcut method to create a schema:

  1. Go to the browser tab that has Data Manager open.
  2. Select Data Models. The Data Models screen is displayed.
  3. Click Add.
  4. On the General tab, enter a name for the data model in the format: DataModel_DataHub.
  5. Click Save.
  6. Select the Model tab.
  7. Click Generate.
  8. Select Database Schema. The Generate Model from Schema screen is displayed.
  9. Select the database connection you created when you completed the Connecting to a SQL Server database task, from the Database field. Example: DBLogin_UPDemo
  10. Click Get Schema to view all tables in the database.
  11. Select the DataHub database table that you created when you completed the Creating a database table task, and ensure all the columns you require are selected.
  12. Click Add. The schema is added to the editor.






    Easy! Now you are going to configure the schema attributes for the eight column fields in the database table.
  13. Select the DataHub table schema.
  14. Select the Attributes tab and check the Type properties are as follows:
    • EmployeeNo: Int
    • FName: String
    • LName: String
    • Mobile_Number: String
    • DateOfBirth: Date Time
    • Desk_Phone: String
    • Mobile_Number_guid: String
    • LName_Guid: String
  15. Click Save & Close.

 Creating a data format

When you import data from a .csv file to a database, you use a data format to indicate the relationship of data between the file and the database table.
A data format tells Universal Platform what columns in the file matches the columns in the database table, defined in the data format.
For example, HR.csv contains the data as shown below. Each field header in the .csv file must have a corresponding field in the database table. The field headers defined in the .csv file are:

  • Employee_Number
  • First_Name
  • Last_Name
  • Phone_Number
  • DOB



To configure a data format for importing data from a .csv file to a SQL Server database:

  1. Go to the browser tab that has Interface Manager open.
  2. Select Data Formats. The Data Formats screen is displayed.
  3. Click Add.
  4. On the General tab, enter a name for the data format in the format: DataFormat_CSV.
  5. Select the DataModel_CSV data model you created when you completed the Creating a .csv file data model task, from the Source Data Model list in the Data Format section.
  6. Select the DataModel_DataHub data model you created when you completed the Creating a database table data model task, from the Target Model Data list in the Data Format section.
  7. Click Save.



  8. Select the Format tab.
  9. Drag-and-drop the HR source data entity on to the data format editor. This is the entity you created in DataModel_CSV.
  10. Drag-and-drop the DataHub target data entity on to the data format editor. This is the entity you created in DataModel_DataHub.
  11. Map the .csv fields to the database table columns as follows:
    • DOB >> DateOfBirth
    • Employee_Number >> EmployeeNo
    • First_Name >> FName
    • Last_Name >> LName
    • Phone_Number >> Mobile_Number
  12. Click Save & Close.



Now that you have created the data format, your next step is to configure the pipeline that will import data from the .csv file into the database.

 Configuring the pipeline

A pipeline defines the process and actions for the task you want Universal Platform to perform. Pipelines are defined programmatically using flow-based programming methods.

For example in this module, the pipeline stipulates a Get File function to locate the HR.csv file to process, it then defines the Read Delimited format for data interpretation, defines the Map Data parameters used to correlate data between the .csv file and your database table, and lastly uses the Save function to save the data to your database. 

 
To create a pipeline:

  1. Go to the browser tab that has Platform Manager open.
  2. Select Pipelines. The Pipelines screen is displayed.
  3. Click Add.
  4. On the General tab, enter a name for the pipeline in the format: Pipeline_CSV.
  5. Click Save.

    The editor enables you to program the pipeline. You program your pipeline for the Dropbox connector you created using flow-based programming methods. 
    To begin programming, you must select the Dropbox connector you created:

  6. Select the Editor tab. The pipeline editor may take some time to load, so don't run away!
  7. Enter the name of the Dropbox connector you created when you completed the Creating a Dropbox connector task: DropboxConnector_TrainingAccount, in the filter… field.




    When you search for connectors, the results are displayed in groups - Universal and Connectors. The Universal group displays the system default connectors and the Connectors group displays custom connectors. Since you are looking for the connector you created, you must use the Connectors group.

  8. Click Connectors in the list. A list of functions you can perform with the connector is displayed. To use a function, drag-and-drop it on to the pipeline editor.



    You are now going to configure a Get File function for your pipeline.

  9. Drag-and-drop the Get File function on to the pipeline editor.
  10. Select the Get File function block on the editor to view the function Details and Properties. If the Properties pane is not displayed, click Open Properties.
  11. Enter /HR.csv in the Get File Properties Path field. This is the relative path to where the .csv file is stored on Dropbox.
  12. Click Save.



  13. Click Run to test that your connection to the .csv file is working. Your output should complete with no errors. If you receive errors, please check that the path you entered is correct.



You have now successfully created a pipeline to get HR.csv file from Dropbox! The next step is to read the data in the file.

 Importing data from the .csv file to the SQL database through the pipeline

Now that you have successfully created a pipeline to the HR.csv file on Dropbox, you can read and import the data in the file. 

To read the file:

  1. Ensure you have your pipeline open to the Editor tab.
  2. Enter Read in the filter.
  3. Click Connectors in the list.
  4. Drag-and-drop the Read Delimited document connector on to the pipeline editor.
  5. Select the Read Delimited document block on the editor to view the function Details and Properties.
  6. Ensure that the field delimiter is defined as a comma: ,.



    You now need to join the functions using Bind. When you bind functions, you are specifying that the functions are related so that they are executed in the same process, and in doing that, you bind data output and data input to each step. The line between the function blocks shows the order they are executed in. 
    To bind the functions:

  7. Join the Get File function block to the Read Delimited function block as shown in the image below. The line between function blocks shows the order they are executed in.
  8. Enter Bind in the filter.
  9. Click Universal in the list.
  10. Drag-and-drop the Bind workflow on to the pipeline editor.
  11. Join the functions as follows:



  12. Select the Bind workflow block on the editor to view the function Details and Properties.
  13. Select Files in the Bind Properties Get File and Read Delimited lists.
  14. Click Save.



  15. Click Run to execute your program. The results are displayed in the Output pane for you to review.
  16. Click the Context tab on the Output pane.
  17. Expand the Read Delimited list and select Data. The data output should look as follows: 

    Note:This is a JSON representation of the data and is not in JSON format.



    Right, now that the pipeline is configured to read a .csv file, let's map the data in the file to the database table according to the relationship defined in the DataFormat_CSV data format.

  18. Enter Map Data in the filter… field.
  19. Drag-and-drop the Map Data interface on to the pipeline editor.
  20. Enter Bind in the filter.
  21. Drag-and-drop two instances of the Bind workflow on to the pipeline editor.
  22. Enter Save in the filter.
  23. Click Connectors in the list.
  24. Drag-and-drop the Save database function on to the pipeline editor. You must ensure you select the save function for the database connection you created in the Creating a database connector task, so for Database_UPDemo
  25. Join the functions as follows:



    Looking at joining the functions as above, note that you created the first Bind function when you completed the Configuring the pipeline task. In this task you are working with the second and third Bind functions only. 

    So, starting with the second Bind function:

  26. Select the second Bind workflow block on the editor to view the function Details and Properties.
  27. Select Data in the Bind Properties Read Delimited and Map Data lists.



  28. Select the Map Data interface block on the editor to view the function Details and Properties.
  29. Select the DataFormat_CSV data format you created, from the Data Format list in the Properties section.



  30. Select the third Bind workflow block on the editor to view the function Details and Properties.
  31. Select Data in the Bind Properties Map Data and Save lists.
  32. Click Save.



    Next you must add a save function that imports the read data to the database, according to the attributes defined in the DataModel_DataHub data model.

  33. Select the Save database function block on the editor to view the function Details and Properties.
  34. Select the DataModel_DataHub data model you created when you completed the Creating data models task, from the Data Model list in the Properties section.
  35. Click Save.




    Before we continue, let's look at the program you have created and analyse what it does:




    Now you are ready to run the pipeline and populate your database table with the data in HR.csv.

  36. Click Run to execute your program. The results are displayed in the Output pane for you to review and should complete with no errors. If you receive errors, please check your pipeline configuration.
 Viewing the data imported to the database table

Your last task is to check that the data in the .csv file imported to your database table correctly. 

To do this:

  1. Go to the browser tab that has Data Manager open.
  2. Select Queries. The Queries screen is displayed.
  3. Select the CheckTable_DataHub query you created to view the data in your database table, when you completed the Creating a database table task, from the list.
  4. Select the Editor tab and ensure the following query is displayed: Select * from DataHub
  5. Click Run to execute your query and view the records imported from HR.csv in your database table.
  6. Click Save & Close.

 And that's a wrap

 And that's a wrap - congratulations on completing this module! Before we finish, let's have a quick look at what you have done: 



The next part of this tutorial, Module 2, explains how to ensure that any updates to data in the .csv file are synchronised to the database.

  • No labels