Excel export to JSON

[psysak]

Hey everyone, I was just wondering if anyone here might have an Excel workbook already done that allows creating points, specifically META in my case, and then exports that to JSON? If you'd be willing to share I'd appreciate that.

[phildunlap]

Hi psysak,

It's not exactly what you requested, but it's a similar gist for an option. There may be more excel based options - but I like the python based options so that's the main angle I'd come at it from.

Here's a CSV of modbus points (Excel --> Export as CSV, or you can export using another separator you don't use in the data):

Modbus Register,# of Modbus Registers,Description,Format,Properties
43012,2,Record Number (x),UINT32,Read
43014,2,UTC Seconds,UINT32,Read
43016,2,UTC MicroSeconds,UINT32,Read
43018,2,Source 1 Input Data,FLOAT,Read
43020,2,Source 2 Input Data,FLOAT,Read
43022,2,Source 3 Input Data,FLOAT,Read
43024,2,Source 4 Input Data,FLOAT,Read
43026,2,Source 5 Input Data,FLOAT,Read
43028,2,Source 6 Input Data,FLOAT,Read
43030,2,Source 7 Input Data,FLOAT,Read
43032,2,Source 8 Input Data,FLOAT,Read
43034,2,Source 9 Input Data,FLOAT,Read
43036,2,Source 10 Input Data,FLOAT,Read
43038,2,Source 11 Input Data,FLOAT,Read
43040,2,Source 12 Input Data,FLOAT,Read
43042,2,Source 13 Input Data,FLOAT,Read
43044,2,Source 14 Input Data,FLOAT,Read
43046,2,Source 15 Input Data,FLOAT,Read
43048,2,Source 16 Input Data,FLOAT,Read
43088,2,Record Number (x+2),UINT32,Read
43090,2,UTC Seconds,UINT32,Read
43092,2,UTC MicroSeconds,UINT32,Read
43094,2,Source 1 Input Data,FLOAT,Read
43096,2,Source 2 Input Data,FLOAT,Read
43098,2,Source 3 Input Data,FLOAT,Read
43100,2,Source 4 Input Data,FLOAT,Read

And here's my python script that consumes it:

import re

modbusDataTypes = {"FLOAT":"FOUR_BYTE_FLOAT", "UINT32":"FOUR_BYTE_INT_UNSIGNED"}
xidPrefix = "SJ3_"
baseModbusPoint = """{
         "xid":"%(xid)s",
         "name":"%(name)s",
         "enabled":true,
         "loggingType":"INTERVAL",
         "intervalLoggingPeriodType":"MINUTES",
         "intervalLoggingType":"AVERAGE",
         "purgeType":"YEARS",
         "pointLocator":{
            "range":"HOLDING_REGISTER",
            "modbusDataType":"%(modbusDataType)s",
            "writeType":"NOT_SETTABLE",
            "additive":0.0,
            "bit":0,
            "charset":"ASCII",
            "multiplier":1.0,
            "offset":%(offset)s,
            "registerCount":2,
            "slaveId":1,
            "slaveMonitor":false
         },
         "eventDetectors":[
         ],
         "plotType":"SPLINE",
         "unit":"",
         "templateXid":"Numeric_Default",
         "chartColour":"black",
         "chartRenderer":{
            "type":"IMAGE",
            "timePeriodType":"DAYS",
            "numberOfPeriods":1
         },
         "dataSourceXid":"DS_849061",
         "defaultCacheSize":1,
         "deviceName":"test_mod 502",
         "discardExtremeValues":false,
         "discardHighLimit":1.7976931348623157E308,
         "discardLowLimit":-1.7976931348623157E308,
         "intervalLoggingPeriod":1,
         "intervalLoggingSampleWindowSize":0,
         "overrideIntervalLoggingSamples":false,
         "purgeOverride":false,
         "purgePeriod":1,
         "readPermission":"",
         "setPermission":"",
         "textRenderer":{
            "type":"ANALOG",
            "useUnitAsSuffix":true,
            "unit":"",
            "renderedUnit":"",
            "format":"0.00"
         },
         "tolerance":0.0
      }"""
	  
modbusRegisterMap = open("simpleModbusCSV.csv")

outputFile = open("modbusGeneratorOut.json", "w+")
outputFile.write("{")
outputFile.write("\"dataPoints\":[")
for line in modbusRegisterMap :
	fields = line.split(",")
	if not len(fields) > 1 or re.search("^4",fields[0]) is None :
		continue
	holdingOffset = re.search("4([0-9]+)", fields[0]).group(1)
	outputFile.write(baseModbusPoint % {"name": fields[2],"xid": xidPrefix+fields[2].replace(" ", "_"), "offset": holdingOffset, "modbusDataType":modbusDataTypes[fields[3]],})
outputFile.write("]}")
outputFile.close()
modbusRegisterMap.close()

You can see all i'm doing is setting the name, xid, offset, and modbusDataType for the points and writing out a {"dataPoints":[ ... ]} list to modbusGeneratorOut.json