Skip to content

Instantly share code, notes, and snippets.

@mrtomeq

mrtomeq/Polyworks_CSV_to_DB.json

Created February 8, 2021 02:25
Show Gist options
  • Save mrtomeq/ce1c695e561cd1c52d4deaca708b40b6 to your computer and use it in GitHub Desktop.
Save mrtomeq/ce1c695e561cd1c52d4deaca708b40b6 to your computer and use it in GitHub Desktop.
Download ZIP
Polyworks CSV to DB
Raw
Polyworks_CSV_to_DB.json
[
{
"id": "9d048712.32d248",
"type": "tab",
"label": "Polyworks CSV to DB",
"disabled": false,
"info": ""
},
{
"id": "de960753.d25e38",
"type": "inject",
"z": "9d048712.32d248",
"name": "Source",
"props": [
{
"p": "sourcePath",
"v": "I:\\Polyworks\\",
"vt": "str"
},
{
"p": "archivePath",
"v": "I:\\Polyworks\\archive",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"x": 110,
"y": 100,
"wires": [
[
"50fc950e.7a184c"
]
]
},
{
"id": "50fc950e.7a184c",
"type": "fs-ops-dir",
"z": "9d048712.32d248",
"name": "List CSV files",
"path": "sourcePath",
"pathType": "msg",
"filter": "*.csv",
"filterType": "str",
"dir": "payload",
"dirType": "msg",
"x": 270,
"y": 100,
"wires": [
[
"aed12841.c92948"
]
]
},
{
"id": "aed12841.c92948",
"type": "split",
"z": "9d048712.32d248",
"name": "Split file list",
"splt": "\\n",
"spltType": "str",
"arraySplt": 1,
"arraySpltType": "len",
"stream": false,
"addname": "",
"x": 450,
"y": 100,
"wires": [
[
"dabfcc49.495ca"
]
]
},
{
"id": "dabfcc49.495ca",
"type": "change",
"z": "9d048712.32d248",
"name": "Set part properties",
"rules": [
{
"t": "set",
"p": "file",
"pt": "msg",
"to": "payload",
"tot": "msg"
},
{
"t": "set",
"p": "filename",
"pt": "msg",
"to": "$join([sourcePath,payload])\t",
"tot": "jsonata"
},
{
"t": "set",
"p": "filenameShort",
"pt": "msg",
"to": "$substringBefore(payload, '.csv')\t",
"tot": "jsonata"
},
{
"t": "delete",
"p": "parts",
"pt": "msg"
},
{
"t": "set",
"p": "measurementTime",
"pt": "msg",
"to": "$replace($split(filenameShort, \"_\")[1],\"-\", \":\")",
"tot": "jsonata"
},
{
"t": "set",
"p": "measurementDate",
"pt": "msg",
"to": "$moment(\t$split(filenameShort, \"_\")[0] &\" \" &measurementTime\t,\"YYYY-MM-DD hh:mm:ss\")\t",
"tot": "jsonata"
},
{
"t": "set",
"p": "partNumber",
"pt": "msg",
"to": "$split(filenameShort, \"_\")[2]",
"tot": "jsonata"
},
{
"t": "set",
"p": "partName",
"pt": "msg",
"to": "$split(filenameShort, \"_\")[3]",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 650,
"y": 100,
"wires": [
[
"51d16e3.e274b9"
]
]
},
{
"id": "51d16e3.e274b9",
"type": "file in",
"z": "9d048712.32d248",
"name": "Read file",
"filename": "",
"format": "utf8",
"chunk": false,
"sendError": false,
"encoding": "utf8",
"x": 840,
"y": 100,
"wires": [
[
"1e71cf84.88842"
]
]
},
{
"id": "1e71cf84.88842",
"type": "csv",
"z": "9d048712.32d248",
"name": "Parse CSV",
"sep": ",",
"hdrin": true,
"hdrout": "none",
"multi": "one",
"ret": "\\n",
"temp": "",
"skip": "2",
"strings": true,
"include_empty_strings": "",
"include_null_values": "",
"x": 1010,
"y": 100,
"wires": [
[
"3a791561.9e708a"
]
]
},
{
"id": "9464eb1e.7c1aa8",
"type": "template",
"z": "9d048712.32d248",
"name": "Generate SQL",
"field": "payload",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "\n-- Insert new part and return partID if operation succeeded\nINSERT INTO parts (number, name) VALUES('{{partNumber}}','{{partName}}') ON CONFLICT(number) DO NOTHING;\n\n\nINSERT INTO features (partid, name, type)\nVALUES((SELECT id FROM parts WHERE number = '{{partNumber}}'), '{{payload.Object}}', null) \nON CONFLICT(partid, name) DO NOTHING;\n\n\nINSERT INTO characteristics (featureId, name, nominal, usl, lsl)\n VALUES(\n\t\t (SELECT features.id \n\t\t FROM features INNER JOIN parts ON features.partId = parts.id\n\t\t WHERE parts.number = '{{partNumber}}'\n\t\t AND features.name = '{{payload.Object}}'),\n\t\t '{{payload.Control}}', {{payload.Nominal}}, {{usl}}, {{lsl}}) \nON CONFLICT(featureid, name) DO NOTHING;\n\n\t\n{{#pieces}}\n\nINSERT INTO measurements (characteristicid, value, timestamp)\nSELECT\n\tcharacteristics.Id AS characteristicId,\n\t{{value}} as value,\n\t'{{date}}' as timestamp\nFROM\n\tcharacteristics \nINNER JOIN features ON characteristics.featureId = features.id \nINNER JOIN parts ON features.partId = parts.id\nWHERE parts.number = '{{partNumber}}'\nAND features.name = '{{payload.Object}}'\nAND characteristics.name = '{{payload.Control}}'\nON CONFLICT(characteristicid, timestamp) DO NOTHING;\n\n{{/pieces}}\n",
"output": "str",
"x": 297,
"y": 180,
"wires": [
[
"7e3d1648.a5d678"
]
]
},
{
"id": "3a791561.9e708a",
"type": "change",
"z": "9d048712.32d248",
"name": "Prep. measurements",
"rules": [
{
"t": "set",
"p": "pieces",
"pt": "msg",
"to": "$map($filter($keys(payload), function($v, $i, $a) {\t $substring($v, 0, 5) = 'Piece'\t}), \tfunction($v, $i, $a) {\t {\t \"value\": $lookup(payload, $v), \t \"date\": $moment(measurementDate).add($i, 'seconds').format()\t }\t})\t",
"tot": "jsonata"
},
{
"t": "set",
"p": "payload.Nominal",
"pt": "msg",
"to": "$exists(payload.Nominal) ? payload.Nominal: 0",
"tot": "jsonata"
},
{
"t": "set",
"p": "lsl",
"pt": "msg",
"to": "$type(payload.Tolerance) = \"number\"\t?\tpayload.Tolerance\t:\t$contains(payload.Tolerance, \"±\")\t ? \t $number($substring(payload.Tolerance, 1))*-1\t :\t $substring($substringAfter(payload.Tolerance, \"/\"), 0, 1) = \"-\"\t ?\t $number($substring($substringAfter(payload.Tolerance, \"/\"), 1))*-1\t :\t $number($substring($substringAfter(payload.Tolerance, \"/\"), 1))\t\t\t\t",
"tot": "jsonata"
},
{
"t": "set",
"p": "usl",
"pt": "msg",
"to": "$type(payload.Tolerance) = \"number\"\t?\tpayload.Tolerance\t:\t$contains(payload.Tolerance, \"±\")\t ? \t $number($substring(payload.Tolerance, 1))\t :\t $substring(payload.Tolerance, 0, 1) = \"+\"\t ?\t $number($substringBefore($substring(payload.Tolerance, 1), \"/\"))\t :\t $number($substringBefore($substring(payload.Tolerance, 1), \"/\"))*-1\t\t\t\t",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1220,
"y": 100,
"wires": [
[
"9464eb1e.7c1aa8"
]
]
},
{
"id": "192cdf6f.49bf41",
"type": "postgres",
"z": "9d048712.32d248",
"postgresdb": "701d5a17.0e1664",
"name": "Save to DB",
"output": true,
"perrow": false,
"rowspermsg": "1",
"outputs": 1,
"x": 490,
"y": 180,
"wires": [
[
"7e3d1648.a5d678"
]
]
},
{
"id": "26326a0b.a930e6",
"type": "fs-ops-move",
"z": "9d048712.32d248",
"name": "File archive",
"sourcePath": "sourcePath",
"sourcePathType": "msg",
"sourceFilename": "file",
"sourceFilenameType": "msg",
"destPath": "archivePath",
"destPathType": "msg",
"destFilename": "file",
"destFilenameType": "msg",
"link": false,
"x": 1210,
"y": 180,
"wires": [
[]
]
},
{
"id": "7c1d5886.8bc768",
"type": "fs-ops-access",
"z": "9d048712.32d248",
"name": "Archive access",
"path": "archivePath",
"pathType": "msg",
"filename": "file",
"filenameType": "msg",
"read": true,
"write": true,
"throwerror": false,
"x": 820,
"y": 180,
"wires": [
[
"26326a0b.a930e6"
],
[
"630626c2.e9cbd8"
]
]
},
{
"id": "7e3d1648.a5d678",
"type": "join",
"z": "9d048712.32d248",
"name": "",
"mode": "auto",
"build": "string",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": false,
"timeout": "",
"count": "",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 650,
"y": 180,
"wires": [
[
"7c1d5886.8bc768"
]
]
},
{
"id": "630626c2.e9cbd8",
"type": "fs-ops-mkdir",
"z": "9d048712.32d248",
"name": "Make Archive",
"path": "sourcePath",
"pathType": "msg",
"dirname": "archive",
"dirnameType": "str",
"recursive": false,
"mode": "777",
"fullpath": "directory",
"fullpathType": "msg",
"x": 1020,
"y": 240,
"wires": [
[
"26326a0b.a930e6"
]
]
},
{
"id": "701d5a17.0e1664",
"type": "postgresdb",
"hostname": "localhost",
"port": "5432",
"db": "postgres",
"ssl": false
}
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment