OK, as you haven't replied, I cant test the DATABASE input and I've had to make several assumptions (i.e. there are likely some problems but you should be able to correct them)...
First, a screen shot demonstrating my earlier post...
This is the transform function - it is heavily commented - you should try to understand it
/*** transform payload object into a nice new object ***/
//A function to convert the DATE and TIME into a format the database likes
function formatDT(datepart, timepart){
//split the dd/mm/yyy string by / into a 3 part array
let dateparts = datepart.split('/');
//now put the date parts (in reverse order)
//back into a string and add the time part
let newDateStr = `${dateparts[2]}/${dateparts[1]}/${dateparts[0]} ${timepart}`
return newDateStr;
}
//get
//store original in a msg property (for checking in debug output)
msg.origPayload = msg.payload;
//make a copy of the row data
let row = {...msg.payload};
//example data 11/12/2019;13:47:31;END;180302bt;0100/37707;2
//Giorno- - 11/12/2019
//Ora - 13:47:31
//Stato - END
//Programma - 180302bt
//Lotto - 0100/37707
//Scheda - 1
var lottoParts = row.Lotto.split("/");
msg.payload = {
a: 9999, //not sure what this is about?
DateTime: formatDT(row.Giorno, row.Ora), //Call a function to make the correct DATETIME string
LottoRight5 : lottoParts[1], //characters right of the '/'
LottoLeft4: lottoParts[0], //characters left of the '/'
Stato: row.Stato, //add Stato to the payload
Programma: row.Programma, //add Programma to the payload
Scheda: row.Scheda //add Scheda to the payload
}
//Now we have an object with the clean values to write into DB
//return the msg with nicely formatted data, ready to insert straight into the query {{mustach}}
return msg;
Now the clean values in payload can be mapped straight into the SQL query...
INSERT INTO Output_Produzione (
Centro_di_Lavoro,
data,
n_ord_prod,
n_operazione,
articolo,
VarINT1
) VALUES (
payload.a, --not certain what this is supposed to be
'{{payload.DateTime}}',
'{{payload.LottoRight5}}',
'{{payload.LottoLeft4}}',
'{{payload.Programma}}',
'{{payload.Scheda}}'
}
This is the flow...
[{"id":"87e2527.bb1f0b","type":"inject","z":"a96cce5.282c93","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":60,"wires":[["44a9df5d.d3ce3"]]},{"id":"e25bb35b.d6c6c","type":"csv","z":"a96cce5.282c93","name":"msg","sep":";","hdrin":"","hdrout":false,"multi":"one","ret":"\\n","temp":"Giorno, Ora, Stato, Programma, Lotto, Scheda","skip":"0","strings":true,"x":410,"y":120,"wires":[["9cd5b277.d0c9f","e14f2462.7363a8"]]},{"id":"44a9df5d.d3ce3","type":"file in","z":"a96cce5.282c93","name":"Quadra DVC EVO","filename":"\\\\10.0.0.4\\log_quadra\\1102_job_test.txt","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":290,"y":60,"wires":[["e25bb35b.d6c6c"]]},{"id":"39634da4.b9c5c2","type":"MSSQL","z":"a96cce5.282c93","mssqlCN":"47f92084.1a0af","name":"SQL_Insert","query":"INSERT INTO Output_Produzione (\n Centro_di_Lavoro, \n data, \n n_ord_prod, \n n_operazione, \n articolo, \n VarINT1\n) VALUES (\n payload.a,\n '{{payload.DateTime}}',\n '{{payload.LottoRight5}}',\n '{{payload.LottoLeft4}}',\n '{{payload.Programma}}',\n '{{payload.Scheda}}'\n}","outField":"payload","returnType":"0","throwErrors":"0","x":610,"y":180,"wires":[["c0253a0d.0aba38"]]},{"id":"3e144157.4a543e","type":"catch","z":"a96cce5.282c93","name":"","scope":["39634da4.b9c5c2"],"uncaught":false,"x":570,"y":260,"wires":[["f85477fe.4e9cf8"]]},{"id":"f85477fe.4e9cf8","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":260,"wires":[]},{"id":"c0253a0d.0aba38","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":180,"wires":[]},{"id":"13de8553.8c230b","type":"inject","z":"a96cce5.282c93","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":120,"wires":[["ea008fb6.193d"]]},{"id":"ea008fb6.193d","type":"template","z":"a96cce5.282c93","name":"fake data","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"02/12/2019;15:21:44;START;132300A;0030/23456;1\n02/12/2019;15:22:47;END;132300A;0030/23456;2\n02/12/2019;16:18:38;START;132400A;0030/23457;10\n02/12/2019;16:19:41;END;112400A;0030/23457;11\n02/12/2019;16:28:44;START;112400A;0030/23458;11\n02/12/2019;16:30:46;END;112400A;0030/23458;12\n02/12/2019;18:32:40;START;112800A;0030/23459;99\n02/12/2019;18:34:44;END;112800A;0030/23459;101\n03/12/2019;10:25:30;START;112800A;0030/23459;102\n03/12/2019;10:27:34;END;112800A;0030/23459;103\n03/12/2019;11:15:40;START;113900A;0030/23459;104\n03/12/2019;11:17:54;END;113900A;0030/23459;105\n03/12/2019;12:35:30;START;115600A;0030/25443;42\n03/12/2019;12:37:44;END;115600A;0030/25443;43\n04/12/2019;09:15:11;START;149800A;0030/25442;0\n04/12/2019;09:17:26;END;149800A;0030/25442;1\n04/12/2019;09:18:18;START;149800A;0030/25442;1\n04/12/2019;09:19:33;END;149800A;0030/25442;2\n04/12/2019;09:25:01;START;149800A;0030/25442;2\n04/12/2019;09:26:16;END;149800A;0030/25442;3\n05/12/2019;11:34:21;START;137500A;0030/25667;2\n05/12/2019;11:35:18;END;137500A;0030/25667;3\n05/12/2019;11:38:45;START;137500A;0030/25667;2\n05/12/2019;11:39:42;END;137500A;0030/25667;3\n11/12/2019;13:43:05;START;180302bt;0100/37707;0\n11/12/2019;13:44:31;END;180302bt;0100/37707;1\n11/12/2019;13:46:07;START;180302bt;0100/37707;1\n11/12/2019;13:47:31;END;180302bt;0100/37707;2\n11/12/2019;13:47:56;START;180302bt;0100/37707;2","output":"str","x":260,"y":120,"wires":[["e25bb35b.d6c6c","8feb88b5.17b418"]]},{"id":"8feb88b5.17b418","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":250,"y":180,"wires":[]},{"id":"9cd5b277.d0c9f","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":410,"y":180,"wires":[]},{"id":"e14f2462.7363a8","type":"function","z":"a96cce5.282c93","name":"transform data","func":"/*** transform payload object into a nice new object ***/\n\n\n//A function to convert the DATE and TIME into a format the database likes\nfunction formatDT(datepart, timepart){\n //split the dd/mm/yyy string by / into a 3 part array\n let dateparts = datepart.split('/');\n //now put the date parts (in reverse order) \n //back into a string and add the time part \n let newDateStr = `${dateparts[2]}/${dateparts[1]}/${dateparts[0]} ${timepart}`\n return newDateStr;\n}\n\n//get \n//store original in a msg property (for checking in debug output)\nmsg.origPayload = msg.payload;\n\n//make a copy of the row data\nlet row = {...msg.payload};\n\n//example data 11/12/2019;13:47:31;END;180302bt;0100/37707;2\n//Giorno- - 11/12/2019\n//Ora - 13:47:31\n//Stato - END\n//Programma - 180302bt\n//Lotto - 0100/37707\n//Scheda - 1\n\nvar lottoParts = row.Lotto.split(\"/\");\nmsg.payload = {\n a: 9999, //not sure what this is about?\n DateTime: formatDT(row.Giorno, row.Ora), //Call a function to make the correct DATETIME string\n LottoRight5 : lottoParts[1], //characters right of the '/' \n LottoLeft4: lottoParts[0], //characters left of the '/' \n Stato: row.Stato, //add Stato to the payload\n Programma: row.Programma, //add Programma to the payload\n Scheda: row.Scheda //add Scheda to the payload\n}\n\n//Now we have an object with the clean values to write into DB\n//return the msg with nicely formatted data, ready to insert straight into the query {{mustach}}\nreturn msg;","outputs":1,"noerr":0,"x":580,"y":120,"wires":[["fcc4bfd8.b885c","39634da4.b9c5c2"]]},{"id":"fcc4bfd8.b885c","type":"debug","z":"a96cce5.282c93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":120,"wires":[]},{"id":"47f92084.1a0af","type":"MSSQL-CN","z":"","name":"Scambio_NAV","server":"srv-dc","encyption":true,"database":"Scambio_NAV"}]
