Comma Separated Values Format

This page describes the comma separated values format sample implementation.

Sample File Format

In this sample, data is stored as a comma separated values file.

Note: Values in this example cannot contain a literal comma character. This restriction is added for the simplicity of file parser implementation. The parsing logic can be re-implemented to avoid this restriction. It will not affect the rest of functionality.

The sample CSV file contains the following record types:

Interval Usage: Used for interval usage measurements

Device Events: Used for device events

Trail record: Used to provide the read date/time and number of interval usage and device event records in the file

Interval Usage Format

#

Field Name

Type

Definition

1

Record Type ID

Constant value “U”

Record type identifier

2

Start Date/Time

Time in the Unix Time format.

Meter read’s start date/time

3

End Date/Time

Time in the Unix Time format.

Meter read’s end date/time

4

Device Id

Arbitrary Text

Device identifier

5

Interval Duration

Numeric Integer

The time interval between readings in seconds (seconds per interval).

6

UOM

Arbitrary Text

Describes the units of the data values

7

Data Entry(s)

Numeric Floating-Point and optional Arbitrary Text separated by “:”

Each record can contain unlimited number of data entry fields. Each data entry is a set of two fields – a reading value and an optional reading status flag. Fields are separated by “:” character. The separator is not present when the reading status flag is not provided.

Device Event Format

#

Field Name

Type

Definition

1

Record Type ID

Constant value “E”

Record type identifier

2

Event Date/Time

Time in the Unix Time format.

The date and time at which the event occurred

3

Device Id

Arbitrary Text

Device identifier

4

Event Name

Arbitrary Text

The primary identification name of the event

Trail Record Format

#

Field Name

Type

Definition

1

Record Type ID

Constant value “T”

Record type identifier

2

Creation Date/Time

Time in the Unix Time format.

Meter read’s start date/time

3

Total Records

Numeric Integer

Displays number of records in file

Sample File

U,1,86400,DEVICE_DG_0,900,KWH,1:S,2.5,-3.99:R,4:B
E,86400,DEVICE_DG_1,Power Outage
U,2,86400,DEVICE_DG_2,900,KWH,1:S,2,3:R,4:B,5,6,7:A
E,86402,DEVICE_DG_3,Tamper attempt suspected
U,3,86400,DEVICE_DG_4,900,KWH,1:S
T,86401,5

Sample Scripts

The sample implementation includes the following sample scripts designed for use with the above CSV format:

DG_​SmplPrCSV: This sample script includes one method in the Library Interface – getParser(). The method returns an instance of the class that implements the com.splwg.d1.domain.sgg.dg.processing.PayloadParser interface. The class contains the following logic:

  1. Parses an incoming payload in the CSV Format.

  2. Transforms incoming data to Usage or Event related structures based on incoming data type

  3. Returns one by one that structures in the Plain XML format

DG_​SmPrCSVCh: This script is provided as a sample to demonstrate how to parse the CSV format in chunks (created via the chunkSize parameter) by utilizing a helper. This sample script includes one method in the Library Interface – getParser(). The method returns an instance of the class that implements the com.splwg.d1.domain.sgg.dg.processing.ChunkProcessor and com.splwg.d1.domain.sgg.dg.processing.GenericPayload interfaces.

The ChunkProcessor interface distinguishes a chunking-aware processor from the rest of implementations. A custom parser implemented in groovy can implement this interface. The GenericPayload interface defines methods needed to get access to a payload via specific helpers. Via this interface, a custom parser gets access to an instance of LineReaderChunked.

If the chunkSize dynamic parameter is not set in an SGG Payload Processing configuration the entire payload will be processed. See Common Parameters for more information about the chunkSize parameter.

The class contains the following logic:

  1. Reads a line from incoming payload by utilizing the LineReaderChunked.readLineWithinChunk() method

  2. Parses a line in the incoming payload (in the CSV Format) that was read in the previous step.

  3. Transforms incoming data to Usage or Event related structures based on incoming data type

  4. Returns one by one that structures in the Plain XML format

DG_​SmplTrCSV: This sample script includes one method in the Library Interface – getTransformer(). The method returns an instance of class that implements the com.splwg.d1.domain.sgg.dg.processing.PayloadTransformer interface. The class contains the following logic:

  1. Transforms incoming message in the Plain XML format into the “native” format (the IMD Seeder or Device Event Seeder XML nodes).

  2. Adds new XML nodes as child nodes to the given Result List.

Use the Script portal to view these scripts in more detail.

Parsing

The parser script performs the following data mapping.

Interval Usage to Plain XML Mapping

The following table shows the mapping between fields in incoming interval data and child elements of Payload/Usage element in the Plain XML format:

Interval Usage Field Plain XML Element

Record Type ID

RecordType

Start Date/Time

StartDateTime

End Date/Time

EndDateTime

Device Id

DeviceId

Interval Duration

IntervalDuration

UOM

UOM

Data Entry(s)

Intervals/

Interval/

Value

[Status]

Record content from incoming file.

RawData

Device Event to Plain XML Mapping

The following table shows the mapping between fields in incoming interval data and child elements of Payload/Event element in the Plain XML format:

Device Event Field Plain XML Element

Record Type ID

RecordType

Event Date/Time

DateTime

Device Id

DeviceId

Event Name

Name

Record content from incoming file.

RawData

XML Schema of Plain XML Format

The Plain XML consists of the Interval Usage and Device Event related elements at the same time.

<xs:schema attributeFormDefault="unqualified" 
elementFormDefault="qualified" 
targetNamespace="http://xmlns.oracle.com/GenericAdapter" 
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:ga="http://xmlns.oracle.com/GenericAdapter">
  <xs:element name="Payload" type="ga:PayloadType"/>
  <xs:complexType name="PayloadType">
    <xs:sequence>
      <xs:choice>
        <xs:element name="Usage" type="ga:UsageType"/>
        <xs:element name="Event" type="ga:EventType"/>
      </xs:choice>
      <xs:element name="RawData"  type="ga:NonEmptyString"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="UsageType">
    <xs:sequence>
      <xs:element name="RecordType" type="ga:RecordTypeUsageType"/>
      <xs:element name="StartDateTime" type="xs:integer"/>
      <xs:element name="EndDateTime" type="xs:integer"/>
      <xs:element name="DeviceId" type="ga:NonEmptyString"/>
      <xs:element name="IntervalDuration" type="xs:integer"/>
      <xs:element name="UOM" type="ga:NonEmptyString"/>
      <xs:element name="Intervals" type="ga:IntervalList"/>
    </xs:sequence>
  </xs:complexType>
  <xs:simpleType name="RecordTypeUsageType">
    <xs:restriction base="xs:string">
      <xs:enumeration value="U"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:complexType name="IntervalList">
    <xs:sequence maxOccurs="unbounded">
      <xs:element name="Interval" type="ga:IntervalType"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="IntervalType">
    <xs:sequence>
      <xs:element name="Value" type="xs:float"/>
      <xs:element name="Status" type="ga:NonEmptyString" minOccurs="0"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="EventType">
    <xs:sequence>
      <xs:element name="RecordType" type="ga:RecordTypeEventType"/>
      <xs:element name="DateTime" type="xs:integer"/>
      <xs:element name="DeviceId" type="ga:NonEmptyString"/>
      <xs:element name="Name" type="ga:NonEmptyString"/>
    </xs:sequence>
  </xs:complexType>
  <xs:simpleType name="RecordTypeEventType">
    <xs:restriction base="xs:string">
      <xs:enumeration value="E"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="NonEmptyString">
    <xs:restriction base="xs:string">
      <xs:minLength value="1"/>
    </xs:restriction>
  </xs:simpleType>
</xs:schema>
Note: Note: A part of incoming file that been used to generate current Plain XML structure will be placed into the Payload/RawData element.

Transformation

The transformer script performs the following mapping.

Plain XML to Seeder XML Mapping

The payload transformer creates the IMD or/and Device Event Seeder XML elements as following:

Usage Mapping: If name of Payload/* node is equal to 'Usage' (interval usage), the script creates an XML element with a name defined in the SGG Payload Processing Configuration field “IMD Seeder BO (Interval)” and the structure according to the following mapping table:

Plain XML Element

IMD Seeder Element

Notes

RecordType

N/A

DeviceId

dvcIdN

UOM

externalUOM

IntervalDuration

spi

StartDateTime

stDt

Convert to OUAF "dateTime" type

EndDateTime

enDt

Convert to OUAF "dateTime" type

Intervals/

Interval/

Value [Status]

msrs

Value

msrs/mL/q

Status ->

msrs/mL/sts/stsL/st

N/A

imdType

Const value 'D1IL'

externalId

Value stored in setOrigin method.

serviceProviderExternalId

Value stored in setConfiguration method.

RawData

rawData

If the SGG Payload Processing Configuration has the Populate RAW checkbox checked.

Device Event Mapping: If name of Payload/* node is equal to 'Event' (device event data) create an XML element with a name defined in the SGG Payload Processing Configuration field “Device Event Seeder BO” and the structure according to the following mapping table:

Plain XML Element

Device Event Seeder Element

Note

RecordType

N/A

DateTime

eventDateTime

Convert to OUAF "dateTime" type

DeviceId

externalUOM

Name

externalEventName

externalSourceIdentifier

Value stored in setOrigin method.

externalSenderId

Value stored in setConfiguration method.

RawData

rawEventInformation

If the SGG Payload Processing Configuration has the Populate RAW checkbox checked.