Reading multiple rows from csv

Configuration
1

Im receiving every day a csv document from my heating company in CSV.
With FILE i can already export data from the document but im only getting the last value from the document.
So I was curious if I can import all data with the time when the values were recorded.

Data:
1-8-2021 00:00 19,527 GJ 0,023 m3/h 59,98 C 57,76 C 172,574 m3
1-8-2021 01:02 19,528 GJ 0,025 m3/h 59,69 C 57,44 C 172,597 m3

senor:

  • platform: file
    name: Devo test
    file_path: www/dagelijks-verbruik-devo_20210802010010736.csv
    value_template: >
    {{ (value.split(’;’)[2] ) }}
2

There is no way that I know of to do this.

3

This might be totally off base and @tom_I may be 100% correct but based on the examples section of the FILE home assistant documentation you could write a perl or python script to parse the csv file to a json equivalent and then process it from there. I think this processing would have to be done outside HA and the json file placed into the correct directory.

The script would have to split on a “space” and you could then recombine. I don’t know how you would get the proper timestamp to be read in.

Just some thoughts

4

I wonder if a python script could do what you want? I have little experience with python and none with python scripts, so I’m just pointing you towards an idea, not necessarily a good one. :slight_smile:

5

I wrote a perl script below that converts your csv to a json file. I made it 24 hours

1-8-2021 12:00:00 AM 19,527 GJ 23 m3/h 59,98 C 57,76 C 172,574 m3
1-8-2021 01:02:00 AM 19,528 GJ 25 m3/h 59,69 C 57,44 C 172,597 m3
1-8-2021 02:02:00 AM 19,528 GJ 26 m3/h 59,69 C 57,44 C 172,598 m3
1-8-2021 03:02:00 AM 19,529 GJ 26.1 m3/h 59,69 C 57,44 C 172,598 m3
1-8-2021 04:02:00 AM 19,529 GJ 26.8 m3/h 59,69 C 57,44 C 172,598 m3
1-8-2021 05:02:00 AM 19,530 GJ 27.7 m3/h 59,69 C 57,44 C 172,599 m3
1-8-2021 06:02:00 AM 19,531 GJ 27.9 m3/h 59,69 C 57,44 C 172,599 m3
1-8-2021 07:02:00 AM 19,531 GJ 28.6 m3/h 59,69 C 57,44 C 172,600 m3
1-8-2021 08:02:00 AM 19,532 GJ 29.4 m3/h 59,69 C 57,44 C 172,601 m3
1-8-2021 09:02:00 AM 19,532 GJ 29.9 m3/h 59,69 C 57,44 C 172,601 m3
1-8-2021 10:02:00 AM 19,533 GJ 30.3 m3/h 59,69 C 57,44 C 172,602 m3
1-8-2021 11:02:00 AM 19,534 GJ 30.3 m3/h 59,69 C 57,44 C 172,602 m3
1-8-2021 12:02:00 PM 19,534 GJ 30.9 m3/h 59,69 C 57,44 C 172,602 m3
1-8-2021 02:02:00 PM 19,535 GJ 31.1 m3/h 59,69 C 57,44 C 172,603 m3
1-8-2021 03:02:00 PM 19,536 GJ 32.2 m3/h 59,69 C 57,44 C 172,604 m3
1-8-2021 04:02:00 PM 19,536 GJ 32.8 m3/h 59,69 C 57,44 C 172,604 m3
1-8-2021 05:02:00 PM 19,537 GJ 32.8 m3/h 59,69 C 57,44 C 172,604 m3
1-8-2021 06:02:00 PM 19,538 GJ 33.2 m3/h 59,69 C 57,44 C 172,605 m3
1-8-2021 07:02:00 PM 19,538 GJ 33.5 m3/h 59,69 C 57,44 C 172,605 m3
1-8-2021 08:02:00 PM 19,539 GJ 34.4 m3/h 59,69 C 57,44 C 172,606 m3
1-8-2021 09:02:00 PM 19,539 GJ 34.4 m3/h 59,69 C 57,44 C 172,606 m3
1-8-2021 10:02:00 PM 19,540 GJ 34.5 m3/h 59,69 C 57,44 C 172,606 m3
1-8-2021 10:02:00 PM 19,540 GJ 35.5 m3/h 59,69 C 57,44 C 172,607 m3
1-8-2021 11:02:00 PM 19,540 GJ 36.5 m3/h 59,69 C 57,44 C 172,608 m3

the perl scrip is:

#!/usr/lib/perl -w
#Written By JLG
#Version 1.0

use strict;
use warnings;
;
#
#-------------------------------------------------------------------------------------------------------------------
#
# Open the output files
#
#-------------------------------------------------------------------------------------------------------------------
#
my $file = './Error.txt';
open(ERROR, ">$file") || die print "Can't open error check $file \n" ;

$file = './file.json';  # open the output file
open(OUT, ">$file") || die print "Can't open error check $file \n" ;

#
#-------------------------------------------------------------------------------------------------------------------
#
# Read the data from the csv file
#
#-------------------------------------------------------------------------------------------------------------------
#

$file = './file.txt'; # open the input file
open(INPUT, "<$file") || die print ERROR "Can't open $file \n" ;

my $line = '';

while ($line = <INPUT>) {
    $line =~ s/^\s+|\s+$//g;
    chomp $line;
    my @WA = split(/ /, $line); # split on space
    print OUT "{\"datetime\": $WA[0] $WA[1] $WA[2], \"energy\": $WA[3], \"rate1\": $WA[5], \"temperature1\": $WA[7], \"temperature2\": $WA[9], \"total_volume\": $WA[11]}\n";
}

close (INPUT);
close(OUT);
close(ERROR);

The output file is:

{"datetime": 1-8-2021 12:00:00 AM, "energy": 19,527, "rate1": 23, "temperature1": 59,98, "temperature2": 57,76, "total_volume": 172,574}
{"datetime": 1-8-2021 01:02:00 AM, "energy": 19,528, "rate1": 25, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,597}
{"datetime": 1-8-2021 02:02:00 AM, "energy": 19,528, "rate1": 26, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,598}
{"datetime": 1-8-2021 03:02:00 AM, "energy": 19,529, "rate1": 26.1, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,598}
{"datetime": 1-8-2021 04:02:00 AM, "energy": 19,529, "rate1": 26.8, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,598}
{"datetime": 1-8-2021 05:02:00 AM, "energy": 19,530, "rate1": 27.7, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,599}
{"datetime": 1-8-2021 06:02:00 AM, "energy": 19,531, "rate1": 27.9, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,599}
{"datetime": 1-8-2021 07:02:00 AM, "energy": 19,531, "rate1": 28.6, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,600}
{"datetime": 1-8-2021 08:02:00 AM, "energy": 19,532, "rate1": 29.4, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,601}
{"datetime": 1-8-2021 09:02:00 AM, "energy": 19,532, "rate1": 29.9, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,601}
{"datetime": 1-8-2021 10:02:00 AM, "energy": 19,533, "rate1": 30.3, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,602}
{"datetime": 1-8-2021 11:02:00 AM, "energy": 19,534, "rate1": 30.3, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,602}
{"datetime": 1-8-2021 12:02:00 PM, "energy": 19,534, "rate1": 30.9, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,602}
{"datetime": 1-8-2021 02:02:00 PM, "energy": 19,535, "rate1": 31.1, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,603}
{"datetime": 1-8-2021 03:02:00 PM, "energy": 19,536, "rate1": 32.2, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,604}
{"datetime": 1-8-2021 04:02:00 PM, "energy": 19,536, "rate1": 32.8, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,604}
{"datetime": 1-8-2021 05:02:00 PM, "energy": 19,537, "rate1": 32.8, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,604}
{"datetime": 1-8-2021 06:02:00 PM, "energy": 19,538, "rate1": 33.2, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,605}
{"datetime": 1-8-2021 07:02:00 PM, "energy": 19,538, "rate1": 33.5, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,605}
{"datetime": 1-8-2021 08:02:00 PM, "energy": 19,539, "rate1": 34.4, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,606}
{"datetime": 1-8-2021 09:02:00 PM, "energy": 19,539, "rate1": 34.4, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,606}
{"datetime": 1-8-2021 10:02:00 PM, "energy": 19,540, "rate1": 34.5, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,606}
{"datetime": 1-8-2021 10:02:00 PM, "energy": 19,540, "rate1": 35.5, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,607}
{"datetime": 1-8-2021 11:02:00 PM, "energy": 19,540, "rate1": 36.5, "temperature1": 59,69, "temperature2": 57,44, "total_volume": 172,608}

I can’t upload the files but you should be able to copy and paste.

Hope this is of value

1 Like
6

And now, getting this into the database?

1 Like
7

OP, do you “pull” (i.e. fetch) the CSV, or is it “pushed” to you daily?

If you fetch it, and assuming it’s actually updated hourly, you can imagine using a script to fetch it every hour, then to extract hourly values (but you will not be able to use the timestamps of the CSV).

If it’s pushed, or if the CSV is actually only generated daily, the best you can hope is to create a script that will get you a daily consumption.

In both case, the scripts would be called via a command_line sensor.

8

According to the FILE documentation:

Examples

In this section you find some real-life examples of how to use this sensor.

Entries as JSON

Assuming that the log file contains multiple values formatted as JSON like shown below:

[...]
{"temperature": 21, "humidity": 39}
{"temperature": 22, "humidity": 36}

Text

This would require the following entry in the configuration.yaml file to extract the temperature:

# Example configuration.yaml entry
sensor:
  - platform: file
    name: Temperature
    file_path: /home/user/.homeassistant/sensor.json
    value_template: '{{ value_json.temperature }}'
    unit_of_measurement: "°C"

what I don’t know is how to get the timestamp correct

9

Also in the documentation:

Only the last line of the file is used

10

Yup, that’s what I meant. Home Assistant updates the database in real time. There is no method to insert a value at a prior time.

11

My thought is you might have to write sql to change the database. This is not simple!

@koying You could easily write a single line for the file and upload 24times (I agree this is getting to be a bigger and bigger kludge). The issue is still the timestamp.

12

Here’s how i read multiple rows from a csv file and write them into a variables attribute with a python script.
The csv file with daily PV energy values:

date;pv_total;to_net;from_net;house;to_battery;from_battery
20210724;89.75;60.58;1.11;30.74;10.65;11.07
20210725;114.83;87.21;3.95;28.29;10.69;7.43
20210726;93.48;56.91;0.29;37.93;10.70;11.76
20210727;102.52;73.59;1.32;28.91;10.67;9.31
20210728;96.00;67.65;0.75;27.23;10.82;8.95
20210729;129.42;90.80;0.46;40.26;10.36;11.52
20210730;153.06;120.73;2.32;32.20;10.67;8.22
20210731;123.30;95.01;0.13;28.35;10.28;10.30
20210801;93.31;64.61;3.00;31.04;10.45;9.79
20210802;94.50;55.34;2.20;45.91;12.02;16.53

The python script (Trial and error, i’m no python expert) :slight_smile:

import csv
import requests

csv_file = '/home/homeassi/.homeassistant/daily_energy.csv'
url = 'http://localhost:8123/api/states/variable.'
days = 7

headers = {
    'Authorization': 'Bearer abcdefg',
    'content-type': 'application/json'
}

# Read the csv_file
csv_reader = csv.DictReader(open(csv_file), delimiter=';')
# Take the last (days) rows
the_list = [dict(d) for d in csv_reader][-days:]
# Revert the list, we want the last row at the top
the_list.reverse()

errors = ''

variable = 'daily_pv_energy'
state = '1' # a dummy state
attributes = '{"entries": ' + str(the_list) + '}'

data = '{"state": "' + str(state) + '", "attributes": ' + attributes + '}'
data = data.replace("'",'"')
#print(data)
r = requests.post(url+variable, data=data, headers=headers)
if r.status_code != 200 and r.status_code != 201:
    errors = errors + 'ERROR:' + variable + ' - ' + str(r.status_code)

if errors != '':
    print(errors)

It’s called from cron if a new day starts.

# Daily PV energy to variable for list feed
1 0 * * *	/usr/bin/python3 /home/homeassi/scripts/variable_set_daily_pv_energy.py

Here’s how the variables attribute is created.

Auswahl_468
Auswahl_468858×572 51.8 KB

And here how i show the entries in a list card:
Auswahl_469

          - type: custom:list-card
            entity: variable.daily_pv_energy
            title: Letzte 7 Tage (kWh)
            feed_attribute: entries
            columns:
              - title: Datum
                field: date
                style:
                  - text-align: left
              - title: PV
                field: pv_total
                style:
                  - text-align: right
              - title: Haus
                field: house
                style:
                  - text-align: right
              - title: Netz
                field: from_net
                style:
                  - text-align: right
              - title: Einsp.
                field: to_net
                style:
                  - text-align: right
              - title: Batt.
                field: to_battery
                style:
                  - text-align: right
              - title: Entl.
                field: from_battery
                style:
                  - text-align: right

Hope that helps.

8 Likes
13

Hi there, looking to your example I’m trying to do something similar. I have a csv as follow:

timestamp,weight
yyyy-mm-dd hh:mm:ss,value1
yyyy-mm-dd hh:mm:ss,value2
yyyy-mm-dd hh:mm:ss,value3
…

I tryied to adapt your python script but w/o success. I get such error by executing it:

immagine
immagine881×292 18.1 KB

and also a warning
immagine

Do you have any idea on what I’m doing wrong? Thanks!

14

The python script has nothing to do, and doesn’t work with the Python Scripts integration. :thinking:
I know, confusing, but it’s called like a shell script on the commandline.

1 Like
16

Sorry for digging this out.

I am trying to adapt the script for my purpose and getting an error on execution:

2023-07-13 17:43:50.322 ERROR (MainThread) [homeassistant.helpers.script.websocket_api_script] websocket_api script: Error executing script. Unexpected error for call_service at pos 1: invalid syntax (energy.py, line 10)
...
  File "/config/python_scripts/energy.py", line 10
    'content-type': 'application/json'
                  ^
SyntaxError: invalid syntax

If comment that line out, the script runs through and the result is as expected.

Any advice?

17

Hey there,

i stumbled over this topic because im trying to read an multi column csv into HA. I tried to integrate your python script, but get an error - is there anyway you updated this and can help me read this csv file :slight_smile: