Node-red simple CSV two column lookup

Hi,

I'm hoping someone can help as I've trawled through forums and posted flows and not been able to do what I want to achieve.

In principle, its quite simple.
I have some excel data, 2 columns worth, relating a 'partnumber' to a 'group name'.
There are approx 7000 entries.

A siemens PLC, accsessed via the S7-Input node, gives me the information in a msg.payload in string format of the 'partnumber'.
I simply want to lookup my excel file, or csv file, or even excel to JSON convered file, and send the corresponding 'group name' or 2nd column value to an outbound message. If I achive it working in simply a debug output I would deem that SUCSESS!!!

Can anyone help?.........................praying on knees

Tbh, this data should be in a database.

However, you could read the file into memory and create a lookup object and store it in flow or global context.

If you provide a sample of data from your CSV I can show you. Attach a small CSV (you can use fake data if need be)

here is an example of looking up a groupname using a csv file, i have simulated the cvs, you can use a file in node and read a cvs file.
This uses JSONata expression in the Change node to find the group name.

Hope it helps

[{"id":"63069c9b.875c6c","type":"change","z":"c74669a0.6a34f8","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"part_number","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":250,"y":2760,"wires":[["96f157ce.6fb87"]]},{"id":"e5b8fc50.2447a8","type":"inject","z":"c74669a0.6a34f8","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1236","payloadType":"str","x":80,"y":2760,"wires":[["63069c9b.875c6c"]]},{"id":"96f157ce.6fb87","type":"template","z":"c74669a0.6a34f8","name":"simulate csv file","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"1234,group1\n1235,group2\n1236,group3\n1237,group4\n","output":"str","x":520,"y":2760,"wires":[["fbd4515c.070c38"]]},{"id":"fbd4515c.070c38","type":"csv","z":"c74669a0.6a34f8","name":"","sep":",","hdrin":false,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":190,"y":2820,"wires":[["687e698f.e16a98"]]},{"id":"687e698f.e16a98","type":"change","z":"c74669a0.6a34f8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[col1=$number($$.part_number)].col2","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":2820,"wires":[["327aee38.7d8842"]]},{"id":"327aee38.7d8842","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload.groupName","statusType":"msg","x":570,"y":2860,"wires":[]}]

This would be better done in a DB as less overhead.

Here is a version that caches the file as a lookup object for much faster lookup (than loading file/jsonata looking though 7000 entries)

Rz3hcnu4OV

Demo Flow...

[{"id":"c2505e69813e5c17","type":"function","z":"bec69dbd.8d622","name":"Load CSV file","func":"//generate fake data\n\nvar csvData = [];\nfor (let index = 0; index < 7000; index++) {\n    const r = Math.random()\n    const element = `${index+1},${makeStr(5)}`;\n    csvData.push(element)\n}\nmsg.payload = csvData.join(\"\\n\");\n\nfunction makeStr(length) {\n    var result = '';\n    var characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';\n    var charactersLength = characters.length;\n    for (var i = 0; i < length; i++) {\n        result += characters.charAt(Math.floor(Math.random() *\n            charactersLength));\n    }\n    return result;\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":600,"y":740,"wires":[["3aafe21212078440"]]},{"id":"4a3b47715ce31f13","type":"inject","z":"bec69dbd.8d622","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":430,"y":740,"wires":[["c2505e69813e5c17"]]},{"id":"3aafe21212078440","type":"csv","z":"bec69dbd.8d622","name":"","sep":",","hdrin":"","hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":770,"y":740,"wires":[["6ac8be012310f99f"]]},{"id":"6ac8be012310f99f","type":"function","z":"bec69dbd.8d622","name":"Make lookup","func":"var data = msg.payload;\nvar map = {};\nfor (let index = 0; index < data.length; index++) {\n    const element = data[index];\n    map[element.col1] = element.col2;\n}\nmsg.payload = map;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":930,"y":740,"wires":[["dd59fdbb1e0285a9"]]},{"id":"dd59fdbb1e0285a9","type":"change","z":"bec69dbd.8d622","name":"","rules":[{"t":"set","p":"partLookup","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1120,"y":740,"wires":[[]]},{"id":"c9400dc5833fa881","type":"inject","z":"bec69dbd.8d622","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1236","payloadType":"str","x":430,"y":800,"wires":[["b09691ebb53375a7"]]},{"id":"b09691ebb53375a7","type":"change","z":"bec69dbd.8d622","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"partLookup[msg.payload]","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":600,"y":800,"wires":[["ea5813699d5a3e78"]]},{"id":"ea5813699d5a3e78","type":"debug","z":"bec69dbd.8d622","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"msg","x":770,"y":800,"wires":[]},{"id":"4350b89a419f884f","type":"inject","z":"bec69dbd.8d622","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"6555","payloadType":"str","x":430,"y":840,"wires":[["b09691ebb53375a7"]]},{"id":"cd7818824114141b","type":"comment","z":"bec69dbd.8d622","name":"change to file in node","info":"","x":600,"y":713,"wires":[]}]

This demo flow generates 7000 fake part numbers and part IDs as a CSV & then converts them to a lookup object (so just replace the Load CSV function with a file in node)

the lookup object is stored in flow context (in memory) for fast access.

Hi,

Many thanks for the amazing quick repsonse (to all!).

To save ambiguity, I'm attaching the actual .csv file.

So, I'll recieve a message with something from the first column (partnumber) and want to retrieve the corresonding second column (family).

Again, thanks all for their input. I'll be trying 1st thing in the morning.

May thanks :slight_smile: :grinning:

Sorry,
Couldnt attach actual csv, ut here is a link to it on onedrive..