Importing multiple data files

In this guide, we show how you can use the Import API to load multiple files 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 API endpoint requires you to know your hub. Your hub is the three characters in the URL that appear before .vena.io. In the example below, the hub is us2.

How to import multiple data files using Vena API

These instructions are suitable for importing multiple data files using the Import API. This process is composed of three steps, creating the job, uploading the files and starting the job.

Step 1: Creating the job

1. You will use the Create Job endpoint. Create Job allows you to create a job and set it to the EDITING stage.
2. The request URI for Create Job is available below. Replace the URI parameters {hub} and {templateId} with the appropriate values.
   • Instructions for finding your hub are available here.
   • Instructions for finding the templateId is available here.

POST https://{hub}.vena.io/api/public/v1/etl/templates/{templateId}/jobs

3. Include your API key at the end of the request. Instructions for finding your API key are available here.

4. A sample request is included below:

curl https://us2.vena.io/api/public/v1/etl/templates/1099180598582312960/jobs
-X POST -H "Content-Type: application/json" -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391

5. After submitting your API request, a 200 status code indicates that an ETL Job was created and is in the EDITING stage. A sample response is included below.

{
  "id": "1099180598582312960",
  "name": "LoadGL",
  "status": "EDITING",
  "modelId": "988930839071293441",
  "modelName": "Financial Model",
  "createdDate": 1623338765403,
  "updatedDate": 1623338873829,
  "userName": "Admin User"
}

6. If you have received 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. To continue the process for importing multiple data files, continue to the next section.

Step 2: Loading data to the ETL step

Use the Get Template endpoint to see all the steps in the job. Steps that require data input will have an inputId field.

This step can be done as many times as needed.

A. Import data (JSON) to ETL step
These instructions are suitable for importing data in JSON format.

1. You will use the Load JSON to Specific Input endpoint. Load JSON to Specific Input allows you to upload JSON data to an existing job without starting the job.
2. The request URI for Load JSON to Specific Input is available below. Replace the URI parameters {hub}, {jobId} and {inputId} with the appropriate values.
   • Instructions for finding your hub are available here.
   • Instructions for finding the templateId is available here.
   • The inputId can be found in the response of your Create Job call.

PUT https://{hub}.vena.io/api/public/v1/etl/jobs/{jobId}/inputs/{inputId}

3. Include your API key at the end of the request. Instructions for finding your API key are available here.
4. 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:

{
  "data": [
    ["1", "2", "3"],
    ["4", "5", "6"],
    ["7", "8", "9"]
  ]
}

5. Combine the request URI and request body to complete your API request. A sample request is included below:

curl https://us2.vena.io/api/public/v1/etl/jobs/1113346477561741312/inputs/1111438645660745728
-X PUT -H "Content-Type:application/json" -u
1112140682300620800.1:51fd255f934040649f98d2a74d2a74d1c4391 -d'{"data": [[1, 2, 3], [4, 5, 6]]}'

6. After submitting your API request, a 204 status code indicates that the data was loaded to the job.
7. If you have received 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.

B. Import data (Flat File) to ETL step
These instructions are suitable for importing data in CSV, PSV and TDF format.

You will use the Load File to Specific Input endpoint. Load File to Specific Input allows you to upload data in CSV, PSV and TDF format to an existing job, without starting the job.

1. The request URI for Load File to Specific Input is available below. Replace the URI parameters {hub}, {jobId} and {inputId} with the appropriate values.
   • Instructions for finding your hub are available here.
   • Instructions for finding the templateId is available here.
   • The inputId can be found in the response of your Create Job call.
2. Include your API key at the end of the request. Instructions for finding your API key are available here.
3. 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.

A sample payload file and metadata is included below:

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

{"input": {"partName": "file", "fileFormat": "CSV", "fileEncoding": "UTF-8", "fileName": "data.csv"}}

4. Combine the request URI and request body to complete your API request. A sample request is included below:

curl --location --request PUT 'https://us2.vena.io/api/public/v1/etl/jobs/1113346477561741312/inputs/1111438645660745728/file' \
-u 498265219441623040.498264904105459712:db30a250930e4b1e975c7a3f139bb7bf \
--form 'file=@"data.csv"' \
--form 'metadata="{\"input\":{\"partName\":\"file\",\"fileFormat\":\"CSV\",\"fileEncoding\":\"UTF-8\",\"fileName\":\"data.csv\"}}"'

5. After submitting your API request, a 204 status code indicates that the data was loaded to the job.

6. If you have received 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.

Step 3: Submit job

1. You will use the Submit Job endpoint. Submit Job allows you to submit an ETL job.
   The request URI for Submit Job is available below. 2. 2. Replace the URI parameters {hub} and {jobId} with the appropriate values.
   • Instructions for finding your hub are available here.
   • Instructions for finding the templateId is available here.

POST https://{hub}.vena.io/api/public/v1/etl/jobs/{jobId}/submit

3. Include the headers and your API key at the end of the request. Instructions for finding your API key are available here.
4. A sample request is included below:

curl https://us2.vena.io/api/public/v1/etl/jobs/1099180598582312960/submit -X POST -H "Content-Type: application/json" -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391

5. After submitting your API request, a 200 status code indicates that the job was submitted. A sample response is included below:

{
  "id": "1099180598582312960",
  "name": "LoadGL",
  "status": "SUBMITTED",
  "modelId": "988930839071293441",
  "modelName": "Financial Model",
  "createdDate": 1623338765403,
  "updatedDate": 1623338873829,
  "userName": "Admin User"
}

6. If you have received 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 the job, follow these steps.