Hi @klaj thank you very much for your integration. It allows me to import all my old electricity usage.
The only issue I’m having is that I have to process the whole history every time I want to import anything new, to calculate the correct sum.
Would it be possible for the integration to autocalculate the sum based on the existing entries already in HASS?
For example:
My power company gives me an CSV for each day with the kWh used every hour. (so that would be the state). If I want to import the last day, I need to calculate the sum based on what I already imported. If I set the sum to 0, I get a negative usage.
I do not understand completely. Fur further imports, you should not need the whole history, but only the newest not yet imported value. Lets say, you have imported until Mar, 1st, 23:00. And the sum is 1.000 at this timestamp.
Then you just have to add the hourly kWh to 1.000.
Does that make sense?
Of course I could do that also in the integration, but the main usecase I have in mind is to import old values before collecting data in HA, and then its only a one-time-action. Lets see if/when I have time. Maybe somebody else wants to do this?
I’ll try and find up with a easier use case for this scenario.
Let’s say that every hour I want to import the amount of electricity used last hour and add it to a “Total Increasing” sensor.
Every time I import it, I need to know the last sum and add the new value/state to it. As the energy dashboard always shows me usage equal difference between this and the previous sum.
I can save the previous sum in a script and always use it to calculate the new one, but I thought it might be easier for the integration to get the previous sum from the hass database and calculate the new sum based on the new state.
Glad I found this integration like some others I have switched some integrations, in my case I switched from Fusion solar to Huawei solar to report the inverter generation. This happened around new years. I kept old Fusion solar entity in the Energy dashboard config to be able to “rewind” to previous year and analyze istory. But one day I made a mistake and deleted the Fusion solar integration. which in turn removed the old entity… and to make the matters worse I noticed this after several days which meant I would create a gap in all other statistics if I just restored… So I just cursed a bit… and went on.
But when I stumbled on this integration I thought I would try to add my old entity history to my new entity history: restored a January backup, updated HA to 2024.3.3 (to get the history download button), saved last years history of the old entity to a .csv file
Reformatted the data in the file to correspond this integrations requirements. it took me several trials until I got it right but eventually I have the statistics restored!
Thanks for creating this integration. Two questions:
What date time format is being used for import? I’m not aware of a standard that formats the date with periods. Is there some easy option to make pandas datetime64 output that format?
What are people using for exporting long term statistics in order to fix them up and re-import?
The time format is “26.01.2024 00:00” (TT.MM.JJJJ hh:mm), as you probably found in the examples (used in some areas of europe, at least in mine ). As of today, you have to convert it on your own with whatever tool. I thought already about making this more flexible, but did not find the time yet. Feel free add this feature on your own.
Since a few versions, you can export from the standard history graph to csv. I was also already thinking about adding this option to the integration.
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
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|
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)
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.
Can you please try if it works without the space between …delivered and the ‘|’?
If the content of the file above is not correct (I saw in your screenshot that you are using a tab as delimiter, above is a pipe), and you do not have a wrong “space”, please attach the real file (the first two lines are enough, if the error occurs with the 2 rows only as well).
Thanks for the catch. There was indeed a space character after “delivered”.
When I removed that, the import went OK. Thanks again for the great integration!
I still hope it can be made more userfriendly. E.g. if Homassistant gets a facility to recalculate the sum value, so we don’t have to struggle with that.
Also, is the unit column really needed? Or can you get that from the entity and fill it in during the import?
Anyways, still very happy and importing some more data from domoticz.
Fine that it is working now . The sum column is on my list already, added “take the unit from the existing entity”. Lets see if/when I have time … in the meantime, here is the list of possible improvements I have collected so far:
Fix bug with seconds in timestamp (only support minutes, and add tests for some “crazy” timestamp)
Calculate sum automatically, based on the oldest existing value
Allow different dateTime-formats
Take the unit from existing entity
Export data
@all: Feel free to implement, I’d be happy to review and merge
Yes, I finished migrating my data. Your integration was very nicely timed for me. And appart from the typo with the space, on my side, it went smoothly. The integration worked flawlessly.
Feedback is welcome, and I will try to include suggested improvements. Although this type of thing is something that you use only once, so I probably never use it myself again. But it can be a starting point for someone.
@Klaus, thanks again for your work. From my point, to add to the wish list is of a utility to upload your entire domoticz db, select the domoticz device, select the Home Assistant Entity, and some other data and migrate fully automatic. But I know this is a very elaborate request. So take it with the apppropriate amount of grains of salt.
like some others I have switched some integrations, in my case I switched from Fusion solar to Huawei solar to report the inverter generation. This happened around new years. I kept old Fusion solar entity in the Energy dashboard config to be able to “rewind” to previous year and analyze istory. But one day I made a mistake and deleted the Fusion solar integration. which in turn removed the old entity… 
and to make the matters worse I noticed this after several days which meant I would create a gap in all other statistics if I just restored… So I just cursed a bit… and went on.
