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 modelName - (OPTIONAL) Str - name of the model 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 modelName - (OPTIONAL) Str - name of the model 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 }
View full tip
