Guides
Contact Us

Load data into Vena

Suggest Edits

In this guide, we show how you can use the Import API endpoints to upload JSON data or CSV data to Vena.

Before you begin

To follow the instructions in this article, you will need at least Modeler access.

You will also need an application token from Vena. You can get your application token by following these instructions.

Every Import API endpoint requires you to know your hub. To find your hub, please refer to the Finding API parameters in Vena article.

How to import data (flat file) using the Import API

postStart with File

These instructions are suitable for importing data in CSV, PSV and TDF format.

  1. You will use the Start with File endpoint. Start with File allows you to create a job, upload a CSV file and start the job in one combined step.
  2. The request URI for Start with File is available below. Replace the URI parameters {hub} and {templateId} with the appropriate values. 
POST https://{hub}.vena.io/api/public/v1/etl/templates/{templateId}/startWithFile
  1. Include your API key at the end of the request. Instructions for finding your API key are available here.
  2. Create the request body. The request body is composed of the file and metadata.
    • The file is the file path. The value must be the same as that of the partName in the metadata.
    • The metadata contains the partName, fileFormat, fileEncoding and fileName in JSON format.
NameTypeDescription
partNameStringField name for storing file in request body.
fileFormatCSV, PSV, TDFType of file to be uploaded.
fileEncodingUTF-8, UTF-16, Windows Latin-1The encoding of file to be uploaded.
fileNameStringFile name

A sample payload is available below

Sample file (CSV):

Account,Entity,Department,Placeholder 1,Placeholder 2,Placeholder 3,Placeholder 4,Year,Period,Scenario,Currency,Measure,Value
3910,V001,D10,Undefined,Undefined,Undefined,Undefined,2019,1,Actual,Local,Value,2511.71
3910,V001,D10,Undefined,Undefined,Undefined,Undefined,2019,2,Actual,Local,Value,2511.71
3910,V001,D10,Undefined,Undefined,Undefined,Undefined,2019,3,Actual,Local,Value,2511.71

Sample metadata:

{
  "input": {
    "partName": "file", 
    "fileFormat": "CSV", 
    "fileEncoding": "UTF-8", 
    "fileName": "data.csv"
  }
}
  1. Combine the request URI, headers, and request body to complete your API request. A sample request is included below:
curl --location --request POST 'https://us2.vena.io/api/public/v1/etl/templates/1050582343200276480/startWithFile' \
-u 498265219441623040.498264904105459712:db30a250930e4b1e975c7a3f139bb7bf \
--form 'file=@"data.csv"' \
--form 'metadata="{\"input\":{\"partName\":\"file\",\"fileFormat\":\"CSV\",\"fileEncoding\":\"UTF-8\",\"fileName\":\"data.csv\"}}"'
  1. After submitting your API request, a 200 status code indicates that an ETL job is in progress. A sample response is included below:
{
  "id": "1099180598582312960",
  "name": "LoadGL",
  "status": "RUNNING",
  "modelId": "988930839071293441",
  "modelName": "Financial Model",
  "createdDate": 1623338765403,
  "updatedDate": 1623338873829,
  "userName": "Admin User"
}
  1. If you receive an error response code when calling this endpoint, you can use this resource for troubleshooting. To monitor the job status, follow these steps. To cancel a job, follow these steps.

How to import data (JSON) using the Import API

postStart with Data

These instructions are suitable for importing data in JSON format.

  1. You will use the Start with Data endpoint. Start with Data allows you to create a job, upload JSON data and start the job in one combined step.
  2. The request URI for Start with Data is available below. Replace the URI parameters {hub} and {templateId} with the appropriate values. 
POST https://{hub}.vena.io/api/public/v1/etl/templates/{templateId}/startWithData
  1. Include your API key at the end of the request. Instructions for finding your API key are available here.
  2. Create the request body. The request body is composed of the JSON data. The current limitation is 25MB records. A sample payload is available below:
{
  "input": {
    "data": [
      [
        "Account",
        "Entity",
        "Department",
        "Placeholder 1",
        "Placeholder 2",
        "Placeholder 3",
        "Placeholder 4",
        "Year",
        "Period",
        "Scenario",
        "Currency",
        "Measure",
        "Value"
      ],
      [
        "3910",
        "V001",
        "D10",
        "Undefined",
        "Undefined",
        "Undefined",
        "Undefined",
        "2019",
        "1",
        "Actual",
        "Local",
        "Value",
        "2511.71"
      ],
      [
        "3910",
        "V001",
        "D10",
        "Undefined",
        "Undefined",
        "Undefined",
        "Undefined",
        "2019",
        "2",
        "Actual",
        "Local",
        "Value",
        "2511.71"
      ],
      [
        "3910",
        "V001",
        "D10",
        "Undefined",
        "Undefined",
        "Undefined",
        "Undefined",
        "2019",
        "3",
        "Actual",
        "Local",
        "Value",
        "2511.71"
      ]
    ]
  }
}
  1. Combine the request URI, headers, and request body to complete your API request. A sample request is included below:
curl https://us2.vena.io/api/public/v1/etl/templates/1050582343200276480/startWithData -X POST -H "Content-Type: application/json" -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391 -d '{"input": {"data": [[1, 2, 3], [4, 5, 6]]}}'
  1. After submitting your API request, a 200 status code indicates that an ETL job is in progress. A sample response is included below:
{
  "id": "1099180598582312960",
  "name": "LoadGL",
  "status": "RUNNING",
  "modelId": "988930839071293441",
  "modelName": "Financial Model",
  "createdDate": 1623338765403,
  "updatedDate": 1623338873829,
  "userName": "Admin User"
}
  1. If you receive an error response code when calling this endpoint, you can use this resource for troubleshooting. To monitor the job status, follow these steps. To cancel a job, follow these steps.

Updated 7 months ago