• Migration Topics
  • Integration
    • Standard ActionHandlers
    • ServiceNow
    • SAP
    • Email
    • CSV
      • About the csv connector
      • CSV ActionHandler User Guide
    • Approval
    • Introduction to ActionHandlers and Connectors
  • Concepts
  • Best Practices
  • Api

Introduction

CSV ActionHandler converts an existing CSV file from the HIRO Graph (blob on ogit/Attachment) to a proper JSON formatted table, represented by an ogit/Document/Spreadsheet. In this guide we will walk through the process from adding an attachment to Quering the JSON formated table created by CSV ActionHandler.

Prerequisites

  1. Authenticate and retrieve a Graph Authentication token. Please refer to our documentation on HIRO Graph Auth API.

  2. Id for the 'data-scope' (environment) you want to interact with. This value will be written to the ogit/_scope attribute. If omitted the default scope of your account will be used. Please be aware, that this may lead to confusion if your account has access to multiple scopes. Therefore, it’s highly recommended defining your desired scope.

  3. Having a CSV file as an attachment in the HIRO graph. Please see below to create an attachment vertex and load the CSV data.

Creating attachment vertex

In case you do not have an attachment vertex in the graph yet, a simple curl command is given below. Please refer to HIRO Graph REST API to know more about the API. In the following command replace <TOKEN> and <SCOPE_ID>. In this document we run our requests against the stage graph, replace the url with https://core.arago.co/ for global graph. When a valid scope and token is used, you are presented with a response. Make a note of "ogit\/_id", this will be used in the steps to follow.

curl -k --request POST --url https://eu-stagegraph.arago.co/api/graph/7.1/new/ogit%2fAttachment --header 'Authorization: Bearer $TOKEN' --header 'Content-Type: application/json' --data '{"/name": "CSV", "ogit/_scope": "<SCOPE_ID>"}'

Load CSV data to the attachment

In this section we load the CSV data into the attachment created in the previous section. In the following curl command replace <ID> with "ogit\/_id" from the previous step. The response is the contentId of the attachment. Please note, maximum size of csv file should not be bigger than maximum size of the vertex.

curl --request POST --url https://eu-stagegraph.arago.co/api/graph/7.1/<ID>/content --header 'Authorization: Bearer $TOKEN' --header 'Content-Type: text/csv'
--data 'h1,h2,h3,h4
> 1,2,3,5
> 6,7,8,9
> 10,11,12,13'

Using the Action Handler

Parameters in the Action Handler

The table given below consists of parameters that can be used in the KI to achieve the desired structure of JSON.

Parameter Description

keys (optional)

Identifier if keys are represented by rows or columns, default being row. Expected values: row or column.

colOffset (optional)

Offset rows of the table. Entries before the offset are ignored. Default: 0

rowOffset (optional)

Offset columns of the table. Entries before the offset are ignored. Default: 0

delimiter (optional)

Delimiting character. Default:, or Comma

Calling via KI

In the following KI example, we use the default parameter values. When an issue with variable ConvertToSpreadsheet and SourceId is created, this KI is triggered and the corresponding CSV is converted to JSON.

Sample KI for CSV to JSON conversion
on
  ogit/_id
when
  ConvertToSpreadsheet
  SourceId
do
  code: LOCAL::STATUS,
  body: LOCAL::RESULT = action(
      capability: "CSVtoSpreadsheet",
      sourceId: SourceId
  )
  if LOCAL::STATUS == "200" then
    log("SUCCESS: Your CSV is has been formated ${LOCAL::RESULT#result}")
    TargetId = "${LOCAL::RESULT#result['target_id']}"
	delete(ConvertToSpreadsheet)
  else
    log("ERROR ${LOCAL::STATUS}: ${LOCAL::RESULT}")
  end
Example of data returned by CSV ActionHandler
log SUCCESS: Your CSV is has been formated {"target_id": "${TARGET_ID}"}

You can modify the KI to use column as identifier:

Sample KI for CSV to JSON conversion with column as identifier
do
  code: LOCAL::STATUS,
  body: LOCAL::RESULT = action(
      capability: "CSVtoSpreadsheet",
      sourceId: SourceId,
      keys: "column",
  )

Resulting Spreadsheet

On successful execution of the KI, you were presented with the TARGET_ID which holds the ogit/_id of the created ogit/Documents/Spreadsheet node. This node holds the JSON fomated CSV.

Table 1. JSON structure when the key is set to Row vs Column

Row

Column

{
  "/h3": [
    {
      "created": 1614085834712,
      "value": "3",
      "key": 0
    },
    {
      "created": 1614085834712,
      "value": "8",
      "key": 1
    },
    {
      "created": 1614085834712,
      "value": "12",
      "key": 2
    },
    {
      "created": 1614085834712,
      "value": "23.0",
      "key": "Total"
    }
  ],
  "/h4": [
    {
      "created": 1614085834712,
      "value": "5",
      "key": 0
    },
    {
      "created": 1614085834712,
      "value": "9",
      "key": 1
    },
    {
      "created": 1614085834712,
      "value": "13",
      "key": 2
    },
    {
      "created": 1614085834712,
      "value": "27.0",
      "key": "Total"
    }
  ],
  "/h1": [
    {
      "created": 1614085834712,
      "value": "1",
      "key": 0
    },
    {
      "created": 1614085834712,
      "value": "6",
      "key": 1
    },
    {
      "created": 1614085834712,
      "value": "10",
      "key": 2
    },
    {
      "created": 1614085834712,
      "value": "17.0",
      "key": "Total"
    }
  ],
  "/h2": [
    {
      "created": 1614085834712,
      "value": "2",
      "key": 0
    },
    {
      "created": 1614085834712,
      "value": "7",
      "key": 1
    },
    {
      "created": 1614085834712,
      "value": "11",
      "key": 2
    },
    {
      "created": 1614085834712,
      "value": "20.0",
      "key": "Total"
    }
  ],
  "ogit/_type": "ogit/Documents/Spreadsheet"
}
{
  "ogit/_created-on": 1614068913491,
  "/10": [
    {
      "created": 1614068912895,
      "value": "11",
      "key": 0
    },
    {
      "created": 1614068912895,
      "value": "12",
      "key": 1
    },
    {
      "created": 1614068912895,
      "value": "13",
      "key": 2
    },
    {
      "created": 1614068912895,
      "value": "36.0",
      "key": "Total"
    }
  ],
  "/6": [
    {
      "created": 1614068912895,
      "value": "7",
      "key": 0
    },
    {
      "created": 1614068912895,
      "value": "8",
      "key": 1
    },
    {
      "created": 1614068912895,
      "value": "9",
      "key": 2
    },
    {
      "created": 1614068912895,
      "value": "24.0",
      "key": "Total"
    }
  ],
  "/1": [
    {
      "created": 1614068912895,
      "value": "2",
      "key": 0
    },
    {
      "created": 1614068912895,
      "value": "3",
      "key": 1
    },
    {
      "created": 1614068912895,
      "value": "5",
      "key": 2
    },
    {
      "created": 1614068912895,
      "value": "10.0",
      "key": "Total"
    }
  ],
  "/h1": [
    {
      "created": 1614068912895,
      "value": "h2",
      "key": 0
    },
    {
      "created": 1614068912895,
      "value": "h3",
      "key": 1
    },
    {
      "created": 1614068912895,
      "value": "h4",
      "key": 2
    },
    {
      "created": 1614068912895,
      "value": "NaN",
      "key": "Total"
    }
  ],
  "ogit/_type": "ogit/Documents/Spreadsheet"
}