Community
OpenEnergyMonitor

Community

EmonCMS and CSV files

Tags: #<Tag:0x00007f6e0138f330> #<Tag:0x00007f6e0138f1f0>

I could potentially use EmonCMS as a useful viewer for my electric car logs, where a file is logged to my phone and Dropbox, containing all sorts of geeky stats. Of course this includes energy use, but also some battery temperatures.

Is there an easy way of getting the data from Dropbox into emonCMS? Is this a job for nodeRED or similar?

It would save ages in Excel :confused:

Thanks!

Node-red can read a csv file. And there is an emoncms node to send data to emoncms. I’ve tried it with <1000 lines of data and it seems to work OK. I only spot checked the imported data.

EDIT: below is a simple node-red diagram to move data from a csv file into emoncms. One part I haven’t figured out is how fast data can be pushed into emoncms. I’ve set the limit to 2 messages per second.
 

 

[
    {
        "id": "8f912d5d.8340e8",
        "type": "function",
        "z": "4a22cedb.45235",
        "name": "",
        "func": "var kWh = msg.payload.kWh;\nif ( kWh <= 0 ) { return null; }\nmsg.nodegroup = \"16\";\nmsg.time = '' + msg.payload.epoch;  //  must be 'string input' not 'number input'\nmsg.payload = \"energy_kWh:\" + kWh;\n\nreturn msg;\n",
        "outputs": "1",
        "noerr": 0,
        "x": 170,
        "y": 200,
        "wires": [
            [
                "248c3d88.1a1692"
            ]
        ]
    },
    {
        "id": "56a2cdb0.50361c",
        "type": "csv",
        "z": "4a22cedb.45235",
        "name": "csv",
        "sep": ",",
        "hdrin": "",
        "hdrout": "",
        "multi": "one",
        "ret": "\\n",
        "temp": "epoch,kWh",
        "x": 410,
        "y": 100,
        "wires": [
            [
                "8f912d5d.8340e8"
            ]
        ]
    },
    {
        "id": "b7d2c1ea.b29f3",
        "type": "file in",
        "z": "4a22cedb.45235",
        "name": "get data",
        "filename": "yourCSVfile.csv",
        "format": "utf8",
        "x": 260,
        "y": 100,
        "wires": [
            [
                "56a2cdb0.50361c"
            ]
        ]
    },
    {
        "id": "f924d020.a27e38",
        "type": "inject",
        "z": "4a22cedb.45235",
        "name": "go",
        "topic": "",
        "payload": "",
        "payloadType": "str",
        "repeat": "",
        "crontab": "",
        "once": false,
        "x": 110,
        "y": 100,
        "wires": [
            [
                "b7d2c1ea.b29f3"
            ]
        ]
    },
    {
        "id": "91a876aa.c583e8",
        "type": "emoncms",
        "z": "4a22cedb.45235",
        "name": "emoncms",
        "emonServer": "6f9482ce.906b7c",
        "nodegroup": "",
        "x": 500,
        "y": 200,
        "wires": []
    },
    {
        "id": "2e461ac8.9f5ea6",
        "type": "comment",
        "z": "4a22cedb.45235",
        "name": "CSV file to emonCMS",
        "info": "My CVS input file is:\n- one column of epoch (in seconds)\n- and one column of kWh.",
        "x": 120,
        "y": 40,
        "wires": []
    },
    {
        "id": "248c3d88.1a1692",
        "type": "delay",
        "z": "4a22cedb.45235",
        "name": "",
        "pauseType": "rate",
        "timeout": "5",
        "timeoutUnits": "seconds",
        "rate": "2",
        "nbRateUnits": "1",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "5",
        "randomUnits": "seconds",
        "drop": false,
        "x": 330,
        "y": 200,
        "wires": [
            [
                "91a876aa.c583e8"
            ]
        ]
    },
    {
        "id": "6f9482ce.906b7c",
        "type": "emoncms-server",
        "z": "",
        "server": "http://127.0.0.1/emoncms",
        "name": "emonPi"
    }
]
1 Like

Is there any further helpful info on this topic? I’m trying to do exactly the same thing, feed data from a linux host on the same network into emoncms on an emonPi.

I’ve got data flowing via tcp to nodeRed on the Pi, but can’t find any info on how the emoncms output object in nodeRED needs to be configured.

Is there (I suspect yes) configuration changes needed on the emoncms side to recognise the incoming data? Is it going to see it as an MQTT message, or is that the alternative way to get data in?

Hi Michael,

You can use a Node-Red node called emoncms and it is configured like this:

https://blog.openenergymonitor.org/2015/11/node-red-emoncms-node/

Just pick a unused Node number. I happened to pick 12. No emonPi configuration is needed except go to the Inputs web page, look for Node 12 and then add the processes like “Log to Feed”.

The difference is the emoncms server would be your emonPi (and not emoncms.org)

Does this help?

Thanks Jon, yes, I didn’t know if the emoncms or pi needed any config changes to receive he new feed. I managed to get nodeRED sorted out to receive the data I wanted and send it to the debug console, or even email.

What I’m going to have to figure out now is how to ad more programmed function features to the RED flow, as I need to pull out specific fields in a CSV string, and put them back together as a pair of values to send to emoncms.

All looks good and fun so far. Can’t see why this is not going to work.

Michael - FYI, my big issue was throughput. The emoncms node could only take a few posts per minute. Sorry I don’t remember the high-end.

If your data is not timestamp’d or time sensitive, MQTT might work better. (no emonPi config needed)

Hi Jon,

The data I’m thinking of logging is just a speedtest result (using speedtest-cli) and only once an hour. To monitor peak congestion of long term degradation of ISP performance :slight_smile: and have it nicely graphed etc for quick visual analysis.

The nodeRED route looks easy as I can send the data with netcat from a linux PC to the emonPi, which already has nodeRED built in.

I suspect if I went the MQTT route I’d have to have additional software on the linux host, or I guess I could just pull the text TO the pi and have a script running there that pulls the numbers out of the log and inject it into emonCMS via MQTT locally on the Pi?

Have not found the right docs yet showing the inner nuts’n’bolts of how all this hangs together in the emonPi.

New at this but I’ve just managed to post CSV data into emonCMS using a simple python script and the HTTP based API.
Presumably it’s preferable to use MQTT, so this is good learning…

Still struggling to get data into emoncms (on my emonPi) using nodeRED with either MQTT or the emoncms output flows.

Injecting test data using the inject input flow.

Can’t find any references as to what the correct settings / data types etc are for the input or output nodes.

Using the inject node with a string payload of 123 and a Topic of emon/test/test2 connected to a MQTT node configured to talk to localhost:1883 I can get the data to appear in the Inputs of the emoncms page, BUT, it seems to be ovewritten by any new data even when the Topic is different…

e.g if I send data with a Topic of emon/test/test3 , then that is updated in the Inputs of emoncms, but the test2 like ((with a Key of test2) vanishes.

I can’t get the emoncms output node to work at all, as I can’t find any documentation on what the format of the data is meant to be to feed into it.

Is there any good examples online that I’ve missed, that show the data format needed to feed into the emoncms node?

Possibly related error…

When I try to add the one Input that I have managed to create to a Feed, I get an error “ERROR: Could not save processlist. undefined”.

I suspect this is related to some missing data in the injected MQTT message?

OK, so I made the Pi angry, and had to reboot… Then my original test with two different inject nodes (in nodeRed) feeding the MQTT output node resulted in TWO Inputs in emoncms, that can be updated independently… So I really don’t know what was going on before, this is the behavior that I was expecting.

And… I can add the input to a Feed without the error mentioned above…

So, onwards…

I did the same thing with speedtest-cli! If you run speedtest-cli on the Pi keep in mind the Pi ethernet port is 100 Mbps (theoretical max speed). There was someone that sent a shaming-type tweet every time his ISP degraded too low.

 

For the Inputs page, the data is always overwritten when new data appears. If you want to save it or process it, you must click on the spanner/wrench and add what is needed (e.g., Log to Feed). If a new topic overwrites older topics, then do an emonPi reboot. It isn’t you! (I have the same issue occasionally).

 

The info I found was on the right Info tab.

Here is an example of my output to emoncms:
msg.payload = temp:75,humidity:84,epoch:1469746320,tempC2F:75.2

 

emonPi reboot helps with this also. There are a few other posts with this error.

Indeed, but what I was seeing was the whole FEED being replaced, not just the data. However, that behaviour stopped after a Pi reboot, so something must have been amiss.

Yes, I run speedtest-cli from a propper PC, as I knew the Pi wold not be able to give a proper reading.

Once I figure out all the little questions I still have, I’ll post the full setup I used here for others to enjoy.

The info tab is a wealth of information for sure.

Are you talking about the emoncms output node here? Is that posting to a local emoncms or emoncms.org (or does it not matter)?

I got the data going to my local emonPi last night using MQTT and just started trying to get the emoncms output node to post to emoncms.org.

Yes, this is the message I pass to the emoncms out node. And the message is good for local emoncms and emoncms.org.

Glad you got MQTT working also!

OK, so here is what I ended up with. It does everything I wanted. It could probably have been done neater but it was a learning exercise.

What I was trying to do is log speed test results each hour for my two uplinks / ISP’s, and for good measure, each 15 minutes log the ping average to google DNS via each uplink.

The data was generated on two different linux hosts so that I could setup my router to direct the trafic from each over the desired uplink path.

They generate the data in cron with something like:

59 * * * * /usr/local/bin/speedtest-cli --server 5074 --csv >> /var/log/speedtest.log
1 * * * * /usr/bin/tail -1 /var/log/speedtest.log | /bin/nc -q1 172.16.0.13 2001
*/15 * * * * /bin/ping -n -q -c 10 8.8.8.8 | tail -1 | cut -d / -f 5 | /bin/nc -q1 172.16.0.13 2003

and the data is sent to the emonPi (172.16.0.13) with netcat.

Initially I could not get the emoncms node-red output node to send to my local emonPi, but I did get the MQTT output node working, so I neded up using that for local data logging.

Given that MQTT was not going to be an option to log to emoncms.org, I tinkered some more and with help on here (thanks Jon and others) and other doco I got the emoncms node-red output node to correctly post the data to emoncms.org.

Turned out to be an interesting little learning exercise. Have not done javascript before. I think that’s what I was writing in the function nodes :slight_smile:

If anyone tries to do something similar in future and wants some details of the node-red node configurations I used, post a msg here and I’ll be happy to share more details.

1 Like