Excel and Node-RED Integration: How to Map and Show Data?

[khunakon]

Hello everyone,

I'm currently working on a project where I'm using Node-RED to read data from a PLC using the FINS protocol. I've been successful in receiving numeric data from the PLC, but now I want to map this data to specific text entries in an Excel file.

My Excel file is structured with two columns: Column A contains part numbers (numeric data), and Column B contains corresponding descriptive text. Here's an Example

My goal is to take the numeric input from the PLC, match it with a part number in the Excel file, and output the corresponding text in Node-RED. For instance, if I receive '10' from the PLC, I'd like Node-RED to output 'AB'.

So far, I've tried using the node-red-contrib-spreadsheet-in node to read the data from the Excel file, but I'm not quite sure how to map the data from the PLC to the part numbers in the Excel file and then output the corresponding text.

Has anyone done anything similar or could provide some guidance on how to achieve this? I would really appreciate your help!

Thank you in advance.

[E1cid]

What format does the spreadsheet in output?

Here is an example using a csv to output an array of objects. You can then use JSONata in a change node to search it. This could also be done in a function node.

[{"id":"9e8b475e121dbc44","type":"inject","z":"b9860b4b9de8c8da","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"10","payloadType":"num","x":130,"y":1220,"wires":[["558239f8b57c11e3"]]},{"id":"558239f8b57c11e3","type":"change","z":"b9860b4b9de8c8da","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"find_part","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":230,"y":1160,"wires":[["4909404408d0b040"]]},{"id":"4909404408d0b040","type":"template","z":"b9860b4b9de8c8da","name":"csv from excel","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"10,ab\n20,cd\n30,ef\n40,gh","output":"str","x":340,"y":1220,"wires":[["4f7d262c15c6a748"]]},{"id":"4f7d262c15c6a748","type":"csv","z":"b9860b4b9de8c8da","name":"","sep":",","hdrin":"","hdrout":"none","multi":"mult","ret":"\\n","temp":"part,location","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":510,"y":1220,"wires":[["6f89596875d62bc7"]]},{"id":"6f89596875d62bc7","type":"change","z":"b9860b4b9de8c8da","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload[part =$$.find_part].location","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":580,"y":1160,"wires":[["315ace8e8c6cecae"]]},{"id":"315ace8e8c6cecae","type":"debug","z":"b9860b4b9de8c8da","name":"debug 288","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":710,"y":1220,"wires":[]}]