Hello!
I would like to merge two CSVs.
With SQL, do this via a LEFT JOIN, is that also possible in node red?
Kind of. Have a try with node-red-contrib-alasql (node) - Node-RED (nodered.org) and see if that helps.
Hello!
Thank you for your mail.
Unfortunately, this is only available for one cell with SELECT.
I just want to merge two CSV data. as with SQL LEFF JOIN
here is an example
CSV 1
Item; number1
A; 1
B; 2
C; 1
D; 3
CSV 2
Item; number2
A; 4
B; 6
D; 8
and the result should look like this when the CSV is ready.
Item; number1; number2
A; 1; 4
B; 2; 4
C; 1
D; 3; 8
How big is the data?
You could convert the 2 CSV's to JavaScript objects with the first column as the key then you could merge them. There are node.js libraries that will do object merge or you could do it yourself with a loop.
You could also covert to objects then merge the objects with jsonata expression in a change node
$map($.csv1, function($v,$i){ $merge([$v, $$.csv2[$i]]) })
Then convert back to a csv, but delete the msg.column property first.
1 Like
Hello!
One file is 20000 lines and the other 300 lines.
Hello!
Thank you for the tip.
and how does the flow have to look then?
Is it correct that way?
[{"id":"7a1e5c34.0516d4","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"d3309d3c.a4cf08","type":"inject","z":"7a1e5c34.0516d4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":460,"wires":[["eabad911.f20f4","7db09b11.304bfc"]]},{"id":"20f412b9.d6fbde","type":"debug","z":"7a1e5c34.0516d4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1040,"y":460,"wires":[]},{"id":"eabad911.f20f4","type":"file","z":"7a1e5c34.0516d4","name":"CSV1","filename":"/Users/michaelklausner/Downloads/freifeld1.csv","appendNewline":true,"createDir":false,"overwriteFile":"false","encoding":"none","x":330,"y":440,"wires":[["1879c04a.b5b4"]]},{"id":"7db09b11.304bfc","type":"file","z":"7a1e5c34.0516d4","name":"CSV2","filename":"/Users/michaelklausner/Downloads/freifeld2.csv","appendNewline":true,"createDir":false,"overwriteFile":"false","encoding":"none","x":330,"y":500,"wires":[["784cdb95.11aad4"]]},{"id":"1879c04a.b5b4","type":"csv","z":"7a1e5c34.0516d4","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":480,"y":440,"wires":[["a38a811b.8a5258"]]},{"id":"784cdb95.11aad4","type":"csv","z":"7a1e5c34.0516d4","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":480,"y":500,"wires":[["a38a811b.8a5258"]]},{"id":"a38a811b.8a5258","type":"function","z":"7a1e5c34.0516d4","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":700,"y":460,"wires":[["20f412b9.d6fbde"]]}]
Hi, you need to have 1 of the files temporarily saved to a context variable or moved to another property
start->read csv2->convert to json->save to flow.csv2->read csv1->convert to json->function->output
or probably better:
start->read csv2->convert to json->change node (move msg.payload to msg.csv2)->read csv1->convert to json->function->output
You would be best using file in nodes, not file out, also all could be done in series.
e.g.
[{"id":"f1d9e743.9cae48","type":"file in","z":"7a1e5c34.0516d4","name":"csv1","filename":"/Users/michaelklausner/Downloads/freifeld1.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":320,"y":360,"wires":[["1879c04a.b5b4"]]},{"id":"1879c04a.b5b4","type":"csv","z":"7a1e5c34.0516d4","name":"","sep":";","hdrin":"","hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":450,"y":360,"wires":[["cf913061.602e7"]]},{"id":"d3309d3c.a4cf08","type":"inject","z":"7a1e5c34.0516d4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":360,"wires":[["f1d9e743.9cae48"]]},{"id":"cf913061.602e7","type":"change","z":"7a1e5c34.0516d4","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"csv1","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":360,"wires":[["896c6f2e.e8a528"]]},{"id":"896c6f2e.e8a528","type":"file in","z":"7a1e5c34.0516d4","name":"csv2","filename":"/Users/michaelklausner/Downloads/freifeld2.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":290,"y":440,"wires":[["784cdb95.11aad4"]]},{"id":"784cdb95.11aad4","type":"csv","z":"7a1e5c34.0516d4","name":"","sep":";","hdrin":"","hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":430,"y":440,"wires":[["5aa6343f.dfeeac"]]},{"id":"5aa6343f.dfeeac","type":"change","z":"7a1e5c34.0516d4","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$map(csv1, function($v,$i){\t$merge([$v, $$.payload[$i]])\t})","tot":"jsonata"},{"t":"delete","p":"columns","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":440,"wires":[["226d7090.172108"]]},{"id":"226d7090.172108","type":"csv","z":"7a1e5c34.0516d4","name":"","sep":";","hdrin":"","hdrout":"all","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":760,"y":440,"wires":[["ea13e930.669f08"]]},{"id":"ea13e930.669f08","type":"debug","z":"7a1e5c34.0516d4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":520,"y":520,"wires":[]}]
Hello!
Thank you very much for that, but the second value is not added.
It's already over.
col1; col2
Item; number2
A; 4
B; 6
D; 8
D; 3
But it should look like that.
Item; number1; number2
A; 1; 4
B; 2; 4
C; 1
D; 3; 8
If the csvs are not same items then search for matching keys
e.g.
$map(csv1, function($v,$i){
$merge([$v, $$.payload[Item = $v.Item]])
})
here is testing flow, as i have no files i created cvs's with template nodes.
[{"id":"bdbf843d.9a2268","type":"inject","z":"c74669a0.6a34f8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":4020,"wires":[["e4bc4684.bce58"]]},{"id":"e4bc4684.bce58","type":"template","z":"c74669a0.6a34f8","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Item;number1\nA;1\nB;2\nC;1\nD;3","output":"str","x":150,"y":4080,"wires":[["8e632ff3.bd8538"]]},{"id":"8e632ff3.bd8538","type":"csv","z":"c74669a0.6a34f8","name":"","sep":";","hdrin":true,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":290,"y":4080,"wires":[["521fcad8.08ee6c"]]},{"id":"521fcad8.08ee6c","type":"change","z":"c74669a0.6a34f8","name":"","rules":[{"t":"set","p":"csv1","pt":"msg","to":"payload","tot":"msg"},{"t":"delete","p":"columns","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":470,"y":4060,"wires":[["84e5cd7c.38bb2"]]},{"id":"84e5cd7c.38bb2","type":"template","z":"c74669a0.6a34f8","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"Item;number2\nA;4\nB;6\nD;8","output":"str","x":150,"y":4120,"wires":[["252c9dc1.b8e952"]]},{"id":"252c9dc1.b8e952","type":"csv","z":"c74669a0.6a34f8","name":"","sep":";","hdrin":true,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":310,"y":4120,"wires":[["4fe5e194.63325"]]},{"id":"4fe5e194.63325","type":"change","z":"c74669a0.6a34f8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$map(csv1, function($v,$i){\t$merge([$v, $$.payload[Item = $v.Item]])\t})\t","tot":"jsonata"},{"t":"delete","p":"columns","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":4120,"wires":[["cd4b20af.9f15c8"]]},{"id":"cd4b20af.9f15c8","type":"csv","z":"c74669a0.6a34f8","name":"","sep":";","hdrin":"","hdrout":"all","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":730,"y":4120,"wires":[["ed65e41a.ce54a"]]},{"id":"ed65e41a.ce54a","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":680,"y":4020,"wires":[]}]
and if you want it dynamic that joins on first column then
$map(csv1, function($v,$i){
$merge([$v, $$.payload[$.*[0] = $v.*[0] ]])
})
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
