xlsx Node

[Adam S]

I'm a node-red noob, and I'm looking for excel support in node-red.  I see there is a node for working with .xlsx files here but from what I have found regular nodes from npm do not "just work" with node-red.  What are the options?  Is it easy to create a node for node-red from an existing npm node?

[Ben Hardill]

Writing Node-RED nodes is not that hard, there are docs for what you need here:

http://nodered.org/docs/creating-nodes/

You will need to decide what parts of the node you want to expose to the flow and create the config UI.

[Dave C-J]

the difficulty will be more matching the styles of models... Node-RED is event based - aimed at handling dynamic data... whereas spreadsheets are typically much more static...  - Yes you could usr Node-RED to poke data into cells or read it out  - but I've never had much joy with multiple points of access - (things and people) into a dynamic spreadsheet... but yes it would be possible :-) .. Good luck !

[Julian Knight]

There's no question that handling Excel spreadsheets can be challenging. I've written a few processing systems over the years though the last one I did was in PHP some years ago now.

You might want to start simple with a couple of nodes to read/write a spreadsheet to JSON. Even this has a number of things you will need to decide on. A workbook may contain several sheets, each sheet might contain several tables and/or named areas. There is also a data model behind the scenes which can also store data. So you might want to consider how you will get the possible input structures, list them and allow the user to choose from them.

Perhaps you would like to expand on what your priority requirements might be? 

[Adam S]

On Thursday, January 7, 2016 at 5:33:38 AM UTC-5, Dave C-J wrote:

the difficulty will be more matching the styles of models... Node-RED is event based - aimed at handling dynamic data... whereas spreadsheets are typically much more static...  - Yes you could usr Node-RED to poke data into cells or read it out  - but I've never had much joy with multiple points of access - (things and people) into a dynamic spreadsheet... but yes it would be possible :-) .. Good luck !

You're correct, it is not ideal.  But, this is how the data begins its life as so I will need to deal with it.

On Thursday, January 7, 2016 at 4:29:15 PM UTC-5, Julian Knight wrote:

There's no question that handling Excel spreadsheets can be challenging. I've written a few processing systems over the years though the last one I did was in PHP some years ago now.

You might want to start simple with a couple of nodes to read/write a spreadsheet to JSON. Even this has a number of things you will need to decide on. A workbook may contain several sheets, each sheet might contain several tables and/or named areas. There is also a data model behind the scenes which can also store data. So you might want to consider how you will get the possible input structures, list them and allow the user to choose from them.

Perhaps you would like to expand on what your priority requirements might be? 

On Thursday, 7 January 2016 10:33:38 UTC, Dave C-J wrote:

the difficulty will be more matching the styles of models... Node-RED is event based - aimed at handling dynamic data... whereas spreadsheets are typically much more static...  - Yes you could usr Node-RED to poke data into cells or read it out  - but I've never had much joy with multiple points of access - (things and people) into a dynamic spreadsheet... but yes it would be possible :-) .. Good luck !

I'm making a program that will read an xlsx file with a sheet named the current date then feed that in to a function that make the data presentable and send it to a websocket for multiple people to view at once.  There will also be a push button (or a few) that will have an effect on this data and then write the changes back to the xlsx file.  Now that I'm typing this out it may be a better idea to use the speadsheet for one time read-only to populate an SQLite database then work from that.  Which, of course, can be done with a simple cron job with existing code I have for python.  After it's in SQLite I think I can find a way to get what I need since there appears to be an SQLite node.  I would be interested to see some sort of node-red node for modifying excel files though, since I'm sure I'm not the only one that has needs with excel files!

[Josh Conway]

It's not a node, but there are some tools we could cobble together to make a flow that would effectively work as you ask.

In Gnumeric, there exists a program called ssconvert . It is a spreadsheet commandline conversion tool... See where I am going? The goal here is, "How do we convert a spreadsheet to something with messages?"

I have my diabetic spreadsheet (was diagnosed 1 month ago) in the exec node, so change it to whatever chart you're using.

Inject->Exec->CSV->debug

And it outputs 1 row as an object per message sequentially to end of spreadsheet.

[{"id":"450c4ccd.41a16c","type":"exec","z":"20240cab.96b054","command":"ssconvert \"/home/josh/Documents/diabetes spreadsheet.ods\" fd://1 -T Gnumeric_stf:stf_csv","addpay":true,"append":"","useSpawn":"","name":"Convert XLSX to CSV","x":263,"y":157,"wires":[["a411edbc.cba66"],[],[]]},{"id":"a411edbc.cba66","type":"csv","z":"20240cab.96b054","name":"","sep":",","hdrin":"","hdrout":"","multi":"one","ret":"\\n","temp":"","x":449,"y":144,"wires":[["1b163ba3.536dcc"]]},{"id":"1b163ba3.536dcc","type":"debug","z":"20240cab.96b054","name":"","active":true,"console":"false","complete":"false","x":600,"y":144,"wires":[]},{"id":"bd88ccef.9772f","type":"inject","z":"20240cab.96b054","name":"","topic":"","payload":"","payloadType":"none","repeat":"","crontab":"","once":false,"x":80,"y":154,"wires":[["450c4ccd.41a16c"]]}]

It's not quite what you were asking for (you wanted I assume a node that you send in data on msg.payload or file location msg.filename   ... But this can work. Now, load the data in a database and you can have a live DB hosting the content. And periodically, you can read the DB and regenerate the XLSX file. But I'm sure you can do that :-)

Sincerely,
Josh Conway

[Josh Conway]

For the dataset.

I ask only 1 condition of using this data: If you use this as part of, or wholly in an academic work, email me the final paper in which you used it in.

Sincerely,
Josh Conway

[Julian Knight]

Hi Adam, 

I would personally go for the separate process to translate Excel to SQLite and then process that. The initial process could be controlled by NR as Josh suggests. That could be event driven too simply using the watch node. Now you have reduced a complex task into a couple of much simpler tasks.

On Friday, 8 January 2016 13:31:16 UTC, Adam S wrote:

...

[Adam S]

On Friday, January 8, 2016 at 11:01:27 AM UTC-5, Josh Conway wrote:

 Hi Josh,

Thanks for your code!  Unfortunately for my purposes I need the modified data to be saved to disk and not in memory so in case the Pi loses power, crashes, etc it can pick up where it left off.  This program will be working on the data from the spreadsheet for a few hours and to lose track of where it is at in the process is not an option.  I think at this point reading the excel file with my current code and then saving to SQLite is pretty much the best/only option.

[Josh Conway]

Of course you don't want it RAM. That would be a "Bad Thing".

What that code does, is is the import side of an XLSX->JSON msg per row. That's all I did. there's no DB import or anything, because I'm not sure what DB tech you want to use. Now, you said you're using SQLite db, so cool.

What you want is 3 flows (if you allow editing of the DB. if not, ignore #3).

1. My initial flow to import the data into your DB, whilst changing the "Inject" to a "Watch" node.

2. Then you want the DB->web flow.

3. And lastly you want a reverse of my initial flow that outputs the whole DB table to ssconvert and re-makes the XLSX file and saves it to destination.

Doing this would allow real-time updates to the DB whenever the XLSX file is modified. A trigger is sent to import file to DB table. On the web end, as users come to see the data, the Websocket spools the data from the DB as needed, providing real-time updates. You could also provide a "lastupdated" log so your webapp will know when to refresh. And then if you allow web-editing of said data, you can then export said DB table back to the file.

On a side note; it would not be a good idea to do some sort of "catch-all" function that does everything with a file format. We have plenty of parsers, formatters, input and output handlers. It would be prudent to use simple pieces to create the complex "edit binary blob format" rather than some npm install node-red-contrib-gunk that may not even work. It's a fine line there, but there's my 2 bits :)

Sincerely,
Josh Conway