CSV Tool

Owned by Stonebranch
Last updated: Nov 28, 2023
12 min read

Disclaimer

Your use of this download is governed by Stonebranch’s Terms of Use, which are available at https://www.stonebranch.com/integration-hub/Terms-and-Privacy/Terms-of-Use/.

Version Information

Template NameExtension NameExtension Version
ue-csv-toolCSV Tool1.0.0

Refer to Changelog for version history information.

Overview

This Universal Extension is a tool to process input data in Comma-Separated Value (CSV), Delimited Separated Value (DSV), or Tab Separated Value (TSV) formats. It is typically used when input data need to be read, filtered, and possibly saved into new files or transformed into JSON format.

Key Features

FeatureDescription
Read, Filter, Save and Transform CSV data

Read Input CSV/DSV/TSV input files with the capability to perform row filtering and

  • Save data on a new file of the same format.

  • Transform Data into JSON (as EXTENSION Output)

Support for multiple data formats

Universal Task enables users to select well-known input data formats (known also as dialects) to parse and process Input data. The dialect list is the following:

  • Excel CSV

  • Excel TSV

  • Unix CSV

A “Custom” option also exists to provide full flexibility.

Requirements

This integration requires a Universal Agent and a Python runtime to execute the Universal Task.

AreaDetails
Python VersionRequires Python of version 3.7.  Tested with the Universal Agent bundled Python distribution (python version 3.7.6).
Universal Agent

Both Windows and Linux agents are supported:

  • Universal Agent for Windows x64 Version >= 7.1.0.0

  • Universal Agent for Linux Version >= 7.1.0.0

Universal ControllerUniversal Controller Version >= 7.1.0.0

Supported Actions

Action: Process CSV/DSV/TSV File

Read Input CSV/DSV/TSV input files with the capability to perform row filtering and:

  • Save data on a new file of the same format

  • Transform Data into JSON (as EXTENSION Output)

Row Filtering is performed against the values of a specific column. For more information refer to Extension Parameter and Fields.

Configuration examples

User Scenario: Process a CSV file by selecting the UNIX CSV input data format, and using the system's default encoding.



User Scenario: Process a TSV (Excel TSV file format), filter rows based on column data, save the data on a new CSV file, and send data on Extension Output (JSON format).



User Scenario: Process a DSV file by providing additional parsing details through the “Custom“ Input Data Format option., filter rows based on column data with a regex, save the data on a new DSV file, and send data on Extension Output (JSON format).


Action Output

Output Type

Description (Successful Execution)

Example (Successful Execution)

EXTENSION

The extension output follows the standard Extension Output format, providing:

  • “exit_code“, “status“, “status_description“: General info regarding the task execution.

  • “invocation” > “fields”: The task configuration used for this task execution.

  • “result” > “csv_to_json”: JSON representation of CSV/DSV or TSV data. Each element of the list represents a row of data.


{
	"exit_code": 0,
	"status": "SUCCESS",
        "status_description": "Task executed successfully.",
	"invocation": {
        "fields": { ... },
    },
	"result": {
          "csv_to_json": [
              {
                "name": "John Smith",
                "company": "John INC."
              },
              {
                "name": "John Doe",
                "company": "John INC."
              },
              {
                "name": "Joe Pesci",
                "company": "Hollywood"
              }
          ]
	}
}

Description (Failed Execution)

Example (Failed Execution)

The extension output follows the standard Extension Output format, providing:

  • “exit_code“, “status“, and “status_description“: Will be set according to the specific error that happened during the execution. A list of possible values is provided in the extension exit codes table in this document.

  • “result“ > “errors“: If additional error details can be retrieved when the error occurred, they are provided in this JSON element.

{
	"exit_code": 1,
	"status": "FAIL",
        "status_description": "Task execution failed.",
        "changed": False,
	"invocation": {
        "fields": { ... },
    },
	"result": {
        "errors": ["Sample Message"]
	}
}

STDOUT

The CSV, DSV, or TSV data are printed in human-friendly tabular format for visibility purposes (e.g. when filtering is applied and the user tests the task before saving it in production).

| name       | company
-------------+----------
| John Smith | John INC.
| John Doe   | John INC.
| Joe Pesci  | Hollywood

Extension Parameters & Fields

Field

Type

Default Value

Required

Description

Introduced in Version

Action

Choice

Process CSV, DSV or TSV File

Yes

The action performed upon the task execution. Available actions are as follows:

  • Process CSV, DSV or TSV File

1.0.0

Input File Path

Text


Yes

The path to the input file that is to be processed.

1.0.0

Input File Encoding

Choice

System Default Encoding

Yes

The mode for selection of Input File Encoding. Two modes are available

  • System Default Encoding

  • User Defined

When “System Default Encoding” is selected the default Operating System encoding is used.

1.0.0

Encoding

Text


Optional

The encoding used by the input file. Supported encodings can be found in the official 3.7 python documentation.

Available when Input File Encoding = “User Defined”

1.0.0

Input Data Format

Choice

Excel CSV

Yes

The input data format. There are some ready-to-use formats (also known as dialects) as well as a “Custom” option which provides full flexibility.

  • Excel CSV

    • The delimiter used is Comma (,).

    • The quote character used is double-quote (“).

    • The line separator used is CR+LF (Windows).

    • Fields containing line breaks (CR+LF), double quotes, and commas
      are enclosed in double-quotes.

    • If a double-quote appears in a field, the double-quote itself is doubled.

  • Excel TSV

    • The delimiter used is Tab (\t).

    • The quote character used is double-quote (“).

    • The line separator used is CR+LF (Windows).

    • Fields containing line breaks (CR+LF), double quotes, and commas
      are enclosed in double-quotes.

    • If a double-quote appears in a field, the double-quote itself is doubled

  • Unix CSV

    • The delimiter used is Comma (,).

    • The quote character used is double-quote (“).

    • The line separator used is LF (UNIX).

    • The escape character used is Backslash (\).

    • Fields containing line breaks (LF), double quotes, and commas
      are enclosed in double-quotes.

    • If the quoting character appears in the field, it is prefixed with the escape character.

    • If the escape character appears in a field, it is prefixed with itself.

  • Custom

    • The delimiter can be specified with the “Delimiter“ field.

    • The quote character can be specified with the “Quote Character“ field.

    • The line separator can be specified with the “Line Separator“ field.

    • The escape character can be specified with the “Escape Character“ field.

    • The handling of quotes and escape characters can be specified with the “Handling of Quotes and Escape Character“ field.

    • The quote mode can be specified with the “Quote Mode“ field.

1.0.0

Delimiter

Choice

Comma

Yes

The character to be used as delimiter when processing input data. Available delimiters are as follows:

  • Comma (,)

  • Tab (\t)

  • Semi-colon (;)

  • Colon (:)

  • Pipe (|)

  • Space ( )

  • Other

Available when Input Data Format = “Custom”

1.0.0

Delimiter Value

Text


Optional

The specified value of the delimiter.

Available when Input Data Format = “Custom” and Delimiter = “Other”

1.0.0

Line Separator

Choice

Windows (\r\n)

Yes

The character to be used as record separator. Available record separators are as follows:

  • Windows (\r\n)

  • UNIX (\n)

Available when Input Data Format = “Custom”

1.0.0

Handling of Quotes and Escape Character

Choice

Use only Quotes

Yes

The strategy used by the input file to escape special characters present in value fields such as a delimiter, CR, LF, etc. Available escape strategies are as follows:

  • Use only Quotes

    • If the delimiter, \n or \r appear in a field, the field is enclosed in the quoting character.

    • If the quoting character appears in the field, the quoting character itself is doubled.

  • Use only Escape Character

    • If the delimiter, \n or \r appear in a field, they are prefixed with the escape character.

    • If the escape character appears in a field, it is prefixed with itself.

  • Use Quotes and Escape Character

    • If the delimiter, \n or \r appear in a field, the field is enclosed in the quoting character.

    • If the quoting character appears in the field, it is prefixed with the escape character.

    • If the escape character appears in a field, it is prefixed with itself.

  • --None--

    • Fields should not contain the delimiter character, \n or \r.

Available when Input Data Format = “Custom”

1.0.0

Quote Mode

Choice

Only when escaping a character is required

Optional

The quote mode used by the input file. Available quote modes are as follows:

  • Only when escaping a character is required (Only quote those fields which contain special characters such as delimiter, quote character or any of the characters in record separator)

  • Only in the case of non-numeric values (Only quote non-numeric fields)

  • In all cases (Quote all fields)

Available when Input Data Format = “Custom” and Handling of Quotes and Escape Character is either “Use only Quotes” or “Use Quotes and Escape Character”

1.0.0

Escape Character

Choice

Double Quote

Optional

The character to be used as escape character.

Available when Input Data Format = “Custom” & Handling of Quotes and Escape Character is either “Use only Escape Character” or “Use Quotes and Escape Character”. Available escape characters are as follows:

  • Quote (')

  • Double Quote (“)

  • Forward Slash (/)

  • Backslash (\)

  • Exclamation mark (!)

1.0.0

Quote Character

Text

“

Optional

The character to be used as quote character when processing the input data.

Available when Input Data Format = “Custom” and Handling of Quotes and Escape Character is either “Use only Quotes” or “Use Quotes and Escape Character”

1.0.0

Data Output

Multiple Choice

Extension Output

Yes

The data output method. Available actions are as follows:

  • Extension Output: Data is represented in JSON format

  • File Output: filtered CSV data can be written in a new file.

  • STDOUT: Data are presented on STDOUT in tabular format to help the user make sense of the filtered data.

1.0.0

Output File Path

Text


Optional

The path to the output file where filtered data are to be stored. Output file format is the same as the Input File format.

If the file already exists it is overwritten.

1.0.0

Filter Data

Boolean

false

Yes

Enables data filtering and unfolds on the UI the available filter options

1.0.0

Column

Dynamic Choice


Optional

The column’s name based on which to apply the filtering.

The first row of input data is always presumed to be the headers row (the row that represents the column names)

Available only when Filter Data = True

1.0.0

Condition

Choice

Equals

Optional

Determines the row filtering condition that will be used to filter the rows of data. Available filtering conditions are as follows:

Available only when Filter Data = True

1.0.0

Condition Value

Text


Optional

The condition’s value.

Available only when Filter Data = True

1.0.0

Extension Cancellation

When a “cancel command” is received from the controller, any temporary file is cleaned up and everything returns to the state that it was before the task was run.

Extension Exit Codes

Extension Exit Code

Status

Status Description

 Meaning

0

SUCCESS

“SUCCESS: Task executed successfully. <<Additional Information>>“

Successful Execution

1

FAIL

“FAIL: <<Error Description>>”

This error is mapped to the following cases:

  • UAC Error

  • Any unexpected Error

20

DATA_VALIDATION_ERROR

“DATA_VALIDATION_ERROR: <<Error Description>>“

** See STDERR for more detailed error descriptions.

Input fields validation error. See STDERR for more detailed error descriptions.

21

INPUT_FILE_NOT_FOUND_ERROR

“INPUT_FILE_NOT_FOUND_ERROR: Input file could not be found.”

The path specified in the “Input File Path“ field is not valid.

22

OUTPUT_FILE_PATH_ERROR

“OUTPUT_FILE_PATH_ERROR: The following file path is not valid: <<Filepath>>.”

The path specified in the “Output File Path“ field is not valid.

23

FILE_FORMAT_ERROR

“FILE_FORMAT_ERROR: The format of the input file does not match the specified format.“

The format of the input file is not the same as the one specified in the input fields.

24

HEADER_ROW_NOT_FOUND_ERROR

“HEADER_ROW_NOT_FOUND_ERROR: Input file does not contain a header row.“

The input file does not contain a header row.

25

INVALID_COLUMN_ERROR

“INVALID_COLUMN_ERROR: Specified column does not exist.“

The column specified in the “Column“ field does not exist.

50

PERMISSION_ERROR

“PERMISSION_ERROR: <<Error Description>>“

Failed to access or write to file.

STDOUT and STDERR


STDOUT and STDERR provide additional information to User. The populated content can be changed in future versions of this extension without notice. Backward compatibility is not guaranteed.

How To

Import Universal Template

To use the Universal Template, you first must perform the following steps.

  1. This Universal Task requires the Resolvable Credentials feature. Check that the Resolvable Credentials Permitted system property has been set to true.

  2. To import the Universal Template into your Controller, follow these instructions.

  3. When the files have been imported successfully, refresh the Universal Templates list; the Universal Template will appear on the list.

Modifications of this integration, applied by users or customers, before or after import, might affect the supportability of this integration. For more information refer to Integration Modifications.

Configure Universal Task

For a new Universal Task, create a new task, and enter the required input fields.

Environment Variables

For performance and memory tuning the extension recognizes two environment variables.

  • UE_INPUT_FILE_BUFFER_LINES expects an integer value and controls how many lines are read at a time from the input file and written to the output file (if one is provided). Higher numbers for this variable will increase performance but may also significantly increase the memory usage of the extension. Lower numbers reduce memory usage but may also reduce performance. A default value of 1000 is used if none is provided and considered most suitable for the majority of the cases.

  • UE_EXTENSION_OUTPUT_MAX_LINES expects an integer value and controls the maximum number of rows from the input file that is printed as JSON on the Extension output. If the number of rows of the input file exceeds the value provided to this variable the number of rows is truncated to the value of this variable and the message “Extension output, maximum number of lines reached.” is appended to the Status Description of the task. This truncation only affects data printed to the Extension Output and does not affect the rest of the outputs (STDOUT, File Output).

Integration Modifications

Modifications applied by users or customers, before or after import, might affect the supportability of this integration. The following modifications are discouraged to retain the support level as applied for this integration.

  • Python code modifications should not be done.

  • Template Modifications

    • General Section

      • "Name", "Extension", "Variable Prefix", and "Icon" should not be changed.

    • Universal Template Details Section

      • "Template Type", "Agent Type", "Send Extension Variables", and "Always Cancel on Force Finish" should not be changed.

    • Result Processing Defaults Section

      • Success and Failure Exit codes should not be changed.

      • Success and Failure Output processing should not be changed.

    • Fields Restriction Section
      The setup of the template does not impose any restrictions. However, with respect to the "Exit Code Processing Fields" section.

      1. Success/Failure exit codes need to be respected.

      2. In principle, as STDERR and STDOUT outputs can change in follow-up releases of this integration, they should not be considered reliable sources for determining the success or failure of a task.

Users and customers are encouraged to report defects, or feature requests at Stonebranch Support Desk.

Use Cases

Using the extension with a Recurring Task.

The extension can be used with a recurring task to execute tasks based on CSV input where each file row represents the input for a task.

Suppose we have a list of contacts stored in a CSV and we want to email every contact related to the Ferrell LLC company.

This can be done with a recurring task. In each iteration, the recurring task takes a file row as input and sends the corresponding email. In the first iteration, the recurrent task takes the first row of the file as input, in the second iteration it takes the second row as input, etc.

Firstly, we create a workflow and add two tasks to it.

Email contacts workflow

The first is a CSV Tool task that takes the contacts file as input, filters it based on the company, and converts it to JSON.


“Contacts to JSON“ task definition

The second one is a recurrent task that sends an email to each contact in the file.

“Send Email to each Contact“ Task Definition

Using the following command, we set the number of recurrences to be equal to the number of file rows.

${_outputJsonPathFromTask('Contacts To JSON', 'EXTENSION', '$.result.csv_to_json.length()')}

From 7.4 onward we can use ${__varCsvRecordCount('mapping_csv')} to get the length of a CSV file, where mapping_csv is a variable that holds the CSV data.


Using the following command, we extract the field value from each row where "field" stands for the field's header name.



${_outputJsonPathFromTask('Contacts To JSON', 'EXTENSION', '$.result.csv_to_json.[${__subtract('${ops_recurrence_count}','1')}].field')}

From 7.4 onward ${ops_recurrence_count_minus_1} can be used instead of ${__subtract('${ops_recurrence_count}','1'). 

${_varCsvRecordValue('mapping_csv', ${ops_recurrence_count_minus_1}, 0)} can be used to get a field value from a CSV file.


Usage Principles

When filtered data is large then the optimal way is to use a file as output, without pushing the data as Extension Output.

When filtered data needs to be easily visible in a workflow and it's small in size then you can consider having it as Extension Output (JSON format)
However, task authors should be aware that having the data as Extension Output means it is stored in the UC database.
As a general principle, storing data in the UC database that are not necessary should be avoided and that has certain benefits towards performance and storage capacity.

Document References

This document references the following documents:

Document Link

Description

Universal Templates

User documentation for creating, working with and understanding Universal Templates and Integrations.

Universal Tasks

User documentation for creating Universal Tasks in the Universal Controller user interface.

Changelog

ue-csv-tool-1.0.0 (08-04-2023)

Initial Version