Build an API Task to read data from the database and export the query result into a file stream variable. In this example, we are using the SELECT statement to explain this action.

Note: This action is applicable for both Task Bot and API Task.

Prerequisites

  • Create a file variable as dbFileStream.
  • You must have a valid database server connection.
  • Create a customers database table with the following records.
    cust_id cust_name department baseSalary
    E1001 Mike Williams Sales 1200000
    E1002 John Smith Finance 950000
    E1003 Tom Jones HR 700000
    E1004 Jack Sheryl Sales 1500000
    E1005 Shawn George Operations 650000
  • For more information about how to use API Task, see Example: API Task.

To fetch data from the customers table and export into file stream variable, perform these steps:

Procedure

  1. Log in to the Control Room as Bot Creator.
  2. Create a new API Task.
    1. From the left pane, click Automation.
    2. Click Create new > API Task.
    3. In the Create API Task window, enter API Task name as customer_records.
    4. Accept the default folder location: \Bots\.
      To change the location, click Choose and follow the prompts.
    5. Click Create & edit.
  3. Navigate to Database package and drag the Export to file stream action.
    1. Enter session name as Default.
    2. Choose the SELECT statement option and enter the query. 
      SELECT cust_id, cust_name, department 
FROM customer_records 
WHERE deprtment = 'Sales'
ORDER BY Cust_id
    3. Leave the maximum number of records to retrieve value blank to fetch all the records that meet specified condition.
    4. Enter the timeout value as 120.
    5. Enter file name as Customers.csv.
      Note: This is not a physical file but a reference file name that is used to pass the query result to the file stream variable.
    6. Select the ANSI as CSV file encoding option.
    7. Select the Include column header option.
    8. Specify the file stream variable as dbFileStream to reference the query result.
  4. Click Save.
    When you run this API Task, the Export to file stream action retrieves and exports the following query output into the dbFileStream file stream variable.
    cust_id cust_name department
    E1001 Mike Williams Sales
    E1004 Jack Sheryl Sales