Custom Integration to import long term statistics from a file like csv or tsv

So overall I was successful to import using a tsv file with this format. My failure: I used the wrong sensor name.

statistic_id unit start sum
sensor.stromzaehler_verbrauch kWh 11.12.2023 10:00 2540.0

However there is still one behaviour that I could not solve. When I import data the very next hour starts with a value of minus counter:

Historic data is accurate. Data following that hour as well but I do not know how to solve that, yet.

in one of my trials I had similar issue. it seems it is related to ‘sum’ column

@klaj mentions this in his readme

Initially I just left 0 in all rows in column ‘sum’, then tried to copy the ‘state’ value to the ‘sum’. in both cases I had similar spikes in the junction of imported data and newer data.

I then did a calculation:

  • first I looked up in the database what is the first original 'state; value in the HA ‘statistics’ table. (used phpmyadmin (I am using MariaDB))
  • I then used a google sheets formula to substract the state value from that first state. take a look at the pic.
  • it resulted in an increasing negative ‘sum’

by the way I also used googlesheets to format the date from the one HA exported (used ‘Replace-all’ to cut off the seconds and unnecessary characters).

oh and one more thing. the statistic_ID is different from the one that I exported from HA. in other words I truly merged two different entities history :grinning:

That fixed it.

Next job will be an export of all the data to InfluxDB. Current data is written by the sensor. :slight_smile:

Hello Klaus, all,

I finally got to try your integration. It took me a while to setup a second home assistant, with a copy of my database to safely mess with the measurements.
Thanks for this integration!

I got old data from my domoticz, going back to 2017. Especially the energy readings would be nice to transfer, but also some temperature measurements.

I set up a libreoffice calc sheet to transfer data to the right format, and for the electricity measurements it worked. But with the gas consumption measurement, which for all I can see, I did the same way the integration gives the following error:
(I couldn’t copy the line, so I made a screenshot.

From what I gather , it complains about the statistic_id being invalid. But I never use a colon.

What I am a bit worried and unsure about is the unit. It is cubic meter, and I copied the string from home assistant, but the superscript 3 can cause some trouble.
These are the first 2 lines from the file (badly copied, admitted)
|statistic_id|unit|start|state|sum|
|sensor.dsmr_consumption_gas_delivered |m³ |03.12.2017 00:00|31.782|-14420.305|

Any idea what goes wrong here?

Regards,

Bert

does your sensor.dsmr_consumption_gas_delivered entity exist in your system…that might be your issue as this Integration will only import to existing entities, it doesn’t create them.
(if you don’t have the sensor you want to import to already in your system just create it using a template that never updates and you can import against that)

Hi Gav_in,

Barring a typo, yes, the entity exists. It is part of the Digital Smart Meter Reader(dsmr) integration. That is the strange thing. And the electricity history input went without a hitch, so I thought I had it figured out.

Again, I’m a bit nervous about the m3 going right. typing in kWh is easy, but with the superscript “3” in m3, you never know between file encodings. But then the error is indicating something else of course.
In short, I’m at a loss.

Bert