1. TARGIT Community
  2. Documentation
  3. Data Warehouse: TARGIT InMemory
  4. Examples

Retrieving information from REST Api with loop from input list

Avatar
Niels Thomsen

This script is a simple example of communication with a REST API using ETL studio.

An input list is read - an for each entry the REST API is called and information retrieved - so the script contains an example of looping through reading a file until a certain value is reached of EOF (end of file).

 

ETL Flow

mceclip0.png

 

Full script

LOAD ASSEMBLY 'TARGIT.Csv.dll'//server
LOAD ASSEMBLY 'TARGIT.Javascript.dll'//server
DATASOURCE[CSV_dk_semicolon]= DOTNET CONNECTION 'TARGIT.CSV.CsvConnection''HasHeaders=true;loadasstring=true;type=local;delimiter=;;encoding=utf-8;culture=da-DK;detectionrowscount=100;skiplines=0;'

DATASOURCE[CVR_REST_API]= DOTNET CONNECTION 'TARGIT.Javascript.JavascriptConnection''script=Tokengoeshere==;;scriptTimeoutSecs=600;LicenseAddress=localhost;LoadAsString=true'
/* group Debug */
DECLARE @DebugInfo asINT
DECLARE @CvrRestApiLoadCounter asINT

/* DebugInfo: 
1 will keep temp,STAGE and WORK tables in final database. 
0 removes temp tables */
SET @DebugInfo =0

/* Specify how many CVR Numbers to look up, during load */
SET @CvrRestApiLoadCounter =3
/* end group Debug */
/* group Load data */
IMPORT[STAGE_CSV_CvrList]=[CSV_dk_semicolon].{select*from[TARGIT REST API\Examples\CVR API\Data\TARGIT DK CVR List.csv]}
IMPORT[WORK_CvrList]=[ME].{SELECT
    [EndUser VAT Nbr - SAP],
    [EndUser Name - SAP],
    /* Returns a string which replaces all occurrences of string2 in string1 with string3 */
 /* Removes DKK first and then DK */
    REPLACE(
     REPLACE([EndUser VAT Nbr - SAP],'DKK','')
     ,'DK','')AS CvrNumber
FROM[STAGE_CSV_CvrList]}
/*
select * from [] 
*/

DECLARE @CvrNumber ASSTRING
DECLARE @i ASINT

WHILE[WORK_CvrList].EOF=false and @i < @CvrRestApiLoadCounter
 SET @i = @i +1
 SET @CvrNumber = WORK_CvrList.CvrNumber
 PRINT'Reading CVR data about '+ @CvrNumber
 PRINT'Number '+ @i +' of '+ @CvrRestApiLoadCounter
 IMPORT[STAGE_CvrRestApi_CvrData]=[CVR_REST_API].{select*from[](PARAMETERS:CVR=@@CvrNumber)}
MOVENEXT[WORK_CvrList]
LOOP
/* end group Load data */
SAVE

 

 

Note: The REST API in question needs a token which is not included in this example. The input list is not included either.

 

Was this article helpful?
0 out of 0 found this helpful

Comments

1 comment
Date Votes
  • Avatar
    Kristoffer Refslund
    (Edited )

    Hi Niels,

    I am a bit confused by the example provided here as the syntax seems significantly different from the template provided when trying to import through Java.

    Why is that and what is the difference?

    // Otherwise use this template:
    const doGetAsync = async (uri, contentType, headers) => {
        return utils.GetAsync(uri, contentType, JSON.stringify(headers))
            .ToPromise()
            .then(success => {
                return JSON.parse(success);
            }, error => {
                return JSON.parse(error);
            }).then(response => {
                if (response.Data && response.Data.Response) {
                    return JSON.parse(response.Data.Response);
                }
                return response.Data;
            });
    };
    var data = await doGetAsync('@@URL@@', "application/json", {});
    jsonStringResult.registerJsonResponse(JSON.stringify(data)); //this line is required to pass the JSON string into the JavaScript driver.

    Just to be clear. It is the JavascriptConnection that doesnt make much sense to me :)

    DATASOURCE[CVR_REST_API]= DOTNET CONNECTION 'TARGIT.Javascript.JavascriptConnection''script=Tokengoeshere==;;scriptTimeoutSecs=600;LicenseAddress=localhost;LoadAsString=true'
    0

Please sign in to leave a comment.

Articles in this section

See more