How do I import excel 365 values one for each day?

Configuration
1

Hi all
I have in excel 365 values one for each day of the year. Ideally I would like to import in a sensor the value of the actual date. eg today 26/10 HA import in a sensor the of 26/10 from my excel.
Can I achieve this somehow?

2

Up to my best knowledge, it will be much easier if you put all this data into DB and use SQL sensor: https://www.home-assistant.io/integrations/sql/

1 Like
3

If the computer with the excel sheet is on all the time then you could have excel email it to HA or output it in a text file or something that is easier for HA to read.
But it requires some VBA.

4

It could be in txt format. How I could import it to HA sensor for example?
lets say I have a txt file that it has:
01/01/2020 120
02/01/2020 150
03/01/2020 165

What is the way to import it to HA as a sensor ? or in what format so I can use these values each day?

5

I believe you could use json format.
If you have one sensor with the complete json and then have another that puls the specific value from the other.

Or if the text file only has todays value https://www.home-assistant.io/integrations/file/

1 Like
6

thanks, I will have a look on this

7

Did you get it to work?
I am trying to pick up a cell value from a Excel sheet, the file on OneDrive.

8

Hi, unfortunately no I didn’t.

9

I did find this

But since I am a N00b, i cant figure it out how to implement this:

function main(workbook: ExcelScript.Workbook:disappointed_face: string {
let sheet = workbook.getWorksheet(“type name of sheet”);
let range = sheet.getRange(“C14”);
return JSON.stringify(range.getValues());
}

10

Here’s a sensor I use that grabs data for River Flooding levels in CSV, uses JQ to reorganize it how I wish and creates a sensor from the result. The “state” is the maximum river level and the attributes carry the date, level and some additional information (like if the river is rising or draining):

- sensor: 
      scan_interval: 3600
      name: Russian River Flood Info
      command: "curl -s 'https://www.cnrfc.noaa.gov/graphicalRVF_csv.php?id=GUEC1' | jq --raw-input '{flooddata: [inputs | split(\",\") | {issued: .[0], valid: .[1] | strptime(\"%m/%d/%Y %I %p\") | strftime(\"%m/%d/%Y %I:%M %p\"), validts: .[1] | strptime(\"%m/%d/%Y %I %p\") | strftime(\"%Y-%d-%m %H:%M:%S\"), level: .[2] | tonumber, trend: .[3], status: .[4], mode: .[5] | gsub(\"[\\r]\"; \"\")  }]| .[2:999] }'"
      value_template: >
            {% set levels = namespace(lvl=[]) %}  
            {% for height in value_json.flooddata %}
              {% set levels.lvl = levels.lvl + [height.level] %}
            {% endfor %}
            {{levels.lvl | max }}
      json_attributes:
        - flooddata
11

If you have an excel file with static data and you just want to get it into HA once: reformat the data into json or YAML, create a template sensor, and set the value of an attribute to the values you want to store.

If you have dynamic data in an excel sheet that you want HA to look at on some time interval, you will need a different method. You could use python (using pyscript or AppDaemon) and read the file in with Pandas. If you have the ability to move or copy the file you can get it into google docs and access the data with Google’s API. Or get it into CSV format and read it with a JQ command like the suggestion from kbrown.

There’s probably a million different ways to do it, there’s just no native way for HA to directly access an Excel file so you have to pick which middleman process you want to employ.