Merging 2 csv files in Node-RED/JavaScript

Hello there!
I'm looking for some help on how to solve some specific challenges I'm facing in a current project. I'm a bit stuck, so I was hoping someone more experienced than myself could give me some hints. :slight_smile:

I have 2 CSV files that each contains some columns with data and a timestamp. I have them imported into Node-Red and directed to a mySQL database- my current flow looks like this:

[{"id":"3992ae32.0275c2","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"6ab9153d.2b444c","type":"inject","z":"3992ae32.0275c2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"true","payloadType":"bool","x":230,"y":320,"wires":[["fc6fa5a1.44d9d8","f9399507.2ca6d8"]]},{"id":"c7106530.531a18","type":"mysql","z":"3992ae32.0275c2","mydb":"60cb07a4.7ce3a8","name":"SQL_Server","x":1090,"y":360,"wires":[[]]},{"id":"fcba8c85.3901a","type":"function","z":"3992ae32.0275c2","name":"GPS","func":"var data = msg.payload.split(\",\");\nvar rosbagtimestamp_in_miliseconds = data[0];\nrosbagtimestamp_in_miliseconds = rosbagtimestamp_in_miliseconds/1000000;\nvar latitude = data[1];\nvar longitude = data[2];\nvar altitude = data[3];\nvar relative_altitude = data[4];\nvar out = \"INSERT INTO gps(rosbagtimestamp_in_miliseconds,latitude,longitude,altitude,relative_altitude) VALUES('\"+rosbagtimestamp_in_miliseconds+\"','\"+latitude+\"','\"+longitude+\"','\"+altitude+\"','\"+relative_altitude+\"')\";\nmsg.topic = out;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":770,"y":280,"wires":[["c7106530.531a18","97e312b4.43de2"]]},{"id":"e883cb4c.c3c608","type":"function","z":"3992ae32.0275c2","name":"Spectrum","func":"var data = msg.payload.split(\",\");\nvar sweep = data[0];\nvar timestamp = data[1];\nvar peak_freq_mhz = data[2];\nvar peak_ampl_dbm = data[3];\nvar out = \"INSERT INTO spectrum(sweep,timestamp,peak_freq_mhz,peak_ampl_dbm) VALUES('\"+sweep+\"','\"+timestamp+\"','\"+peak_freq_mhz+\"','\"+peak_ampl_dbm+\"')\";\nmsg.topic = out;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":780,"y":360,"wires":[["c7106530.531a18","97e312b4.43de2"]]},{"id":"97e312b4.43de2","type":"debug","z":"3992ae32.0275c2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1090,"y":280,"wires":[]},{"id":"f9399507.2ca6d8","type":"file in","z":"3992ae32.0275c2","name":"Spectrum","filename":"C:\\Users\\Oscar Gregers\\Desktop\\Dataopsamlings projekt\\UCL QuadSAT spectrum.csv","format":"lines","chunk":false,"sendError":false,"encoding":"none","x":520,"y":360,"wires":[["e883cb4c.c3c608"]]},{"id":"fc6fa5a1.44d9d8","type":"file in","z":"3992ae32.0275c2","name":"GPS","filename":"C:\\Users\\Oscar Gregers\\Desktop\\Dataopsamlings projekt\\UCL QuadSAT GPS .csv","format":"lines","chunk":false,"sendError":false,"encoding":"none","x":510,"y":280,"wires":[["fcba8c85.3901a"]]},{"id":"60cb07a4.7ce3a8","type":"MySQLdatabase","name":"","host":"","port":"3306","db":"drone_data","tz":"","charset":"UTF8"}]

Problem statement:
Now, my next goal is to merge these 2 files together by timestamps, but comparing the files according to the timestamps, looks something like this sketch:

So my challenges is:

1. How do I merge the files together, resulting in the structure marked with green in the figure above. (something about comparing column 1 in file 1 & column 2 in file 2, but from there I'm lost).

2. How do I discard the rows as shown in the figure above.

If in need of more details, please let me know.
This is my first post ever, so please go easy on me, in case my description is poorly composed. :slight_smile:

Thanks in advance!

Hi and welcome.

You have provided a good explaination of what you want to do, but it would help if you provided example data.

Best to create a flow that has two inject or template nodes, with example data of csv files in each, as people will not have your csv files. A clear explaination of what is wanted and what is to be dropped from the csv's files will also help. The data onlt needs to be a sample not a full file.

Having looked at your flow it appears you are sending one line at a time, it would be diifficult to merge the files one line at a time, Best read data into a message then use javascript or JSONata to merge the objects. Then you can add to database

Hi E1cid,

Thank you for your answer! I found a big-brain friend that helped me solve this, so I am no longer in need of assistance on this matter.

Again, thank you for taking your time to consider my options :slight_smile:

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