Get Data

Getting Data

After getting your authentication token you can call a get request to collect data. Most APIs allow you to collect a maximum number of records per request. It is very unlikely that you can extract all information using only one request. So you will need to get the data in batches.

AccountView is using an unique record-id and you can only get 1000 records per request. So you will need to use the record-id to extract the next batch of records.

Below you will see a Power-Shell script to extract the GL Transactions (Journaal) and I will break it down in sections and give comments (See purple lines)

For security reasons the client id and client secret have been replaced by XXXXXXXX.

The terminal is the screen in which the code is executed. So if it is saying the number is displayed on the terminal then it means it is displayed on the screen.

The process will collect batches of 1000 records and store them in a temp file and will keep doing this until a batch is less than 1000 records. After collecting all the batches it will merge the temp files into 1 file and copy the file to the azure store location, where Board can import the data from.

#The first couple of lines are for generating a LogFile, I use a log file to log information, like on which date the process is executed and how many rows are collected.

#LOGFile

$Bestand_ID = '1070'

$Bestand = 'Journaal'

$Process = 'Get Journaal.ps1'

$Datum = Get-Date

#The variable $params is used to store the token and will be passed on when calling the Invoke-WebRequest

$params = @{"grant_type"="refresh_token";

"client_id"="XXXXXXXXXXXXXXXXXXXX";

"refresh_token"="XXXXXXXXXXXXXXXXXX";

"client_secret"="XXXXXXXXXXXXXXXXXXXX"

}

#The line below we will call the webservice using a post and it will return the access token

$response = Invoke-WebRequest -Uri https://www.accountview.net/api/v3/token -Method POST -Body $params

#The line below will convert the response to a Json format and store it in the variable JSONResult

$JSONResult = $response.Content | ConvertFrom-Json #| select -expand RestResponse | select -expand result

#The lines below will extact data from the JSON and store it into varables so they can be used later on.

$Access_token = $JSONResult | Select access_token #| Out-String

$Token_Type = $JSONResult | Select token_type #| Out-String

$Expires_in = $JSONResult | Select expires_in #| Out-String

$Refesh_token = $JSONResult | Select refresh_token #| Out-String

$token = "bearer $Access_token".Replace("@{access_token=","")

$token = $token.Replace("}","")

#You can get up to 1000 records per request, so in the first request we collect the first batch.

#The first 1000

#In the body we configure which object we will call, the max number of records (PageSize) and which fields and I filter the data with a bookdate >= 1 Jan 2019

$body =@{

BusinessObject = "GJ1"

PageSize = "1000"

Fields = "TRANSACT.PERIOD, TRANSACT.PER_CALC, TRANSACT.DJ_CODE,TRANSACT.TRN_DATE,TRANSACT.SUB_NR,TRANSACT.ACCT_NR,TRANSACT.INV_NR,TRANSACT.TRN_DESC,TRANSACT.COST_CODE,TRANSACT.PROJ_CODE,TRANSACT.ITEM_QTY,TRANSACT.PC_DIM,TRANSACT.CST_AMT,TRANSACT.VAT_CODE,TRANSACT.REC_ID, TRANSACT.S2_ADMYEAR"

SortFields = "TRANSACT.REC_ID"

SortOrder = "ascending"

BookDate = "2019-01-01"

}

#In the header of the request we need to pass the authorization token.

$headers = @{

Authorization = $token

"x-company" = "689c0d09-8899-4bad-b284-cb8ca2c16820"

}

# The First 1000

$rows=1000

#The data which we receive will be stored in temp files

$destinationFile = 'C:\temp\Journaal_'+ $rows+ '.csv'

$url = "https://www.accountview.net/api/v3/accountviewdata"

#In the lines below we will call the API using a GET method and we use a try and catch, which means we can check if the request was successful or not.

try {

$response = Invoke-RestMethod -Uri $url -method GET -Headers $headers -Body $body

#We will save the selected fields to the temp file

$response.TRANSACT | Select-Object PERIOD, PER_CALC, DJ_CODE, TRN_DATE, SUB_NR, ACCT_NR, INV_NR, TRN_DESC, COST_CODE, PROJ_CODE, ITEM_QTY, PC_DIM, CST_AMT, VAT_CODE, S2_ADMYEAR, REC_ID | Export-Csv $destinationFile -Delimiter ';' -NoTypeInformation

}

catch {

#If something when wrong we will get the exception and show it in the terminal

# Dig into the exception to get the Response details.

# Note that value__ is not a typo.

Write-Host "StatusCode:" $_.Exception.Response.StatusCode.value__

Write-Host "StatusDescription:" $_.Exception.Response.StatusDescription

}

#Now we need to get the last record and store it in the variable Last_Transaction_ID

#Get the last transaction

$Transaction_ID = Import-CSV $destinationFile -Delimiter ';' | select -ExpandProperty REC_ID

$LastLine = $Transaction_ID[-1]

$LastLine = "0000000000$LastLine"

$Last_Transaction_ID = $LastLine.substring($LastLine.length - 10, 10)

#The code below will count the transactions in the batch and return the number to the terminal

#Count the transactions

$count = 0

foreach($line in $Transaction_ID)

{

if($line -ne ""){$count++}

}

write-host $count

#Here the loop starts and it will continue to run as long as the batch returns 1000 records.

# Loop

while ($count -eq 1000)

{

$rows=$rows + 1000

$destinationFile = 'C:\temp\Journaal_'+ $rows+ '.csv'

#The difference is we now added the LastKey to the body, this will be used to get the next set starting the next # record after the LastKey

$body =@{

BusinessObject = "GJ1"

PageSize = "1000"

LastKey = $Last_Transaction_ID

SortFields = "TRANSACT.REC_ID"

SortOrder = "ascending"

BookDate = "2019-01-01"

}

$headers = @{

Authorization = $token

"x-company" = "689c0d09-8899-4bad-b284-cb8ca2c16820"

}

$url = "https://www.accountview.net/api/v3/accountviewdata"

try {

$response = Invoke-RestMethod -Uri $url -method GET -Headers $headers -Body $body

}

catch {

# Dig into the exception to get the Response details.

# Note that value__ is not a typo.

Write-Host "StatusCode:" $_.Exception.Response.StatusCode.value__

Write-Host "StatusDescription:" $_.Exception.Response.StatusDescription

}

#Get the last transaction

$Transaction_ID = Import-CSV $destinationFile -Delimiter ';' | select -ExpandProperty REC_ID

$LastLine = $Transaction_ID[-1]

$LastLine = "0000000000$LastLine"

$Last_Transaction_ID = $LastLine.substring($LastLine.length - 10, 10)

#Count the transactions

$count = 0

foreach($line in $Transaction_ID)

{

if($line -ne ""){$count++}

}

write-host $count

#If the number of records is not equal 1000 then the process will exit the loop else it will run for the next batch

if ($count -ne 1000) {break}

}

#Merging the Journaal files into one called tmp_journaal.csv

#Get the header from one of the CSV Files, write it to input.csv

Get-ChildItem "c:\temp\journaal_*.csv" | select -First 1| Get-Content | select -First 1 | Out-File -FilePath "c:\temp\tmp_journaal.csv" -Force

#Get the content of each file, excluding the first line and append it to input.csv

Get-ChildItem "c:\temp\journaal_*.csv" | foreach {Get-Content $_ | select -Skip 1 | Out-File -FilePath "c:\temp\tmp_journaal.csv" -Append}

#Read the data from the file tmp_journaal.csv and store it into a variable called OBJS

$OBJS = @();

$Output = Import-Csv "c:\temp\tmp_journaal.csv" -Delimiter ";" | ForEach{

$Object = New-Object psobject -Property @{

PERIOD = $_.PERIOD.trim()

PER_CALC = $_.PER_CALC.trim()

DJ_CODE = $_.DJ_CODE.trim()

TRN_DATE = $_.TRN_DATE.trim()

SUB_NR = $_.SUB_NR.trim()

ACCT_NR = $_.ACCT_NR.trim()

INV_NR = $_.INV_NR.trim()

TRN_DESC = $_.TRN_DESC.trim()

COST_CODE = $_.COST_CODE.trim()

PROJ_CODE = $_.PROJ_CODE.trim()

ITEM_QTY = ($_.ITEM_QTY.replace(",",".")).trim()

PC_DIM = $_.PC_DIM.trim()

CST_AMT = ($_.CST_AMT.replace(",",".")).trim()

VAT_CODE = $_.VAT_CODE.trim()

S2_ADMYEAR = $_.S2_ADMYEAR.trim()

REC_ID = $_.REC_ID.trim()

BOARDPERIOD = [String]::Concat("0",$_.PERIOD).substring(([String]::Concat("0",$_.PERIOD)).length-2)

BOARDDATE = [String]::Concat($_.S2_ADMYEAR.substring(0,4), [String]::Concat("0",$_.PERIOD).substring(([String]::Concat("0",$_.PERIOD)).length-2), "01")

}

$OBJS += $Object;

}

## Update the Board date for period 0

$OBJS | where BOARDPERIOD -eq "00" | foreach{$_.BOARDDATE = [String]::Concat($_.S2_ADMYEAR.substring(0,4),"0101")}

#save the data stored in the variable OBJS into the final file called journaal.csv

$OBJS | Select-Object "PERIOD", "DJ_CODE", "TRN_DATE", "SUB_NR", "ACCT_NR", "INV_NR", "TRN_DESC", "COST_CODE", "PROJ_CODE", "ITEM_QTY", "PC_DIM", "CST_AMT", "VAT_CODE", "REC_ID", "BOARDDATE", "BOARDPERIOD" | Export-Csv 'c:\temp\journaal.csv' -Delimiter ';' -NoTypeInformation

#cleanup the temp files

Remove-Item 'c:\temp\journaal_*.csv'

Remove-Item 'C:\temp\tmp_journaal.csv'

#Count the transactions

$count = 0

foreach($line in $OBJS)

{

if($line -ne ""){$count++}

}

#Update the LOG File

$LogFile =@()

$LogFile = New-Object System.Object

$LogFile | Add-Member -MemberType NoteProperty -Name "Bestand_ID" -Value $Bestand_ID

$LogFile | Add-Member -MemberType NoteProperty -Name "Bestand" -Value $Bestand

$LogFile | Add-Member -MemberType NoteProperty -Name "Process" -Value $Process

$LogFile | Add-Member -MemberType NoteProperty -Name "Datum" -Value $Datum

$LogFile | Add-Member -MemberType NoteProperty -Name "Antal Records" -Value $count

$LogFile | Export-Csv -Path "c:\temp\logfiles.csv" -Append -Delimiter ';' -NoTypeInformation

#Save the file to the AZURE Storage explorer

$azPath = “C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy”

Set-Location $azPath

$StorageAccountName = “XXXXXXXX”

$StorageAccountKey = “XXXXXXXX”

$SourceFolder = “/Source:C:\temp”

$DestURL = “/dest:https://vektisstrg01.file.core.windows.net/bss/dataset/accountview?sv=2017-04-17&si=bss-XXXXXXXX=s&sig=XXXXXXXX“

$ResultAZCOPY = .\AzCopy.exe $SourceFolder $DestURL /Pattern:journaal.csv /S /Y

Board Tips

Get Data

Getting Data

A sample of the file