Did you read the paragraph “whereas counters are more complex” from the readme? Could be that “You have to align the imported values with the first current value in your database” is violated.
1 Like
hmm i seem to be having issues importing.
i’m trying to import this file, but i’m getting an error in logs, but “unknown error” on the gui.
i’m using container home assistant 2024.12.5
and these are the logs
Logger: homeassistant.helpers.script.websocket_api_script
Source: helpers/script.py:526
First occurred: 2:17:21 PM (3 occurrences)
Last logged: 2:38:55 PM
websocket_api script: Error executing script. Unexpected error for call_service at pos 1: expected string or bytes-like object, got 'NoneType'
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/script.py", line 526, in _async_step
await getattr(self, handler)()
File "/usr/src/homeassistant/homeassistant/helpers/script.py", line 764, in _async_call_service_step
response_data = await self._async_run_long_action(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
...<9 lines>...
)
^
File "/usr/src/homeassistant/homeassistant/helpers/script.py", line 727, in _async_run_long_action
return await long_task
^^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/core.py", line 2802, in async_call
response_data = await coro
^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/core.py", line 2852, in _execute_service
return await self._hass.async_add_executor_job(target, service_call)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.13/concurrent/futures/thread.py", line 58, in run
result = self.fn(*self.args, **self.kwargs)
File "/config/custom_components/import_statistics/__init__.py", line 42, in handle_import_from_file
stats = _prepare_data_to_import(file_path, call)
File "/config/custom_components/import_statistics/__init__.py", line 83, in _prepare_data_to_import
stats = _handle_dataframe(df, timezone_identifier)
File "/config/custom_components/import_statistics/__init__.py", line 151, in _handle_dataframe
source = _get_source(statistic_id)
File "/config/custom_components/import_statistics/__init__.py", line 182, in _get_source
if valid_entity_id(statistic_id):
~~~~~~~~~~~~~~~^^^^^^^^^^^^^^
File "/usr/src/homeassistant/homeassistant/core.py", line 240, in valid_entity_id
return VALID_ENTITY_ID.match(entity_id) is not None
~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^
TypeError: expected string or bytes-like object, got 'NoneType'
Hm. Can you please try to remove the first two columns (although I do not think that should be a problem). Then there should be a state column (values can be equal to sum for now, but I am also not sure if sum only should work as well.
If that does not help, please create a bug on the repo, including the file you try to import (not only a screenshot), and a screenshot of the HA GUI where you call the function.
1 Like
Could be that “You have to align the imported values with the first current value in your database” is violated.
Ok I needed to wrap my head around the alignment you were talking about, but I think I got it now:
Here is what I did:
- (while HA was stopped) delete all sensors data from the database and
core.restore_state - generate (and import) a csv with values with a calculated
sumcolumn:
statistic_id|unit|start|state|sum
sensor.energy_mains_total_f1|kWh|06.02.2024 00:00|83.48|0.0
sensor.energy_mains_total_f1|kWh|29.02.2024 00:00|83.5|0.02
sensor.energy_mains_total_f1|kWh|31.03.2024 00:00|92.13|8.65
sensor.energy_mains_total_f1|kWh|30.04.2024 00:00|122.63|39.15
The main issue was that when I started measuring, the meter was already greater than zero (previous owner energy consumption), so I could not use a sum value equal to the state value.
Thanks!!
It would be nice to have options in the import process to fill gaps of data like simple “lerp” or “repeat-previous”. From what I understand I need to do that myself in an external tool to make sure the tsv is spaced hourly to the current timestamp.
How would I go about creating a sensor for the data that is not updated / overwritten by homeassistant template sensor ?
Yeah, both is true. No plans to do that yet, I’ll put it on my list, but it will take some time until I can work on this integration again.
Not sure what you mean. You can use an external sensor (‘:’ instead of ‘.’), if you want to have statistical data only.
Hi Klaus,
Just switched to HomeAssistant and well impressed, with your custom integration!
I also have a similar issue: imported 2024 gas usage data. All seems to be fine except a few days in this year: 01/01/2025, 02/01/2025 & 07/01/2025.
The history seems to be fine; the line is climbing pretty smoothly.
The Gas Usage graph (and this months totals) indicate different:
Any ideas how to correct this?
Regards, Frank
Got it working by setting up a sensor in yaml that only updates on trigger and importing the data in that one.
template:
- trigger:
- trigger: event
event_type: power_cycle_bill_cost_t1_update
sensor:
- name: "Power Cycle Bill Cost (T1)"
unique_id: power_cycle_bill_cost_t1
state_class: measurement
unit_of_measurement: "€"
state: >
{{this.state}}
Check the history with a graph showing state and sum, something is probably wrong there. I do not know what the energy dashboard uses (state or sum).
You imported hourly values? (So, one value every hour)? Thats necessary.
Thanks, Klaus. I imported one record per day at 00:00, using the same values for state and sum. There were some issues, most which I managed to correct manually by now (using developer-tools statistics). Just a few records at other times during the day seem to spoil the graph now.
I’m looking for an easy way to get rid of those records (using DB browser for SQLite) but I’m afraid to connect to the database while HA is running.
Regards, Frank
Hi Klaus,
I have a solar system that is not integrated into HA, but I can obtain monthly energy produced by my energy provider. I want to show this data into HA and I have used your integration to import data. Since there is no sensor associated and following your guide, I have used the “sensor:” notation:
You can import:
- Either statistics for existing sensors (internal statistics). These sensors have a ‘.’ in its name, e.g. sensor.sun_solar_azimuth
- If you try to import such a sensor which does not exist, you will see this sensor under developer tools / statistics, with an error. You can fix the error there, whereas fix means, remove it from database again
- Or statistics for not existing sensors (external statistics). These sensors have a ‘:’ in its name, e.g. sensor:not_existing_sun_solar_azimuth
The sensor shows up in Developers Tools > Statistics, but I can not find it in any other place in HA.
Any suggestion on how to use it, for example, in a graph?
There are also some errors in the data imported (I have not specified the state value), how can I delete this data and import them again?
Thank you.
if it’s visible in Statistics then it’s most likely all OK…can you see the sensor under Developer Tools > States?
As far as I know if you reimport the same (but corrected) values they will just overwrite the existing entries and you’ll get the result you want
No, if i search for it here no entity show up. I have looked for it everywhere, nothing found, it is visible only into Statistics.
how did you setup the Sensor entity so?
Under normal circumstances an entity is created and it’s attributes (device_class and state_class) tell HA whether it has Statistics or not
TBH I’m not sure how you’d create a Statistic without an Entity to start with…unless this integration is doing it…I’ve used it to add values to a template sensor I have and the imported data shows fine as an entity and as statistics…
edit…re-reading the GitHub notes for this maybe you have only created the statistics without an underlying sensor so that would be the issue (though Integration is functioning as designed)
from GitHub
you can import:
** Either statistics for existing sensors (internal statistics). These sensors have a ‘.’ in its name, e.g. sensor.sun_solar_azimuth*
-
- If you try to import such a sensor which does not exist, you will see this sensor under developer tools / statistics, with an error. You can fix the error there, whereas fix means, remove it from database again*
** Or statistics for not existing sensors (external statistics). These sensors have a ‘:’ in its name, e.g. sensor:not_existing_sun_solar_azimuth*
- If you try to import such a sensor which does not exist, you will see this sensor under developer tools / statistics, with an error. You can fix the error there, whereas fix means, remove it from database again*
so if you’ve chosen the second option it’s expected that no sensor get’s created
there are Statistic Cards available in HA that let you use this data so that might be an alternate route for you…
I haven’t, following the guide into the integration page was specified that i can import the data in this way if the sensor do not exists.
Can you please share the code of your template sensor? As i know a template sensor show “manipulated data” of other sensor/s into it’s state. What i have to put into the “state template” field of my helper template sensor for my use case?
I’ve edited my original post as you were replying so understand what you’ve done now…
what I did was create a blank/non-updating sensor with zero as current value…made sure it had the right device/state class and then imported my history (to show old Energy usage)…
the template sensor I have is created as a helper in HA…see setting I used below
I am not sure what you are trying to achieve. When you do not have “live” data from a sensor, but just importing historical data with the integration, then importing with the “:” is totally fine. You do not need a template sensor then.
Then you “only” have statistic data, and you need to use statistic cards to show these data in the UI:
Plotly is also a good way to show graphs.
1 Like
Thanks! this integration saved me from losing 4 years of statistics regarding energy.
having about 20 sensors, losing everything was really a drama.
I created a script in python that can facilitate the creation of the tsv, starting from the name of the sensor (which measures the kwh).
maybe it will be useful to someone, it is very simple but with a few inputs it allows you to recreate the situation that was lost.
it is specific for the format: counter statistics with state/sum.
the only 2 changes to be able to use it:
database_path = “/path/home-assistant_v2.db” (locally on your PC)
filter_value = “sensor.consumo_energetico_pi”
import sqlite3
import csv
def fetch_ids_with_filters(db_path, table_name, unit_column, unit_value, filter_column, filter_value):
try:
# Connessione al database SQLite
connection = sqlite3.connect(db_path)
cursor = connection.cursor()
# Query parametrizzata per evitare SQL injection
query = f"SELECT id FROM {table_name} WHERE {unit_column} = ? AND {filter_column} = ?"
cursor.execute(query, (unit_value, filter_value))
# Recupero degli ID
ids = [row[0] for row in cursor.fetchall()]
# Chiusura della connessione
cursor.close()
connection.close()
return ids
except sqlite3.Error as e:
print(f"Errore durante l'accesso al database: {e}")
return None
def fetch_statistics_with_metadata_id(db_path, table_name, metadata_id, statistic_id, unit_value):
try:
# Connessione al database SQLite
connection = sqlite3.connect(db_path)
cursor = connection.cursor()
# Query parametrizzata per filtrare metadata_id e sum non NULL
query = f"SELECT '{statistic_id}' AS statistic_id, '{unit_value}' AS unit, strftime('%d.%m.%Y %H:%M', start_ts, 'unixepoch') AS start, state, sum FROM {table_name} WHERE metadata_id = ? AND sum IS NOT NULL"
cursor.execute(query, (metadata_id,))
# Recupero dei dati
rows = cursor.fetchall()
# Recupero dei nomi delle colonne
column_names = [description[0] for description in cursor.description]
# Chiusura della connessione
cursor.close()
connection.close()
# Restituzione dei dati come lista di dizionari
return [dict(zip(column_names, row)) for row in rows]
except sqlite3.Error as e:
print(f"Errore durante l'accesso al database: {e}")
return None
def export_to_tsv(data, output_file):
try:
# Scrittura dei dati in formato TSV
with open(output_file, mode='w', newline='', encoding='utf-8') as file:
writer = csv.DictWriter(file, fieldnames=data[0].keys(), delimiter='\t')
writer.writeheader()
writer.writerows(data)
print(f"Dati esportati con successo in {output_file}.")
except Exception as e:
print(f"Errore durante l'esportazione dei dati: {e}")
if __name__ == "__main__":
# Specifica il percorso del database e il nome della tabella
database_path = "/path/home-assistant_v2.db"
table_name_meta = "statistics_meta"
table_name_stats = "statistics"
unit_column = "unit_of_measurement"
unit_value = "kWh"
filter_column = "statistic_id"
filter_value = "sensor.consumo_energetico_pi" # Cambia con il valore desiderato
output_file = "statisticdata.tsv"
# Recupera gli ID filtrati
ids = fetch_ids_with_filters(database_path, table_name_meta, unit_column, unit_value, filter_column, filter_value)
if ids:
print("ID trovati con unit_of_measurement = 'kWh' e statistic_id specificato:")
for id in ids:
print(id)
all_stats_data = []
# Esegui query sulla tabella statistics per ogni ID
for metadata_id in ids:
stats_data = fetch_statistics_with_metadata_id(database_path, table_name_stats, metadata_id, filter_value, unit_value)
if stats_data:
print(f"Dati trovati per metadata_id = {metadata_id}:")
for row in stats_data:
print(row)
all_stats_data.extend(stats_data)
else:
print(f"Nessun dato trovato per metadata_id = {metadata_id}.")
if all_stats_data:
export_to_tsv(all_stats_data, output_file)
else:
print("Nessun ID trovato o errore durante il download.")