Import Data fails

[Stephan-4711]

Moin,

I'm trying to import the before stored on a diffrent sensor inside homeassistant.
But in your documentation there is no home assistant as data source.

I exported the HA data from my sensors and they look like this

entity_id,state,last_changed
sensor.strommessung_uv_phase_c_total_active_energy,164.53584,2023-11-10T07:00:00.000Z
sensor.strommessung_uv_phase_c_total_active_energy,164.60006,2023-11-10T08:00:00.000Z
sensor.strommessung_uv_phase_c_total_active_energy,164.66443,2023-11-10T09:00:00.000Z

entity_id,state,last_changed
sensor.strommessung_uv_total_active_returned_energy,8.74454,2023-09-15T07:00:00.000Z
sensor.strommessung_uv_total_active_returned_energy,9.03297,2023-09-15T08:00:00.000Z
sensor.strommessung_uv_total_active_returned_energy,9.42448,2023-09-15T09:00:00.000Z

When I try to import this, I get the following error:
Ausführung wurde mit Fehlern beendet.
Ergebnis: NOT NULL constraint failed: STATS_NEW.sensor_id
In Zeile 98:
/* Insert the high resolution records and apply the correction
Grouping by field1 is done to remove any duplicates
*/
INSERT INTO STATS_NEW (sensor_id, ts, value)
SELECT
(SELECT sensor_id FROM SENSORS WHERE name = 'sensor_id_elec_feed_in_tariff_1' LIMIT 1),
round(field1, 0),
round(field2 / (SELECT correction FROM SENSORS WHERE name = 'sensor_id_elec_feed_in_tariff_1' LIMIT 1), 3)
FROM elec_feed_in_tariff_1_high_resolution
GROUP BY field1;

Can you give me an advice what I'm doing wrong?

[patrickvorgers]

Interesting use case :-)

Can you provide me with the following information:

• Which database(script) are you using? SQLite/MariaDB
• What is the configuration of your sensors? Below the default configuration.

/*                          name                                sensor_id correction cutoff_new_meter cutoff_invalid_value */
INSERT INTO SENSORS VALUES ('sensor_id_gas',                    6,        1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_feed_in_tariff_1',  7,        1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_feed_in_tariff_2',  8,        1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_feed_out_tariff_1', 9,        1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_feed_out_tariff_2', 10,       1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_solar',             352,      1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_water',                  653,      1000.0,    25.0,            1000.0); /* Change */

• Which sourcefiles are you trying to import?

elec_feed_in_tariff_1_high_resolution
elec_feed_in_tariff_2_high_resolution
elec_feed_out_tariff_1_high_resolution
elec_feed_out_tariff_2_high_resolution
etc.

• Can you provide some sample import files? Looking at your provided information it seems that your input data is incorrect. The source files should be in the following format:

Each row contains: Epoch Unix Timestamp, sensor value
Example:
- 1540634400, 8120605
- 1540638000, 8120808
- 1540641600, 8120993
- 1540645200, 8121012

[Stephan-4711]

It's SQLite
I used the
sensor_id_elec_feed_in_tariff_1 sensor Id I changed to 738
sensor_id_elec_solar sensor Id I changed to 739
sensors
Or do these sensors need to exist before I import them?

and I exported the data from HA by using the history of HA.
I selected my entity for Solar to energy provider exported this
I selected my consumption entity and exported this to another file,

both just give me this rows
entity_id,state,last_changed

so I guess if you would like to create a import HA to HA feature to your script just these where needed?

For now I used another way.
I used your manual for exporting the Database, using the DB explorer
then I found this manual
https://community.home-assistant.io/t/migrate-energy-statistics-from-one-entity-to-another/405863/20

I created to template helpers, then
I took the last comment and just adopted the IDs

For future use or other people adopting your script to save data from HA history or even from the Database would be amazing. My google searches showed me, many people want to store their history, when they change their hardware for messurements.

[patrickvorgers]

The new sensor should exist in the statistics_meta table because otherwise Home Assistant would not know about the new sensor. It is not needed that the new sensor already has existing statistical data.

When you create an export you have to make sure that the file uses the correct format:
Epoch Unix Timestamp, sensor value

Example:

• 1540634400, 8120605
• 1540638000, 8120808
• 1540641600, 8120993
• 1540645200, 8121012

It cannot be in the format: entity_id, state, last_changed
The entityid is determined based on the filename and the configuration part of the SQL file.

I will have a look whether I can provide simple (SQL) script which would export sensor data of Home Assistant into a CSV file with the correct format. That file can than be imported again for the new sensor.

[Stephan-4711]

That would be great an many people would get help.
Then we just need a developer who can integrate that directly into home assistant 😅

[patrickvorgers]

@Stephan-4711 I added Home Assistant as a datasource. It consists out of simple SQL script to get the data and a how-to on how to export the data in the right format so that it can be imported by the "import script".

[Stephan-4711]

That sounds great. At the moment I. Don't have a case.
But I think I'll make a test the next days. Tank you very much.

Now the become naughty: next step as graphical addon into homeassistant 🙈

[AJediIAm]

I honestly think this would be a great addition to HA core. My home assistant is a bit of a mess but I don't want to start with a clean slate because of the historical energy data.
I'm sure many other users would like to have an option to start over after a year of messing around.

It could also provide a nice way to correct mistakes in the data. It's much easier to do that in excel than in home assistant.

[Stephan-4711]

Excel ftw 😅