# Merging 2 csv LEFT JOIN

**URL:** <https://discourse.nodered.org/t/merging-2-csv-left-join/46663>\
**Category:** General\
**Created:** [3 June 2021 18:49 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663 "2021-06-03T18:49:31Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![madmax](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@madmax](https://discourse.nodered.org/u/madmax)\
**Post date:** [3 June 2021 18:49 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/1 "2021-06-03T18:49:31Z")

</div>

Hello!

I would like to merge two CSVs.  
With SQL, do this via a LEFT JOIN, is that also possible in node red?

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2021 19:17 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/2 "2021-06-03T19:17:28Z")

</div>

Kind of. Have a try with [node-red-contrib-alasql (node) - Node-RED (nodered.org)](https://flows.nodered.org/node/node-red-contrib-alasql) and see if that helps.

---

<div class="post-metadata">

**Author:** ![madmax](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@madmax](https://discourse.nodered.org/u/madmax)\
**Post date:** [3 June 2021 19:57 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/3 "2021-06-03T19:57:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [3 June 2021 20:52 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/4 "2021-06-03T20:52:58Z")

</div>

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.

---

<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:** [3 June 2021 22:31 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/5 "2021-06-03T22:31:02Z")

</div>

You could also covert to objects then merge the objects with jsonata expression in a change node

```auto
$map($.csv1, function($v,$i){	$merge([$v, $$.csv2[$i]])	})

```

Then convert back to a csv, but delete the msg.column property first.

---

<div class="post-metadata">

**Author:** ![madmax](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@madmax](https://discourse.nodered.org/u/madmax)\
**Post date:** [4 June 2021 13:13 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/6 "2021-06-04T13:13:30Z")

</div>

> [@TotallyInformation](#):
>
> 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.

Hello!

One file is 20000 lines and the other 300 lines.

---

<div class="post-metadata">

**Author:** ![madmax](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@madmax](https://discourse.nodered.org/u/madmax)\
**Post date:** [4 June 2021 13:17 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/7 "2021-06-04T13:17:02Z")

</div>

> [@E1cid](#):
>
> You could also covert to objects then merge the objects with jsonata expression in a change node
> 
> ```auto
> $map($.csv1, function($v,$i){	$merge([$v, $.csv2[$i]])	})
> 
> ```
> 
> Then convert back to a csv, but delete the msg.column property first.

Hello!

Thank you for the tip.

and how does the flow have to look then?

Is it correct that way?

 ![Bildschirmfoto 2021-06-04 um 15.16.23](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/5/b5ba199ab136852d6e5ec6d1430faba2aea86b44.png)

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

```

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [4 June 2021 13:37 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/8 "2021-06-04T13:37:09Z")

</div>

Hi, you need to have 1 of the files temporarily saved to a context variable or moved to another property

```auto
start->read csv2->convert to json->save to flow.csv2->read csv1->convert to json->function->output

```

or probably better:

```auto
start->read csv2->convert to json->change node (move msg.payload to msg.csv2)->read csv1->convert to json->function->output

```

---

<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:** [4 June 2021 14:07 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/9 "2021-06-04T14:07:45Z")

</div>

You would be best using file in nodes, not file out, also all could be done in series.  
e.g.

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

```

---

<div class="post-metadata">

**Author:** ![madmax](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@madmax](https://discourse.nodered.org/u/madmax)\
**Post date:** [4 June 2021 15:43 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/10 "2021-06-04T15:43:42Z")

</div>

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

---

<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:** [4 June 2021 16:37 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/11 "2021-06-04T16:37:33Z")

</div>

If the csvs are not same items then search for matching keys  
e.g.

```auto
$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.

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

```auto
$map(csv1, function($v,$i){
$merge([$v, $$.payload[$.*[0] = $v.*[0] ]])
})

```

---

<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:** [3 August 2021 16:38 UTC](https://discourse.nodered.org/t/merging-2-csv-left-join/46663/12 "2021-08-03T16:38:32Z")

</div>

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