Need help inputting CSV data from OBD2 into influx from Chevy bolt

General
1

Hello, I'm trying to input data from a CSV file into influx. I think I have everything right except for getting the time stamp to influx from the CSV file instead of the current time. This is my flow minus the drop box node.

[{"id":"49df5c8b.889d84","type":"debug","z":"e43243.84581dc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":650,"y":140,"wires":[]},{"id":"2f981746.a36ca8","type":"csv","z":"e43243.84581dc","name":"","sep":",","hdrin":true,"hdrout":true,"multi":"one","ret":"\\n","temp":"","skip":"0","x":450,"y":140,"wires":[["f3ea7217.ab22a"]]},{"id":"1c937bc0.d86ae4","type":"influxdb out","z":"e43243.84581dc","influxdb":"b061b4be.968df8","name":"","measurement":"value","precision":"","retentionPolicy":"","x":650,"y":240,"wires":[]},{"id":"f3ea7217.ab22a","type":"change","z":"e43243.84581dc","name":"convert to time stamp","rules":[{"t":"set","p":"payload[\"GPS Time\"]","pt":"msg","to":"","tot":"date"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":320,"wires":[["49df5c8b.889d84","1c937bc0.d86ae4"]]},{"id":"b061b4be.968df8","type":"influxdb","z":"","hostname":"192.168.1.34","port":"8086","protocol":"http","database":"home_assistant","name":"torque","usetls":false,"tls":""}]

And this is the debug console.

GPS Time: 1577775035485
Device Time: "01-Dec-2019 09:01:18.800"
Longitude: -118.65542118996382
Latitude: 48.83717896975577
GPS Speed (Meters/second): 0
Horizontal Dilution of Precision: 6
Altitude: 511
Bearing: 0
G(x): -5.14513922
G(y): -8.14986229
G(z): 0.81642276
G(calibrated): 0.00499421
!Air Temp 0(°C): -5
!Batt Temp(°C): 13
!Battery Capacity Guess(kWh): "-"
!Battery Level Displayed(%): 72.94117737
!Charger HV Current(A): 14.69999981
!Charger HV Power(KW): 5.47574997
!Charger HV Voltage(V): 372.5
!Electronics Temp 1(°C): "-"
!Heater - Battery(W): 1256
!Heater - Cabin(W): 4699
!HV Current(A): -0.25
!HV Current HD(A): -5.250525
!Inst W/kph(W/kph): "-∞"
!MG Voltage(V): 372.98001099
!State Of Charge HD Raw(%): 73.19447327
!State of Charge Raw(%): 73.33333588
!Trans RPM(?): 0
*AC High Side Pressure(psi): 2.03052831
*Air Temp 1(°C): -5
*Air Temp 2(°C): "-"
*Batt Temp 2416(°C): 18
*Charger AC Power(KW): 5.17880011
*State of Charge Raw 2(?): 72.94117737

The problem may be that my timestamp is defined as GPS time instead of just time but I don't know how to change it to a format that influx will except. I apologize in advance as I'm quite the noob but would appreciate any help.

thanks
Dennis Mitchell

2

To provide a timestamp to influx you will have to use the influx batch node rather than the normal influx out node, which uses the current time for the measurements.
It looks as if your timestamp (GPS Time) is a javascript timestamp, which is in milliseconds. There is some confusion over what influx needs but I have found that it needs the timestamp in nanoseconds which means that you should multiply it by 1000. The information tab for the batch node describes how to give it the data and the timestamp. After putting some trial data in have a look at what is in the db using the command line influx client, or some other tool, to make sure that the samples are in the correct century.

3

Thanks Colin.Ill try that

4

Hi Colin

What is the best way to convert my data into a format that the influx batch node would accept. I've been trying to write a function node for the last couple hours but I'm not having much success.I'm just wondering if there is an easier way.

Thanks for your help

5

Show us the structure you are trying to generate.

6

I would like to store all the values with the GPS time stamp for the time

So for example

Battery Level
GPS Time: 1577775035485
!Battery Level Displayed(%): 72.94117737

Battery voltage
GPS Time: 1577775035485
!MG Voltage(V): 372.98001099

Ect.
All values are from a CSV logfile from the OBD2 reader. The way I have it right now I get a message object for each row in the CSV file with GPS Time being the first line outputted. I need to modify the message to a format that is correct for influx batch mode.

I need output similar to this picture with measurement being car, and the fields being Battery voltage and other values.

image
image344×742 15.5 KB

My current message is like this.

image
image326×695 21.2 KB

Thanks for your help , I apologize for being such a noob.

7

No need to apologise, this is not a trivial problem. Do you mean that each value is split across multiple lines, so timestamp and values are on different lines? If so you said that the time is first line, but in the example you posted it does seems that there are four lines in each set, with time being the second. Can you describe exactly what is the form of the data coming in please. The first thing that is needed is to collect together each set into one message so it can be manipulated.

8

I have just looked back at your previous post and am confused. It looks there as if you have already got all the data for a timestamp in one message. Please clarify what you already have.

9

image
image1584×758 61.4 KB

Data coming in is from this CSV file ,I'm using the CSV node.
I then thought I was changing the gps time but in actuality I was just changing it to whatever the current time was.
I was able to convert the date in device time in an excell spreadsheet with this formula " =(B835-DATE(1970,1,1))*86400" but thats not ideal and I would like to convert it in node red.

10

It's late here now so can't experiment myself. What happens if, in a function node, you do

node.warn("original timestamp is " + msg.payload["Device Time"])
let timestamp = new Date(msg.payload["Device Time"])
node.warn("timestamp msec is " + timestamp.getTime())

You should see the messages in the debug window.

1 Like
11

Thanks Colin

That worked awesome , I now have the correct time stamp.

I get this when I add the function.

image
image367×778 26.1 KB

12

Hi Colin

I exported my flow with 3 rows of my CSV file saved in the function node so you can see exactly what I'm working with.

Thanks so much for your help.

[{"id":"e43243.84581dc","type":"tab","label":"torque data from drop box","disabled":false,"info":""},{"id":"2f981746.a36ca8","type":"csv","z":"e43243.84581dc","name":"torque","sep":",","hdrin":true,"hdrout":true,"multi":"one","ret":"\\n","temp":"","skip":"0","x":150,"y":320,"wires":[["e6eeeb39.11fd98"]]},{"id":"a28a50ed.58338","type":"dropbox","z":"e43243.84581dc","dropbox":"","filename":"trackLog-2019-Dec-01_08-33-42.csv","name":"","x":290,"y":160,"wires":[[]]},{"id":"1c937bc0.d86ae4","type":"influxdb out","z":"e43243.84581dc","influxdb":"b061b4be.968df8","name":"","measurement":"value","precision":"","retentionPolicy":"","x":410,"y":600,"wires":[]},{"id":"9fb828f6.85e848","type":"inject","z":"e43243.84581dc","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":"","x":90,"y":240,"wires":[["1498adb7.ef9dd2"]]},{"id":"908fc0b6.040ad","type":"debug","z":"e43243.84581dc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":630,"y":340,"wires":[]},{"id":"3b3d676f.aecf38","type":"influxdb batch","z":"e43243.84581dc","influxdb":"b061b4be.968df8","precision":"","retentionPolicy":"","name":"","x":670,"y":260,"wires":[]},{"id":"e6eeeb39.11fd98","type":"function","z":"e43243.84581dc","name":"convert time to unix","func":"node.warn(\"original timestamp is \" + msg.payload[\"Device Time\"])\nlet timestamp = new Date(msg.payload[\"Device Time\"])\nnode.warn(\"timestamp msec is \" + timestamp.getTime())\nreturn msg;","outputs":1,"noerr":0,"x":310,"y":320,"wires":[["66ef979.d09dd68"]]},{"id":"91aa46cf.36f7e8","type":"function","z":"e43243.84581dc","name":"convert time to unix","func":"node.warn(\"original timestamp is \" + msg.payload[\"Device Time\"])\nlet timestamp = new Date(msg.payload[\"Device Time\"])\nnode.warn(\"timestamp msec is \" + timestamp.getTime())\nreturn msg;","outputs":1,"noerr":0,"x":290,"y":40,"wires":[[]]},{"id":"66ef979.d09dd68","type":"change","z":"e43243.84581dc","name":"","rules":[{"t":"delete","p":"payload[\"GPS Time\"]","pt":"msg"},{"t":"delete","p":"payload[\"Horizontal Dilution of Precision\"]","pt":"msg"},{"t":"delete","p":"payload[\"G(x)\"]","pt":"msg"},{"t":"delete","p":"payload[\"G(y)\"]","pt":"msg"},{"t":"delete","p":"payload[\"G(z)\"]","pt":"msg"},{"t":"delete","p":"payload[\"!Battery Capacity Guess(kWh)\"]","pt":"msg"},{"t":"delete","p":"payload[\"!Electronics Temp 1(°C)\"]","pt":"msg"},{"t":"delete","p":"payload[\"!Inst W/kph(W/kph)\"]","pt":"msg"},{"t":"delete","p":"payload[\"*Air Temp 2(°C)\"]","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":260,"wires":[["908fc0b6.040ad","3b3d676f.aecf38"]]},{"id":"1498adb7.ef9dd2","type":"template","z":"e43243.84581dc","name":"CSV data","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"GPS Time, Device Time, Longitude, Latitude,GPS Speed (Meters/second), Horizontal Dilution of Precision, Altitude, Bearing, G(x), G(y), G(z), G(calibrated), !Air Temp 0(°C),!Batt Temp(°C),!Battery Capacity Guess(kWh),!Battery Level Displayed(%),!Charger HV Current(A),!Charger HV Power(KW),!Charger HV Voltage(V),!Electronics Temp 1(°C),!Heater - Battery(W),!Heater - Cabin(W),!HV Current(A),!HV Current HD(A),!Inst W/kph(W/kph),!MG Voltage(V),!State Of Charge HD Raw(%),!State of Charge Raw(%),!Trans RPM(?),*AC High Side Pressure(psi),*Air Temp 1(°C),*Air Temp 2(°C),*Batt Temp 2416(°C),*Charger AC Power(KW),*State of Charge Raw 2(?)\nSun Dec 01 08:33:53 PST 2019,01-Dec-2019 08:33:52.800,-119.65548346750438,49.83708023093641,0.0,7.5,494.0,0.0,-2.17872357,5.07331324,7.84819317,0.01460817,-5,11,-,72.94117737,19.60000038,7.30100012,372.5,-,2064,7500,4.5,4.50045013,∞,373.07998657,73.2280426,73.33333588,0,1.88549054,-5.5,-,17,7.69560003,72.94117737\nSun Dec 01 08:33:54 PST 2019,01-Dec-2019 08:33:53.800,-119.65548346750438,49.83708023093641,0.0,7.5,494.0,0.0,-2.00394678,5.00388145,8.22168827,0.01631655,-5,11,-,72.94117737,19.60000038,7.30100012,372.5,-,2064,7500,4.5,4.50045013,∞,373.07998657,73.2280426,73.33333588,0,1.88549054,-5.5,-,17,7.69560003,72.94117737\nSun Dec 01 08:33:55 PST 2019,01-Dec-2019 08:33:54.800,-119.65548346750438,49.83708023093641,0.0,7.5,494.0,0.0,-1.75255561,4.94881487,8.76038361,0.06008631,-5,11,-,72.94117737,19.60000038,7.30100012,372.5,-,2064,7500,4.5,4.50045013,∞,373.07998657,73.2280426,73.33333588,0,1.88549054,-5.5,-,17,7.69560003,72.94117737\n","output":"str","x":240,"y":240,"wires":[["2f981746.a36ca8"]]},{"id":"b061b4be.968df8","type":"influxdb","z":"","hostname":"192.168.1.34","port":"8086","protocol":"http","database":"home_assistant","name":"torque","usetls":false,"tls":""}]
13

If you are trying to convert the Device time to milli seconds you left one step out of the function You need to store the converted value back into msg.payload so add
msg.payload["Device Time"] = timestamp.getTime()
at the end of the function node.

1 Like
14

Now you need to generate a message in the right format for the influx batch node. The timestamp needs to be in msg.timestamp, and assuming it needs to be in nanoseconds then that can be done with
let timestampNanos = (new Date(msg.payload["Device Time"])).getTime()*1000
Presumably you know the measurement name you want so to keep the code clean I would do
let myMeasurement = "my measurement name", putting in the right name obviously
Next we need an array of points containing the data values, so you could do something like

let points = []
let point = {}
point.measurement = myMeasurement
point.timestamp = timestampNanos
point.fields = [{"Speed": msg.payload["GPS Speed (Meters/second)"]}
points.push(point)
point = {}
point.measurement = myMeasurement
point.timestamp = timestampNanos
point.fields = [{"Altitude": msg.payload["Altitude"]}
points.push(point)

and so on.
Finally you need to put the array into the payload

msg.payload = points
return msg

In fact the code could be very much tidied up but see if you can get that going. Send the output to a debug node to check if it looks right before trying to send it to influx.

1 Like
15

Actually that isn't quite right, as they all have the same timestamp they can go in one element, so maybe

let points = []
let point = {}
point.measurement = myMeasurement
point.timestamp = timestampNanos
point.fields = []
point.fields.push({"Speed": msg.payload["GPS Speed (Meters/second)"]})
point.fields.push({"Altitude": msg.payload["Altitude"]})
// and so on
points.push(point)

msg.payload = points
return msg

That looks better.

1 Like
16

Thanks so much Colin

I'm so close but I'm now getting a bad request error on influx.I think its due to the time stamp but I'm not sure.

Error: A 400 Bad Request error occurred: {"error":"unable to parse 'bolt 0=[object Object],1=[object Object] 1575218032800000': invalid boolean"}

This is my function node.

image
image341×756 22.1 KB

let timestampNanos = (new Date(msg.payload["Device Time"])).getTime()*1000
let myMeasurement = "bolt"
let points = []
let point = {}
point.measurement = myMeasurement
point.timestamp = timestampNanos
point.fields = []
point.fields.push({"Speed": msg.payload["GPS Speed (Meters/second)"]})
point.fields.push({"Altitude": msg.payload["Altitude"]})
// and so on
points.push(point)

msg.payload = points
return msg;
17

Ah, sorry, my mistake, we have got fields as an array whereas I think it should just be an object. Instead of

point.fields = []
point.fields.push({"Speed": msg.payload["GPS Speed (Meters/second)"]})
point.fields.push({"Altitude": msg.payload["Altitude"]})

it should be

point.fields = {}
point.fields.Speed = msg.payload["GPS Speed (Meters/second)"]
point.fields.Altitude = msg.payload["Altitude"]
// and so on
18

Thanks, Colin
It is working without errors so data is now being put into influx. For some reason, I can't find data on date of Dec1.
If I comment out the time stamp data appears in the current time.
I'm going to play around with different values for this line and see if that helps.
let timestampNanos = (new Date(msg.payload["Device Time"])).getTime()*1000

image
image1680×1000 124 KB

19

.getTime() will give you milliseconds. If you really need nanoseconds then you need to multiply by 1000000. x1000 will only get you microseconds.

20

That's true. Give that a go.
If still not working use the influx command line client to inspect the data in the db and see what is there.