Merge more than 3 .csv files

I have a main.csv file with several lines (for now we can assume 3 lines - first one with the titles and 2 with the data), each data line contains some data + reference to another csv file with other details. I want to read main.csv file line by line, get the file name from each line, get details from another csv file and create unique JSON object for each line of the main.csv.

I have managed to create separate objects for each line of the main.csv, but when I merge them I get first JSON object without details nested object, and the second one with both details object. My flow is here:

[{"id":"37206cf9.513154","type":"inject","z":"130a7976.464137","name":"start","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":80,"wires":[["c1e08c1e.09e96"]]},{"id":"c1e08c1e.09e96","type":"file in","z":"130a7976.464137","name":"","filename":"/home/ros_test/main.csv","format":"lines","chunk":false,"sendError":false,"encoding":"utf8","x":170,"y":160,"wires":[["cce2b9f5.cf50e"]]},{"id":"cce2b9f5.cf50e","type":"csv","z":"130a7976.464137","name":"","sep":";","hdrin":true,"hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":350,"y":120,"wires":[["175217cc.5791f"]]},{"id":"175217cc.5791f","type":"function","z":"130a7976.464137","name":"","func":"var temp = msg.payload;\nvar jsonObj = {};\njsonObj.group = temp.Gruppo;\njsonObj.unit = temp.Unit;\njsonObj.name = temp.Nome;\njsonObj.pointInd = temp.IndicePunto;\njsonObj.point = temp.Punto;\njsonObj.date = temp.Data_Ora;\nvar nestedJsonObj = {};\nvar nesNestObj = {};\nnestedJsonObj.RMedia =  temp.RMedia;\nnestedJsonObj.IMedia = temp.IMedia;\nnestedJsonObj.Energia = temp.Energia;\nnestedJsonObj.Tempo = temp.Tempo;\nnestedJsonObj.punti = temp.punti;\nnestedJsonObj.Ravv = temp.Ravv;\nnestedJsonObj.ResistenzaIn = temp.ResistenzaIn;\nnestedJsonObj.ResistenzaF = temp.ResistenzaF;\nnestedJsonObj.ResistenzaMax = temp.ResistenzaMax;\nnestedJsonObj.ResistenzaMin = temp.ResistenzaMin;\nnestedJsonObj.IstDiMax = temp.IstDiMax;\nnestedJsonObj.TDiSalita = temp.TDiSalita;\nnestedJsonObj.IndCresc = temp.IndCresc;\nnestedJsonObj.TDiDisc = temp.TDiDisc;\nnestedJsonObj.IndDecr = temp.IndDecr;\nnestedJsonObj.DiffResMinMax = temp.DiffResMinMax;\nnestedJsonObj.DiffResMaxEnd = temp.DiffResMaxEnd;\nnestedJsonObj.IndSpruz = temp.IndSpruz;\njsonObj.message = nestedJsonObj;\n\nvar file_name=temp.NomeFile;\nconsole.log(\"TEMP =\" + JSON.stringify(jsonObj));\nmsg.payload = file_name;\nvar msg1 = {payload:jsonObj,topic:\"main\"};\n//var msg1 = jsonObj;\n\nreturn [msg,msg1];\n\n","outputs":2,"noerr":0,"initialize":"","finalize":"","x":480,"y":160,"wires":[["f557afc2.257818"],["1c3a587a.e1fff8"]]},{"id":"f557afc2.257818","type":"change","z":"130a7976.464137","name":"point details filename","rules":[{"t":"set","p":"filename","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":600,"y":60,"wires":[["5b915b21.4b0ae4"]]},{"id":"1c3a587a.e1fff8","type":"join","z":"130a7976.464137","name":"json merge","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"{}","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":750,"y":180,"wires":[["6787cd8b.97ee6c"]]},{"id":"5b915b21.4b0ae4","type":"file in","z":"130a7976.464137","name":"read file from FS","filename":"","format":"lines","chunk":false,"sendError":false,"encoding":"utf8","x":810,"y":60,"wires":[["7520e51d.f51fd4"]]},{"id":"6787cd8b.97ee6c","type":"debug","z":"130a7976.464137","name":"TEST2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":960,"y":220,"wires":[]},{"id":"7520e51d.f51fd4","type":"csv","z":"130a7976.464137","name":"transform csv to json","sep":";","hdrin":true,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":1040,"y":60,"wires":[["bf9f2157.030ee"]]},{"id":"bf9f2157.030ee","type":"change","z":"130a7976.464137","name":"set topic","rules":[{"t":"set","p":"topic","pt":"msg","to":"details","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":1240,"y":60,"wires":[["1c3a587a.e1fff8"]]}]

main.csv example

Gruppo;Unit;Nome;NomeFile
5;1;PAZ50WB1;tbl_log_res_1420745212_0.csv
5;1;PAZ50WB1;tbl_log_res_1420763227_1.csv

tbl_log_res_1420745212_0.csv example

Corrente;Resistenza;Tensione;Potenza
0,717514;3987,835693;2,861328;2,053043
1,507996;1868,296387;2,817383;4,248601
2,237671;1197,970703;2,680664;5,998444

Expected result should be two messages, formed like this:

{
"group": 5,
"unit": 1,
"name": "PAZ50WB1",
"details":[{ "Corrente":"0.717514",
        "Resistenza":"3987.835693",
        "Tensione":"2.861328",
        "Potenza":"2.053043"
    },
    {
        "Corrente":"1.507996",
        "Resistenza":"31868.296387",
        "Tensione":"2.817383",
        "Potenza":"4.248601"
    },
    {
        "Corrente":"2.237671",
        "Resistenza":"1197.970703",
        "Tensione":"2.680664",
        "Potenza":"5.998444"
    }
    ]}

I don't know your file paths so you would need to edit the inject input and manipulate the filename after first change node to get the correct path, But something like below should work

[{"id":"efe43366.41f718","type":"inject","z":"9b3f9f31.c45298","name":"","props":[{"p":"filename","v":"main.csv","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":70,"y":160,"wires":[["feadc59e.a748d8","ba0749bc.79c58"]]},{"id":"ba0749bc.79c58","type":"file in","z":"9b3f9f31.c45298","name":"","filename":"","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":270,"y":100,"wires":[["2d03f86.2a6fd08"]]},{"id":"2d03f86.2a6fd08","type":"csv","z":"9b3f9f31.c45298","name":"","sep":";","hdrin":true,"hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":440,"y":100,"wires":[["423d7e45.f3366"]]},{"id":"423d7e45.f3366","type":"change","z":"9b3f9f31.c45298","name":"","rules":[{"t":"move","p":"payload.NomeFile","pt":"msg","to":"filename","tot":"msg"},{"t":"move","p":"payload","pt":"msg","to":"hold","tot":"msg"},{"t":"delete","p":"parts","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":100,"wires":[["868aabc6.73e0c8","37cf4767.f714a8"]]},{"id":"37cf4767.f714a8","type":"file in","z":"9b3f9f31.c45298","name":"","filename":"","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":270,"y":160,"wires":[["db15d265.24806"]]},{"id":"db15d265.24806","type":"csv","z":"9b3f9f31.c45298","name":"","sep":";","hdrin":true,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":450,"y":160,"wires":[["16f2ef9c.1e7f2"]]},{"id":"16f2ef9c.1e7f2","type":"change","z":"9b3f9f31.c45298","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"hold.details","tot":"msg"},{"t":"move","p":"hold","pt":"msg","to":"payload","tot":"msg"},{"t":"delete","p":"filename","pt":"msg"},{"t":"delete","p":"columns","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":160,"wires":[["58b5f9a0.5dcab"]]},{"id":"58b5f9a0.5dcab","type":"debug","z":"9b3f9f31.c45298","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":790,"y":160,"wires":[]}]

1 Like

Thank you sooo much! work like a sharm!!!!

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