Jerome Lefebvre

Get public JSON data into PI using the PI Connector for UFL

Blog Post created by Jerome Lefebvre on Mar 30, 2016

In this post, I will present a simple example of getting publicly available data accessible via simple http requests into the PI System. The example I'll use is to get currency information from the website Foreign exchange rates and currency conversion JSON API. The website simply republishes currency exchange from European Central Bank and is updated daily. You will find the code, a python script and ini file, for the example below in the PI connector for UFL Sample GitHub repository. The code below is not the complete script, so be sure to visit the GitHub page for all details.


Also, if you are visiting the UC this April, make sure to also register for the sending to course on the PI connector for UFL, held on day 3 and titled:

Collecting Data using the new PI Connector for UFL.


On the data and accessing it

The data is easily available via a simple GET request: (sending a get request is what is typically done when you simply type in a URL in a browser) and will return data in the format below:


So, it is in a JSON format and sent without any line breaks. Beside the rate information, the JSON package also comes with a timestamp and the base currency for the exchange, which is by default Euros. To use a different base for the currency, you can use the base url parameter such as in


To get this data using Python is very straightforward and done by the following line:

response = requests.get" ")
data = response.json()


Sending the data to AF and the PI Data Archive

We will use the the PI Connector for UFL's rest endpoint to send the data. But, before we actually send the data, we will take the opportunity to simply change the format to make it easier to parse. The way we will format it is by writting out every key value pair on its own line, except when it is an array, in case those will be extended. A JSON also doesn't have an inherent ordering, thus we will also sort the keys in alphabetical order. This is done by the following method in python:


data = json.dumps(response.json(), indent=4, sort_keys=True)


The data we now want to parse will look as follows:


    "base": "USD",
    "date": "2016-03-24",
    "rates": {
        "AUD": 1.3321,
        "BGN": 1.7535,
        "ZAR": 15.497

We will now send this to the connector, say the URL for the data source is https://localhost:5460/connectordata/currency, then to send data from our Python script we will need to do the following:


s = requests.session()
s.auth = (username, password)
restufl = "https://localhost:5460/connectordata/value" 
response = s.put(restufl, data=data)

Now to parse the data

Here we will cover over the ini file to parse this JSON data. Looking at the JSON file, there are four pieces of information we would like to extract from the JSON file and send to the PI Data Archive, the timestamp, the base currency, the currency name and the exchange rate.


Field(1).NAME = "Timestamp"
Field(1).TYPE = "DateTime"
Field(1).FORMAT = "yyyy-MM-dd"
Field(2).NAME = "Base"
Field(3).NAME = "CurrencyName"
Field(4).NAME = "CurrencyValue"
Field(4).TYPE = "number"

So, we now have our variables. Looking at the JSON data, we can easily see that there is 3 different types of lines in which those lines are included, namely the lines containing the date, the base currency and each individual currency rate.


The date and base currency are easily to deal with and we can filter each simply base on having the correct key somewhere in the line.

Date.FILTER = C1=="*date*"
Timestamp = ["*date\": \"(*)\","]

BaseCurrency.FILTER = C1=="*base*"
Base = ["*base\": \"(*)\""]


Thus, in either case, all we grab is the value in the key/value pair.


Now, we need to grab the currency name and value.


Currency.FILTER = C1=="        *"
CurrencyName = ["*\"(*)\"*"]
CurrencyValue = ["*: (*),"]


Which will grab all but the currency information, except in the case of the very last entry as it does not have a trailing comma. We can grab it using the following example


' To deal with the last currency which does not have a trailing comma
IF CurrencyValue is Null THEN
  CurrencyValue = ["*: (*)"]


Due to the ordering of the data, the timestamp and the base currency were already parsed before the before we reached each individual currency. We can send the data each time we read in a new exchange rate. This will create tags with the name UFL.USD_TO_CAD


StoreEvent(Base + "_to_" + CurrencyName, , Timestamp, CurrencyValue)


Creating AF Structure

To add a small detail, we can also create an AF structure, by first creating a base currency element in AF.


BaseCurrency.FILTER = C1=="*base*"
Base = ["*base\": \"(*)\""]

An when storing an element, we will also add in an attribute name we can then make reference to it when sending data to the asset servers.


StoreEvent(TagName, CurrencyName, Timestamp, CurrencyValue) 
DynAttrCol = Add(TagName)
StoreElement(Base, "Currency", DynAttrCol)


Last thing, we need to clear the collection, this is for example a required step, when for example an other currency is used, then the list of attributes will be different. This is done via the following message:


EndOfFile.FILTER = C1=="}"
DynAttrCol = Clear()


GitHub PI connector for UFL repository

We are always looking to improve this repository. If you have any data source and ini file that you would like us to showcase, please contact us. Or if you find any issues with the source code, please be sure to post below or post an issue to GitHub!