Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Community Tip - Stay updated on what is happening on the PTC Community by subscribing to PTC Community Announcements. X
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Notify Moderator
Incoming CSV File Parsing - Populating the Axeda Enterprise
No ratings
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
}
