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

**URL:** <https://discourse.nodered.org/t/excel-and-node-red-integration-how-to-map-and-show-data/78342>\
**Category:** General\
**Tags:** database\
**Created:** [11 May 2023 02:07 UTC](https://discourse.nodered.org/t/excel-and-node-red-integration-how-to-map-and-show-data/78342 "2023-05-11T02:07:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![khunakon](https://avatars.discourse-cdn.com/v4/letter/k/3da27b/32.png) [@khunakon](https://discourse.nodered.org/u/khunakon)\
**Post date:** [11 May 2023 02:07 UTC](https://discourse.nodered.org/t/excel-and-node-red-integration-how-to-map-and-show-data/78342/1 "2023-05-11T02:07:37Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/6/66e606342163d03086cab312e737b411aa5a837f.png)

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/f/afe32d4b463f37752451a7d04212d8bae91f2811.png)

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [11 May 2023 08:19 UTC](https://discourse.nodered.org/t/excel-and-node-red-integration-how-to-map-and-show-data/78342/2 "2023-05-11T08:19:31Z")

</div>

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.

```auto
[{"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":[]}]

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [10 July 2023 08:20 UTC](https://discourse.nodered.org/t/excel-and-node-red-integration-how-to-map-and-show-data/78342/3 "2023-07-10T08:20:22Z")

</div>

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