Data import from Excel/Sheet/CSV for RFID card codes

[gorgor_gonzales]

Hi there,
Is there a way to import a dataset from an Excel file in the network or a Google Sheet into Home Assistant?

I am aware of the Google Sheets integration, but all I could find was how to export data to the sheet, not the other way around.

The project I want to use this for is an access control panel where people can present their RFID card and a gate opens. Since there are around a 150 RFID cards that are valid and the list changes quite often it is not practical to list them all in HA directly. I would rather have them in some external file where people who don’t have access to the HA Settings can also add or remove RFID codes.

The Hardware is an ESP32 with an RFID reader running ESPHome which exposes a text sensor in HA with the RFID code, this all works fine so far.

I’m also absolutely open to other creative solutions

Thanks!

[rjd (RJDalga)]

I am actually looking to do something similar. I’d like to download my solar data into Google sheets and then somehow import that into Home Assistant creating values that I can then put into the Energy dashboard.

[gorgor_gonzales]

I ended up using the google sheets node in NodeRed to check is the value reportet by my rfid reader is showing up in the spreadsheet.
For me the GSheet Node worked just fine node-red-contrib-google-sheets (node) - Node-RED
I would have preferred a solution directly in HA but this is fine for now…

[Aleksey-nt]

Display Google Sheet data in a Dashboard

My solution for analyzing scv file data. I have a google spreadsheet with access control data. I upload data in csv format, separated by commas, to the home as server. Next, I search for data in the csv file using input_text.data_search. The command line sensor searches for a term with the required data and places the result in the attributes. Perhaps this will be useful to someone. - sensor: name: BD_scv command: | grep {{ states("input_text.data_search") }} /config/www/downl…

[kbrown01]

See here for using JQ to parse CSV.

Create graph/automation based on river level

Here is an alternative that may prove better depending on your use. NOAA also provides raw CSV with just the graphical data for the chart. Using a command_line sensor and jq, you can process that to a more simplistic structure. Here is my example as of now. I am going to strip some characters out ("\r") and add names to the fields to make it easier to graph the data. NOTE: This is a simplistic approach and could fail if the CSV data has quotes in it or something like that. I believe it works in…

[Aleksey-nt]

This is a great solution. I’ll definitely try it.

[Aleksey-nt]

Thank you. I tried reading google sheets. Works great.

  - sensor: 
      scan_interval: 30
      name: bd sheets
      command: "curl -L 'https://docs.google.com/spreadsheets/d/jhwenfw92901203123nnm31902130/export?gid=816578980&range=A5:W5000&exportFormat=csv' | jq --raw-input '{data: [inputs | split(\",\") | {Date_of_delivery: .[0], Installation_date: .[1], Model: .[2], Power: .[4], Serial_number: .[5], MAC: .[6], IP: .[7] , Status: .[8]| gsub(\"[\\r]\"; \"\")  }] }'"
      value_template: "{{ now() }}"
      json_attributes:
        - data

[gorgor_gonzales]

That’s awesome, definitely gonna give this a try!