Solarman Export Data

[wifi75]

Hi Patrick from the Solarman site, a site used by many Chinese innvrter, it would be interesting to import the data on Home assistant. I attach the export file that I just generated from my portal account:

https://globalhome.solarmanpv.com/plant/infos/exportdata

I only need to import the battery data, but many others will definitely need to import everything

Casa-Daily Statistics-20250316.xlsx

[patrickvorgers]

I just added support for batteries and Solman as a datasource.
Could you verify whether this works for you?

[wifi75]

I just added support for batteries and Solman as a datasource. Could you verify whether this works for you?

hi in the file Import Energy data into Home Assistant.sql what do I have to change to add the battery data in and out?

questi sono i due file che ho generato con il tuo script che devo importare in HA

elec_battery_feed_in_high_resolution.csv
elec_battery_feed_out_high_resolution.csv

[patrickvorgers]

In the SQL file for MariaDB you have to specify the ID's of the battery charge and battery discharge.
In the example below it is ID 450 (charge) and ID 451 (discharge).

/*                          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_elec_battery_feed_in',   450,      1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_battery_feed_out',  451,      1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_water',                  653,      1000.0,    25.0,            1000.0); /* Change */

Your provided CSV files only contain information from this month (start date 2nd of March), is this correct?

[wifi75]

In the SQL file for MariaDB you have to specify the ID's of the battery charge and battery discharge. In the example below it is ID 450 (charge) and ID 451 (discharge).

/*                          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_elec_battery_feed_in',   450,      1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_battery_feed_out',  451,      1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_water',                  653,      1000.0,    25.0,            1000.0); /* Change */

Your provided CSV files only contain information from this month (start date 2nd of March), is this correct?

ah ok, can I import the two csv together and then run the .sql script just once?

[patrickvorgers]

The procedure has not changed from what you have done with the other csv files.
But because you already have the data of the other sensors, you can just comment them out and only keep the battery sensors and only import the two generated CSV files for the battery.

/*                          name                                sensor_id correction cutoff_new_meter cutoff_invalid_value */
INSERT INTO SENSORS VALUES ('sensor_id_elec_battery_feed_in',   450,      1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_battery_feed_out',  451,      1000.0,    25.0,            1000.0); /* Change */

Change the sensor_id with the value found in your installation (similar to what you have done with the other sensors.

Only the data of the battery will be imported when you define only these two sensors.

[wifi75]

The procedure has not changed from what you have done with the other csv files.
But because you already have the data of the other sensors, you can just comment them out and only keep the battery sensors and only import the two generated CSV files for the battery.

/*                          name                                sensor_id correction cutoff_new_meter cutoff_invalid_value */
INSERT INTO SENSORS VALUES ('sensor_id_elec_battery_feed_in',   450,      1000.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_battery_feed_out',  451,      1000.0,    25.0,            1000.0); /* Change */

Change the sensor_id with the value found in your installation (similar to what you have done with the other sensors.

Only the data of the battery will be imported when you define only these two sensors.

why?

[patrickvorgers]

Could you please download the latest version of the SQL script?
The latest version fixes this issue (table name should be in caps).

[wifi75]

Could you please download the latest version of the SQL script? The latest version fixes this issue (table name should be in caps).

the same

[patrickvorgers]

Could you retry again? I fixed another tablename.

[wifi75]

Could you retry again? I fixed another tablename.

data import now it is ok
but value not correct.

[patrickvorgers]

Can you double check the id's?

• configuration energy dashboard
• statistics_meta definition

[wifi75]

Can you double check the id's?

• configuration energy dashboard
• statistics_meta definition

yes:

[patrickvorgers]

Seems correct, what have you defined in the energy dashboard in Home Assistant?

[wifi75]

Seems correct, what have you defined in the energy dashboard in Home Assistant?

see below:

[patrickvorgers]

For how long do you have the battery configured in Home Assistant?

Because the total is very low. The script assumes that at least the short-term statistics are there (7 days).

[patrickvorgers]

Could you retry again? I fixed another tablename.

data import now it is ok but value not correct.

What is the period that you are displaying here (year/month/day etc.)?
Can you mabe create an export of the database and share it with me so that I can have a look on my end?

[wifi75]

Could you retry again? I fixed another tablename.

data import now it is ok but value not correct.

What is the period that you are displaying here (year/month/day etc.)? Can you mabe create an export of the database and share it with me so that I can have a look on my end?

the whole month of June 2024
below my full db
https://solar.iu3cyv.eu/MariaDB_homeAssistant_18032025.zip

[patrickvorgers]

I found the issue. In the configuration of the battery sensors you have specified that the input data should be corrected. This is normally needed when you go, for instance, from Wh to kWh. But in this case your input data is in kWh and your target sensor in Home Assistant is also in kWh. So the correctionfactor should be 1.0 (no correction).

INSERT INTO SENSORS VALUES ('sensor_id_elec_battery_feed_in',   372,      1.0,    25.0,            1000.0); /* Change */
INSERT INTO SENSORS VALUES ('sensor_id_elec_battery_feed_out',  373,      1.0,    25.0,            1000.0); /* Change */

You have to delete the incorrect records in case you want to use the database you provided to me. That can be done by the following query. This deletes records from the database so be sure that it is correct!

DELETE FROM statistics
WHERE
metadata_id IN (372, 373) AND
start_ts < 1725804000

Below the screenshot from my test machine using your database with the corrected import.

[wifi75]

ok I run the query to delete the data and import the csv again with the 1.0 modification (no correction).

[wifi75]

ok I run the query to delete the data and import the csv again with the 1.0 modification (no correction).

YESS !!!
Thank you

[patrickvorgers]

I was double checking something else and saw that your imported data (CSV files) is until the 1st of September but the data in Home Assistant starts at the 8th of September. This causes an incorrect graph. I think you should import again but make sure that the CSV files also contain the data of September 2024.

[wifi75]

I only imported data from April 1st to August 31st.

I might think about importing data from September 1st to September 7th

[patrickvorgers]

Yes that is incorrect. You should also include September (April - September). The script needs to have some overlap between the imported data and the Home Assistant data.

You cannot import September 1st to September 7th separately because it has to adjust all the records and it cannot insert data in between.

[wifi75]

Yes that is incorrect. You should also include September (April - September). The script needs to have some overlap between the imported data and the Home Assistant data.

You cannot import September 1st to September 7th separately because it has to adjust all the records and it cannot insert data in between.

but can I import everything or do I have to delete the data from the DB?

[patrickvorgers]

You have to execute the delete query again and then use the new CSV's (including the September data) and execute the script.

[wifi75]

You have to execute the delete query again and then use the new CSV's (including the September data) and execute the script.

could you please tell me the correct query including the month of September?

[patrickvorgers]

You have to execute the delete query again and then use the new CSV's (including the September data) and execute the script.

could you please tell me the correct query including the month of September?

It is the same query as you have been using.

DELETE FROM statistics
WHERE
metadata_id IN (372, 373) AND
start_ts < 1725804000

[wifi75]

You have to execute the delete query again and then use the new CSV's (including the September data) and execute the script.

could you please tell me the correct query including the month of September?

It is the same query as you have been using.

DELETE FROM statistics
WHERE
metadata_id IN (372, 373) AND
start_ts < 1725804000

Yess:

[patrickvorgers]

That looks a lot better!
Enjoy

[wifi75]

Thanks to you I could retire MeterN!!!
You are great!