Reading from CSV file insert values into influx-DB

General
1

Hi
i am very much new to Node-Red concept . we have emerson PLC -CPL410 which supports node-red and Influx-db. I try to read PLC Diagnostic data CSV file continuously and insert a data into influx-db one by one.

i could able to display in node.warn msg individual data but not quite sure how to insert individual data into field like below

image
image1444Ă—239 21.5 KB 

[{"id":"73e5e95c.050fe8","type":"csv","z":"711169bd.c68438","name":"","sep":",","hdrin":true,"hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":434,"y":312,"wires":[["27bdaada.685e9e","7f507812.bf37d8"]]},{"id":"9a9ed6a0.88d5b8","type":"inject","z":"711169bd.c68438","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":154,"y":312,"wires":[["f1b006d3.ab7888"]]},{"id":"f1b006d3.ab7888","type":"function","z":"711169bd.c68438","name":"","func":"data = `date,temperature\n202008110900,20\n202008111000,26\n202008111100,25\n202008111200,27\n202008111300,21\n`\nreturn {payload:data};","outputs":1,"noerr":0,"initialize":"","finalize":"","x":300,"y":312,"wires":[["73e5e95c.050fe8"]]},{"id":"9916d5e5.46f21","type":"debug","z":"711169bd.c68438","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":838,"y":312,"wires":[]},{"id":"27bdaada.685e9e","type":"switch","z":"711169bd.c68438","name":"Temperature > 25 ?","property":"payload.temperature","propertyType":"msg","rules":[{"t":"gt","v":"25","vt":"num"}],"checkall":"true","repair":false,"outputs":1,"x":618,"y":312,"wires":[["9916d5e5.46f21"]]},{"id":"7f507812.bf37d8","type":"debug","z":"711169bd.c68438","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":578,"y":240,"wires":[]}]
2

Welcome to the forum @Ferdi84

Show us what the data coming out of the csv node looks like in a debug node.

3

Can you also provide details about your existing InfluxDB (version, if using InfluxQL or Flux, etc.)?

4

image
image534Ă—916 58.3 KB

thanks for the reply . csv output in debug mode attached as image copy

5

Hi
influxdb Version 1.8.6, InlfuxQL

6

Sorry, that is unreadable as a screenshot. In the debug window click on the top level object and two little buttons should appear at the right hand side. Hover over the right hand button and it should show Copy Value. Do that then paste it here, using the </> button above the forum text entry window when pasting it.

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item and how to copy data from there.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

7 
{"PlcTime,CatalogNum,SerialNum,FwVer,BuildId,FreeBytesInPart,FreeBlksInPart,MaxFreeBlkSize,AllocBytesInPart,AllocBlksInPart,SystemHeapSize,SysHeapTotalBlks,SharedHeapAvail,SharedHeapLargeAlloc,SharedHeapUse,SrtpConn,MbusConn,LAN1MbufFree,LAN1MbufUse,LAN2MbufFree,LAN2MbufUse,LAN3MbufFree,LAN3MbufUse,LAN4MbufFree,LAN4MbufUse,EthP1Off,EthP2Off,EthP3Off,pktRate[LAN1],max_pkt_rate[LAN1],timestamp[LAN1],pktRate[LAN2],max_pkt_rate[LAN2],timestamp[LAN2],pktRate[LAN3],max_pkt_rate[LAN3],timestamp[LAN3],":"22-JUL-2022 14:30:57.0,IC695CPL410-AAAA,UFAR00A,10.15,EGX2,14432880,175,5845056,253994064,26222,268426944,26397,2091008,1048576,6144,0,0,1022,37987656,1023,261,1023,257,1200,585,0,0,0,2,2782,16-JUL-2022 06:24:26.0,0,0,01-JAN-1970 00:00:00.0,0,0,01-JAN-1970 00:00:00.0"}

sorry for the trouble

8

What does it look like going into the csv node?

9

I don't see how that can be coming out of the csv node in the example flow you posted.

10

image
image1908Ă—264 26.3 KB

This is sample CSV file i m reading with column header and values ( Columns are till AK)

11

So the flow you posted was irrelevant apparently.

Cut the file down to a couple of lines and show us what is going into the csv node, using a debug node as before.

Also show us how you have configured the csv node.

12

image
image640Ă—726 26.2 KB

above is CSV node setting

read node below

image
image435Ă—876 11.6 KB

input to CSV node through Read node
image

CSV node output first object

{"PlcTime,CatalogNum,SerialNum,FwVer,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,":""}

CSV Node output 2nd object 
{"PlcTime,CatalogNum,SerialNum,FwVer,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,":"7/22/2022 14:30,IC695CPL410-AAAA,UFAR00A,10.15,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"}
13 
[{"id":"821ed147.a04a9","type":"tab","label":"Sensors","disabled":false,"info":""},{"id":"3c451f09.b059d","type":"function","z":"821ed147.a04a9","name":"Add Daily Readings","func":"var json=[];\nfor(var w in msg.payload){\nvar to=msg.payload[w]\nvar tosplit =to.split(\",\");\nfor(var i=0;i<tosplit.length;i++)\n{node.warn(tosplit[i])}}\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":790,"y":100,"wires":[["7ece2c18.fd9644"]]},{"id":"2cb9ad73.620162","type":"csv","z":"821ed147.a04a9","name":"","sep":"\\t","hdrin":true,"hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":450,"y":80,"wires":[["993d9476.ae6ff8"]]},{"id":"5860de91.e0685","type":"file in","z":"821ed147.a04a9","name":"Read","filename":"/data/log2.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":290,"y":80,"wires":[["2cb9ad73.620162"]]},{"id":"fa7ecd1d.548a3","type":"inject","z":"821ed147.a04a9","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":80,"wires":[["5860de91.e0685"]]},{"id":"993d9476.ae6ff8","type":"json","z":"821ed147.a04a9","name":"","property":"payload","action":"obj","pretty":false,"x":590,"y":80,"wires":[["3c451f09.b059d"]]},{"id":"7ece2c18.fd9644","type":"influxdb out","z":"821ed147.a04a9","influxdb":"10991be8.70c254","name":"Local DB Server","measurement":"LocalDB1","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":1100,"y":100,"wires":[]},{"id":"10991be8.70c254","type":"influxdb","hostname":"172.18.0.9","port":"8086","protocol":"http","database":"LocalDB1","name":"LocalDBserver2","usetls":false,"tls":"ced18875.cf10b8","influxdbVersion":"1.x","url":"http://localhost:8086","rejectUnauthorized":true},{"id":"ced18875.cf10b8","type":"tls-config","name":"","cert":"","key":"","ca":"","certname":"","keyname":"","caname":"","servername":"","verifyservercert":true}]

sorry mistakenly i posted another flow , this is flow i am trying to work out

15

I want to see the input to the csv node, as you see it in a debug node. Reduce the file down to two data lines (plus the header line) to make it easier to follow.

1 Like
16

Looks like your csv file is using comma as separator, but you have set csv node to tab.
Also do not check empty strings and null values.

1 Like
17

Yes, I was hoping to get the OP to work that out for himself.

2 Likes
18

Life's under no obligation to give us what we expect/hope/wish.

1 Like
19

input to CSV node

PlcTime,CatalogNum,SerialNum,FwVer
7/22/2022 14:30,IC695CPL410-AAAA,UFAR00A,10.15
7/22/2022 14:30,IC695CPL410-AAAA,UFAR00A,10.15
7/22/2022 14:30,IC695CPL410-AAAA,UFAR00A,10.15
7/22/2022 14:31,IC695CPL410-AAAA,UFAR00A,10.15

output from CSV node ( thanks for the Tip)

{"PlcTime":"7/22/2022 14:30","CatalogNum":"IC695CPL410-AAAA","SerialNum":"UFAR00A","FwVer":10.15}
20

OK, that is much better. A couple of questions

  1. What time zone is the timestamp? Hopefully it is in UTC, otherwise it will have to be converted to UTC as that is what Influxdb requires.

  2. Which of those values do you want as tags and which as fields in the database.

  3. Have you looked at the help text for the Influx Out to see what format you need the data?

21

Hey thanks for the reply

  1. TimeZone -IST ( Indian Standard Time) , UTC+5.30
  2. Serial No- Tag and rest of them are fields
  3. i was gone through the document in Influx-DB out

msg.payload = 
  {
    PlcTime: global.get("PLctime"),
    CatalogNum: global.get("CatalogNum"),
    SerialNum: global.get("SerialNum"),
    FwVer: global.get("FwVer")
}
return msg