The Export to file stream action retrieves data from a database using a SELECT statement or Stored procedure, exports the query result into a file stream variable for efficient handling and sharing across automation tasks.

You can use the Export to file stream action for both Task Bot and API Task to handle files directly from a database. For more information, see File streaming using file variable.

To automate the task of reading data from a database and exporting it into a file stream variable, follow these steps:

Procedure

  1. Enter the name of the session you used to connect to the database server in the Connect action. For more details, see Using Connect action for database.
  2. Select one of the following options to read data from the database.
    • SELECT statement
    • Stored procedure
    Using SELECT statement
    1. Enter the SELECT statement to specify the column and table names.
      This field supports SQL syntax. For example, SELECT CustomerName,City FROM Customers
      Note: CTE (Common Table Expression) is supported using WITH keyword in the SQL compliant databases such as Oracle and MySQL. For example, 
      WITH customers_in_usa AS (SELECT CustomerName, state FROM customers WHERE country = 'USA') 
SELECT cName FROM customers_in_usa WHERE state = 'LA' ORDER BY CustomerName
    Using Stored procedure
    1. Enter the name of the stored procedure.
    2. Optional: Click Add parameter to provide an input parameter or configure an output parameter.
      Select from the following parameter options:
      Options Parameters and description Supported data types
      Input Parameter is passed from the bot to the stored procedure.
      • Name: Enter the name of the parameter from the stored procedure.
      • Value (optional): Enter a value or select a variable.
      • Type: Select the required data type from the list.
      For example, if running the example function from the Manage stored procedure action, configure the following two input parameters and values for the sum_of_two function to add:
      • Parameter 1: num1, 5, TINYINT
      • Parameter 2: num2, 10, TINYINT
      • BIGINT (number)
      • BINARY (Boolean)
      • BIT (number)
      • CHAR (string)
      • DATE (date time)

        Supported format yyyy-mm-dd

      • DECIMAL (number)
      • DOUBLE (number)

        Default choice for decimal values.

      • FLOAT (number)
      • INTEGER (number)

        Uses 4 bytes to store an integer with a value from -2,147,483,648 to 2,147,483,647

      • LONGVARBINARY (Boolean)
      • LONGVARCHAR (string)
      • NUMERIC (number)
      • REAL (number)
      • SMALLINT (number)

        Uses 2 bytes to store an integer with a value from -32,768 to 32,767

      • TIME (date time)

        Supported format hh:mm:ss

      • TIMESTAMP (date time)

        Supported format yyyy-mm-dd hh:mm:ss.f, where f is fractional seconds

      • TINYINT (number)

        Uses 1 byte to store an integer with a value from 0 to 255

      • VARBINARY (Boolean)
      • VARCHAR (string)
      Output Parameter is passed from the stored procedure to the bot.
      • Name: Enter the name of the parameter from the stored procedure.
      • Type: Select the required data type from the list.

      When the automation runs, the action converts the database data type to an Automation 360-supported data type.

      For example, if running the example sum_of_two function from the Manage stored procedure action, configure the output parameter: sum, TINYINT.
      Input/Output Parameter can be used for both input and output.
      • Name: Enter the name of the parameter from the stored procedure.
      • Value (optional): Enter a value or select a variable.
      • Type: Select the required data type from the list.
  3. Enter the maximum number of records that you want to retrieve.
    You can limit the results of the execution.
    Note: If you do not specify the maximum number of records to fetch value, then this action retrieves all the records.
  4. Optional: Enter a timeout value.
    When the specified time expires, the statement execution stops even if the execution is not completed.
    Note: If you do not specify the timeout value, then this action waits for the query execution to complete. Otherwise, it encounters an error message if the timeout occurs.
  5. Enter a valid file name with .CSV extension to which you want to export the query result.
    Note: This is only a reference file name to pass the query result to file stream variable.
  6. Select one of the following CSV file encoding options.
    • ANSI: Default option. Uses the system’s default code page for encoding. Suitable for English and Western European languages without special characters.
    • UNICODE: Supports a wider range of characters (UTF-16). Suitable for data containing multilingual text or extended character sets.
    • UTF8: Unicode Transformation Format (8-bit). Widely supported across platforms and tools (Excel, Notepad++, etc.). Provides smaller file size compared to UNICODE.
  7. Select whether you want to include the column headers to the query result.

    Without column headers (Default selection)

    Manny Pittsburgh
    Kate Los Angeles
    John Boston

    With column headers

    CustomerName City
    Manny Pittsburgh
    Kate Los Angeles
    John Boston
  8. Specify the file stream variable to store the query result.
    You can either create a file stream variable using the Create variable option or select an existing file stream variable from the drop-down list.
  9. Click Save.
    When you run an API Task with this action, the SELECT statement or Stored procedure fetches data from the database and saves the query output into the specified file stream variable.