How can I read CSV columns periodically?

I run speedtest-cli every hour using a python script on my Pi4 which saves the results into a CSV file, I would like to extract the values from the file and graph it in Home Assistant.

My CSV file looks like this

Date,Time,Ping (ms),Jitter (ms),Download (Mbps),Upload (Mbps)
03/17/23,20:13,2.42,0.09,716.99,867.84

In my configuration.yaml file I have the following:

sensor:
  - platform: file
    name: Download Speed
    file_path: /speedtest/speedtest.csv
    value_template: '{{ value.split(",")[4] }}'
    unit_of_measurement: "Mbit"

I have a few issues with the current setup:

  • The sensor doesn’t seem to be displayed anywhere, I cant see it in services either when I search for “Download”
  • This only reads the Download column, I also want to track the Upload column. How can I make changes to the configuration to reflect this?
  • How often is the file read? My python script will write to the end of the csv every hour at *:45, I only want to store those values hourly in HA to avoid duplication.

Any reason you don’t just use the Speedtest integration?

I did try that initially, but I run HA in a docker on Pi4 so the speeds arent accurate. A speed test through cli will achieve 900mbps (which is accurate), using the Speedtest integration within HA only gets around 600mbps.

Ok. Look in Developer Tools → States (not services), do you see your sensor there?

If not, did you restart after creating the sensor?

Are there any errors in the system log?

For upload:

sensor:
  - platform: file
    name: Download Speed
    file_path: /speedtest/speedtest.csv
    value_template: '{{ value.split(",")[4] }}'
    unit_of_measurement: "Mbit"
  - platform: file
    name: Upload Speed
    file_path: /speedtest/speedtest.csv
    value_template: '{{ value.split(",")[5] }}'
    unit_of_measurement: "Mbit"

Pretty sure the default polling interval is 30 seconds. Home assistant will only store another value in the database if the value changes.

Thank you, this has worked. There was an error reading the directory, aftering whitelisting the dir in the config its now being displayed.