cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Incoming CSV File Parsing - Populating the Axeda Enterprise

Level 5

Incoming CSV File Parsing - Populating the Axeda Enterprise

These code snippets illustrate parsing CSV files and populating the Axeda Enterprise with data, locations and organizations.  These files are incoming to the Axeda Platform.

Note:  These snippets do NOT handle null values in the CSV due to the lack of a CSV parsing library.  Workaround is to populate empty values with an empty or null signifier (such as a blank space) and test for these on the Groovy side.

Code Snippets:


CSV file to Data Items
CSV file to Location Organization


Script Name: CSV file to Data Items


Description: Executed from an expression rule with file hint "datainsert", takes a CSV file and adds data based on values.


Parameters:

OPTIONAL - only needed for debugging

  1. modelName - (OPTIONAL) Str - name of the model
  2. serialNumber - (OPTIONAL) Str - name of the serial number


import com.axeda.drm.sdk.Context

import com.axeda.drm.sdk.device.DeviceFinder

import com.axeda.drm.sdk.device.ModelFinder

import com.axeda.drm.sdk.device.DataItemFinder

import com.axeda.drm.sdk.device.DataItem

import com.axeda.drm.sdk.data.DataValueEntry

import java.util.regex.Pattern

import groovy.json.*

import com.axeda.drm.services.device.DataItemType

import net.sf.json.JSONObject

/**

* CSVToData.groovy

* -----------------------

*

* Executed from an expression rule with file hint "datainsert", takes a CSV file and adds data based on values.

*

* @note  There must be a column with "model" and one with "serial".  The rest of the columns should be data item names with values

* in the rows. DOES NOT handle null values in CSV.  Workaround is to insert blank spaces in null values and test for those on the Groovy side.

* Solution would be to add a library for CSV parsing such as open csv.

*

* @params - only needed if NOT executed from expression rule - primarily for debugging

* modelName - (OPTIONAL) Str - name of the model

* serialNumber - (OPTIONAL) Str - name of the serial number

*

*

*/

/**

* initialize our global variables

* json = the contents of our response

* infoString = a stringBuilder used to collect debug information during the script

* contentType = the content type we will return

* scriptname = The name of this Script, used in multiple places

*/

def json = new groovy.json.JsonBuilder()

def infoString = new StringBuilder()

def contentType = "application/json"

def scriptName = "CSVToData.groovy"

def root = ["result":["items":[]]]

def columns = []

try {

  Context CONTEXT = Context.getSDKContext()

  def modelIndex

  def serialIndex

  // initialize Model and Device Finders

  ModelFinder modelFinder = new ModelFinder(CONTEXT)

  DeviceFinder deviceFinder = new DeviceFinder(CONTEXT)

  // implicit object compressedFile

  File file = compressedFile.getFiles()[0].extractFile()

/* //begin non-expression rule code, useful for debugging

    File file

    modelFinder.setName(Request.parameters.modelname)

              def model1 = modelFinder.find()

    deviceFinder.setSerialNumber(Request.parameters.serialNumber)

    deviceFinder.setModel(model1)

    def d = deviceFinder.find()

     UploadedFileFinder uff = new UploadedFileFinder(CONTEXT)

    uff.device = d

    def ufiles = uff.findAll()

    UploadedFile ufile

    if (ufiles.size() > 0) {

        ufile = ufiles[0]

        file = ufile.extractFile()

    }

        

*/ //end non-expression rule code

  file.eachLine {line ->

      def row = line.tokenize(',')

  

      // set the column headings

      if (columns.size() == 0){

        columns = row

    

        // find model and serial index, assumes there's a column that has "model" and "serial", otherwise take columns 0 and 1

        def modelpatt = Pattern.compile(/[A-Za-z_\-]{0,}model[A-Za-z_\-]{0,}/, Pattern.CASE_INSENSITIVE)

        def serialpatt = Pattern.compile(/[A-Za-z_\-]{0,}serial[A-Za-z_\-]{0,}/, Pattern.CASE_INSENSITIVE)

        modelIndex = columns.findIndexOf{ it ==~ modelpatt } > -1 ? columns.findIndexOf{ it ==~ modelpatt } : 0

        serialIndex = columns.findIndexOf{ it ==~ serialpatt } > -1 ? columns.findIndexOf{ it ==~ serialpatt } : 1

    

      }

      // otherwise populate data

      else {

      

          modelFinder.setName(row.get(modelIndex))

          def model = modelFinder.find()

      

          deviceFinder.setModel(model)

          deviceFinder.setSerialNumber(row.get(serialIndex))

      

          def device = deviceFinder.find()

      

          def assetInfo = [

                    "model": model.name,

                    "serial": device.serialNumber,

                    "data":[]

                    ]

      

          row.eachWithIndex{ item, index ->

              if (index != modelIndex && index != serialIndex){

                def dataItemName = columns[index].replace(" ","")

                DataItemFinder dif = new DataItemFinder(CONTEXT);

                dif.setDataItemName(dataItemName);

                dif.setModel(model);

                DataItem dataItem = dif.find();

            

                if (dataItem){

                    if (item.isNumber()){

                       item = Double.valueOf(item)

                    }

                    DataValueEntry dve = new DataValueEntry(CONTEXT, device, dataItem, item)

                    dve.store()

                }

                else {

                    DataItem newDataItem

                    if (item.isNumber()){

                        newDataItem = new DataItem(CONTEXT, model,DataItemType.ANALOG, dataItemName)

                        item = Double.valueOf(item)

                    }

                    else {

                       newDataItem = new DataItem(CONTEXT, model,DataItemType.STRING, dataItemName)

                    }

                   newDataItem.store()

                   DataValueEntry dve = new DataValueEntry(CONTEXT, device, newDataItem, item)

                    dve.store()

                }

                assetInfo.data << [

                        "name": dataItemName,

                        "value": item

                    ]

            

              }

              root.result.items << assetInfo

          }

      

      }

  }

  logger.info(JSONObject.fromObject(root).toString(2))

} catch (Exception e) {

    processException(scriptName,json,e)

}

//return ['Content-Type': 'application/json', 'Content': JSONObject.fromObject(root).toString(2)]

/*

    Processes the contents of an Exception and add it to the Errors collection

    @param json The markup builder

*/

private def processException(String scriptName, JsonBuilder json, Exception e) {

    // catch the exception output

    def logStringWriter = new StringWriter()

    e.printStackTrace(new PrintWriter(logStringWriter))

    logger.error("Exception occurred in ${scriptName}: ${logStringWriter.toString()}")

    /*

        Construct the error response

        - errorCode Will be an element from an agreed upon enum

        - errorMessage The text of the exception

     */

    json.errors  {

        error {

            message     "[${scriptName}]: " + e.getMessage()

            timestamp   "${System.currentTimeMillis()}"

        }

    }

    return json

}

Script Name: CSV file to Location Organization


Description: Executed from an expression rule with file hint "locorginsert", takes a CSV file and adds orgs and locations based on values.


Parameters:

OPTIONAL - only needed for debugging

  1. modelName - (OPTIONAL) Str - name of the model
  2. serialNumber - (OPTIONAL) Str - name of the serial number

import com.axeda.drm.sdk.Context

import com.axeda.drm.sdk.device.DeviceFinder

import com.axeda.drm.sdk.device.ModelFinder

import com.axeda.drm.sdk.device.DataItemFinder

import com.axeda.drm.sdk.device.DataItem

import com.axeda.drm.sdk.data.DataValueEntry

import java.util.regex.Pattern

import groovy.json.*

import com.axeda.drm.services.device.DataItemType

import net.sf.json.JSONObject

import com.axeda.drm.sdk.contact.Organization

import com.axeda.drm.sdk.contact.Location

import com.axeda.drm.sdk.contact.OrganizationFinder

import com.axeda.drm.sdk.contact.LocationFinder

import com.axeda.drm.sdk.data.UploadedFile

import com.axeda.drm.sdk.data.UploadedFileFinder

/**

* CSVToLocOrg.groovy

* -----------------------

*

* Executed from an expression rule with file hint "locorginsert", takes a CSV file and adds orgs and locations based on values.

*

* @note  There must be a column with "model" and one with "serial".  The rest of the columns should be either parts of a

* location or an organization.  The location parts columns should be prefixed with the org# that they correspond to.

* DOES NOT handle null values in CSV.  Workaround is to insert blank spaces in null values and test for those on the Groovy side.

* Solution would be to add a library for CSV parsing such as open csv.

*

* @params - only needed if NOT executed from expression rule - primarily for debugging

* modelName - (OPTIONAL) Str - name of the model

* serialNumber - (OPTIONAL) Str - name of the serial number

*

*

*

*/

/**

* initialize our global variables

* json = the contents of our response

* infoString = a stringBuilder used to collect debug information during the script

* contentType = the content type we will return

* scriptname = The name of this Script, used in multiple places

*/

def json = new groovy.json.JsonBuilder()

def infoString = new StringBuilder()

def contentType = "application/json"

def scriptName = "CSVToLocOrg.groovy"

def root = ["result":["items":[]]]

def columns = []

try {

  Context CONTEXT = Context.getSDKContext()

  def modelIndex

  def serialIndex

  def locIndices = [:]

  def locKeys = ["line1","line2", "address1", "address2", "city","state","zip","country", "org"]

  // initialize Finders

  ModelFinder modelFinder = new ModelFinder(CONTEXT)

  DeviceFinder deviceFinder = new DeviceFinder(CONTEXT)

  LocationFinder locationFinder = new LocationFinder(CONTEXT)

  OrganizationFinder organizationFinder = new OrganizationFinder(CONTEXT)

  // implicit object compressedFile

  File file = compressedFile.getFiles()[0].extractFile()

  /* //begin non-expression rule code, useful for debugging

    File file

    modelFinder.setName(Request.parameters.modelname)

              def model1 = modelFinder.find()

    deviceFinder.setSerialNumber(Request.parameters.serialNumber)

    deviceFinder.setModel(model1)

    def d = deviceFinder.find()

     UploadedFileFinder uff = new UploadedFileFinder(CONTEXT)

    uff.device = d

    def ufiles = uff.findAll()

    UploadedFile ufile

    if (ufiles.size() > 0) {

        ufile = ufiles[0]

        file = ufile.extractFile()

    }

         

*/ //end non-expression rule code

  file.eachLine {line ->

      def row = line.tokenize(',')

      // set the column headings

      if (columns.size() == 0){

        columns = row

        // find model and serial index, assumes there's a column that has "model" and "serial", otherwise take columns 0 and 1

        def modelpatt = Pattern.compile(/[A-Za-z_\-]{0,}model[A-Za-z_\-]{0,}/, Pattern.CASE_INSENSITIVE)

        def serialpatt = Pattern.compile(/[A-Za-z_\-]{0,}serial[A-Za-z_\-]{0,}/, Pattern.CASE_INSENSITIVE)

        modelIndex = columns.findIndexOf{ it ==~ modelpatt } > -1 ? columns.findIndexOf{ it ==~ modelpatt } : 0

        serialIndex = columns.findIndexOf{ it ==~ serialpatt } > -1 ? columns.findIndexOf{ it ==~ serialpatt } : 1

     

        locKeys.each{ key ->

            // construct a regex for each key and create a map for finding/creating

            def locPatt = Pattern.compile(/[A-Za-z0-9_\-]{0,}${key}[A-Za-z0-9_\-]{0,}/, Pattern.CASE_INSENSITIVE)

            def colIndex = columns.findIndexOf{

                    def match = it =~ locPatt

                    if (match){

                        return match?.getAt(0)

                    }

                }

         

            if (colIndex > -1){

                locIndices[colIndex] = key

            }

        }

      }

      // otherwise populate data

      else {

          modelFinder.setName(row.get(modelIndex))

          def model = modelFinder.find()

          deviceFinder.setModel(model)

          deviceFinder.setSerialNumber(row.get(serialIndex))

          def device = deviceFinder.find()

          def assetInfo = [

                    "model": model.name,

                    "serial": device.serialNumber,

                    "locs":[]

                    ]

       

          def locMap = [:]

          def orgName

          def locKey

          def locBool = false // make sure we get some criteria

          row.eachWithIndex{ item, index ->

            

              if (index != modelIndex && index != serialIndex && item && item != ""){

                  locKey = locIndices[index]

               

                  if (locKey){

                      locBool = true

                      if (locKey == "address1"){

                        locKey = "line1"

                      }

                      if (locKey == "address2"){

                        locKey = "line2"

                      }

                      if (locKey == "org"){

                            orgName = item

                      }

                      // don't execute if we've got an organization key

                      else {

                          // for finding

                          locationFinder[locKey] = item

                          // for creating (if needed)

                          locMap[locKey] = item

                      }

                  }

               

              }

          }

       

          assetInfo.org

          Organization org

       

          if (orgName){

              organizationFinder.setName(orgName)

              org = organizationFinder.find()

           

              if (!org){

                org = new Organization(CONTEXT, orgName)

                org.store()

  

              }

           

          }

      

          Location loc

          if (locBool){

              logger.info("with bool")

            loc = locationFinder.find()

            logger.info(loc?.name)

          }

       

          if (!loc){

          

              def line1 = locMap["line1"]

           

              def name = line1?.replace(" ","")?.replace(/\./,"")?.replace("_","") + "_Loc"

           

              def line2 = locMap["line2"]

              def city = locMap["city"]

              def state = locMap["state"]

              def zip = locMap["zip"]

              def country = locMap["country"]

           

              if (line1 && city){

               loc = new Location(CONTEXT,name,line1,line2,city,state,zip,country)

               loc.store()

            

            

              }

           

              if (loc && org){

                  org.addLocation(loc)

                  org.store()

              }

           

          }

       

          assetInfo.locs << [

                   "name": loc.name,

                    "line1": loc.line1,

                    "line2": loc.line2,

                    "city": loc.city,

                    "state": loc.state,

                    "zip": loc.zip,

                    "country": loc.country

                 

                   ]

                   assetInfo.org = [

                        "name": org.name

                 

                    ]

          root.result.items << assetInfo

      }

  }

  logger.info(JSONObject.fromObject(root).toString(2))

} catch (Exception e) {

    processException(scriptName,json,e)

}

//return ['Content-Type': 'application/json', 'Content': JSONObject.fromObject(root).toString(2)]

/*

    Processes the contents of an Exception and add it to the Errors collection

    @param json The markup builder

*/

private def processException(String scriptName, JsonBuilder json, Exception e) {

    // catch the exception output

    def logStringWriter = new StringWriter()

    e.printStackTrace(new PrintWriter(logStringWriter))

    logger.error("Exception occurred in ${scriptName}: ${logStringWriter.toString()}")

    /*

        Construct the error response

        - errorCode Will be an element from an agreed upon enum

        - errorMessage The text of the exception

     */

    json.errors  {

        error {

            message     "[${scriptName}]: " + e.getMessage()

            timestamp   "${System.currentTimeMillis()}"

        }

    }

    return json

}

Tags (1)