How to add filters to a Microsoft Excel CSV generated from Node-Red

General
1

Hello I am wondering if it is at all possible to add filters to my column data in my CSV. I have no trouble generating the report and adding my data to specific cells but I have no clue how to add filters. Any idea how or where I can find this info? Thank you.

2

What do you mean by filters?

3

I expect that it means Excel filters. These are the drop-downs on each column that lets to filter down the rows by the data in that column.

You will, I think, need to check the underlying library used by the various CSV->Excel nodes.

The easiest and most robust way to use filters is not to use them! :grinning: By which I mean that it is better to turn your data into an Excel Table. These implement filters automatically and have many other benefits as well. Again though, you will need to check which nodes might support this.

Please do let us know what you find.

Looks like this library has some support - exceljs - npm

By the way, there IS another way to achieve this. Instead of writing a new Excel file, output data as CSV and have a single Excel file that uses PowerQuery to import the latest CSV file in your output folder. I use this all the time, it is a very effective way of working since the PowerQuery automatically gives you a table (with filters of course) and you always get the latest data.

1 Like
4

Thanks for your advice. I think I am going to try your second option out and see how that works. Regarding the library I believe I saw an area to add an auto-filter, from my understanding it does not add a filter into the excel sheet but allows you to do a one time filter of the data you are adding to the sheet.

5

Yeah i would definitely be using Powerquery to bring the data in and turn it into a table with headers

You will be amazed by what you can achieve in powerquery and the M language with a little bit of work.

There are a number of very good Youtube channels that cover this in depth

Craig

6

Create a new query from a folder. Sort the folder so the latest files are top, filter to CSV files if needed. Select the top 1. Then remove the extra folder columns and expand the remaining table.

When you create a new CSV, simply refresh the query.

7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.