Tips for Looping through files that are not CSV or JSON with PI Connector for UFL

Tips for Looping through files that are not CSV or JSON with PI Connector for UFL

Recently when working with a new type of report that needed to be processed via the PI Connector for UFL, I came across the need to loop through sections of an xml file. Official documentation states that the looping function (FOR EACH) only works for JSONs or CSVs (https://docs.aveva.com/bundle/pi-connector-for-ufl/page/1010394.html) since it depends on CsvGetItem or JsonGetItem functions.

There is a workaround for other data types though. Both CsvGetItem and JsonGetItem can be used with the ForEach loop and extra functions such as INSTR, SUBSTR, REPLACE, LEFT, RIGHT may be used to parse through the resulting __ITEM object. Defining a different DELIMITER that suits the available source file better is key.

Attached to this post is a data sample and an ini file that works for it.

Details

Iterating through comma-separated values: Before the FOREACH() statement got introduced, dealing with comma-separated inputs with variable numbers of items (columns) was obstructive. The reason was because the input could contain a csv with x columns and during the next run it could have been different. With FOREACH(), the syntax is now more flexible, it is adaptable to the varying content of the csv input.

As per PI World 2019 Lab Programmability in the PI Connector for UFL:

Predefined variables when using CsvGetItem or JsonGetItem

• __MESSAGE: The content of the current message (line).
• __ITEM: A string variable which is assigned a value each time the JsonGetItem() or CsvGetItem() functions 
• are evaluated.
• __ITEM_Name: A string variable which is assigned the name of the selected JSON element each time the JsonGetItem() function is evaluated.

For XML or other types of data files, use a different DELIMITER on CsvGetItem.

A short example:

<Results>
  <ResultData>
    <Label>StartDate</Label>
    <Value>2025-01-01T00:00:00</Value>
  </ResultData>
  <Points>
    <point>
      <time>7.45835</time>
      <flow>124.85</flow>
      <PointNumber>1</PointNumber>
    </point>
    <point>
      <time>7.58335</time>
      <flow>122.84</flow>
      <PointNumber>2</PointNumber>
    </point>
    <point>
      <time>7.70835</time>
      <flow>120.86</flow>
      <PointNumber>3</PointNumber>
    </point>
  </Points>
</Results>

The main MSG statement to loop through the time and flow values (full ini is attached and contains the FIELD definitions and more):

[PlotPoints]
PlotPoints.FILTER=C1=="*<Points>*"

PlotTime_tag                        = "Plot Time"
PlotFlow_tag                        = "Plot Flow"

Counter = 0
FOREACH (CSVGetItem(__MESSAGE, "</point>")) DO
	'print("new iteration loop")

	PositionPointTime = INSTR(__ITEM, "<time>", 1, 1)
	PositionPointFlow = INSTR(__ITEM, "<flow>", 1, 1)

	PlotTime_String = REPLACE(REPLACE(SUBSTR(__ITEM, PositionPointTime+1, INSTR(__ITEM,"</time>",1,1)-PositionPointTime), "time>", ""), "<", "")
	PlotFlow_String = REPLACE(REPLACE(SUBSTR(__ITEM, PositionPointFlow+1, INSTR(__ITEM,"</flow>",1,1)-PositionPointFlow), "flow>", ""), "<", "")

	IF LEN(PlotTime_String)>0 THEN
		PlotTime = PlotTime_String
		if PlotTime is not null then StoreInPI(PlotTime_tag,,StartDate+TimeStep*Counter,PlotTime,,) endif
	ENDIF

	IF LEN(PlotFlow_String)>0 THEN
		PlotFlow = PlotFlow_String
		if PlotFlow is not null then StoreInPI(PlotFlow_tag,,StartDate+TimeStep*Counter,PlotFlow,,) endif
	ENDIF

	Counter = Counter + 1
ENDFOR

This strategy is based on the following:

• Using FOREACH and CSVGetItem to loop through each <point>*</point> section
• Using INSTR to find the position of the desired data
• Using REPLACE and SUBSTR to retrieve the numbers, using the INSTR position to support these functions
• Casting the string to a number (PlotTime and PlotFlow should be Doubles in PI Points)
• Storing each data point to a slightly offset timestamp (defined by StartDate+TimeStep*Counter in the StoreInPI function) - this was used to build an XY plot in PI Vision

If the user knows the UOM of the time values, those could potentially replace the TimeStep variable so that it the actual TimeStamp is exact and not a stepped one.

With this, developers can still iterate through files that are not JSON or CSV and pick up multiple pairs of values-timestamps and historise in PI Points for later trending.

• exampleLoopingXML.ini
• sampleData.xml