Parse cvs file that number of raw different in some column

[okircali]

Hello, I have a cvs file that have more than one column in a one raw. I want to get the extra columns as an array. However, I just get 1st data of the extra columns in the output of the csv node.

For instance, In the below example I want to get 3 objects , that the object 1 has an array size of 4 , object 2 has array size of 3...

Do you have a suggestion?

[Colin]

Show us an example of what is in the csv file please.
Copy/paste some of the data, not screenshot.

[okircali]

Here is one example

1	ŞRJ/2311	GIOev - WOW İstanbul Hotel	HALKA_ACIK	GIOEV	GİOEV ŞARJ İSTASYONLARI İŞLETMELERİ ANONİM ŞİRKETİ	Yeşilköy Mahallesi Atatürk Caddesi No:15/1 Bakırköy / İSTANBUL	Soket No	Soket Tipi	Soket Türü	Soket Gücü (kW)
							SKT/4245	DC	DC_CCS	300
							SKT/4246	DC	DC_CCS	300
							SKT/4250	AC	AC_TYPE2	22
2	ŞRJ/2312	GIOev - Antalya - Manavgat	HALKA_ACIK	GIOEV	GİOEV ŞARJ İSTASYONLARI İŞLETMELERİ ANONİM ŞİRKETİ	Ilıca Mahallesi Antalya Bulvarı Caddesi No:56 Manavgat / ANTALYA	Soket No	Soket Tipi	Soket Türü	Soket Gücü (kW)
							SKT/4247	DC	DC_CCS	300
							SKT/4248	DC	DC_CCS	300

[okircali]

It does not pasted in a good format. Here is how it seen on excel;

[Colin]

We need to see it as csv data. So numbers and text interposed with commas.

[okircali]

I cannot add it as csv here since It is not allowed. Only below formats are available. Let me know How I can send to you.

[Colin]

Open the file with a text viewer and copy/paste the first few lines here.

[okircali]

1,ŞRJ/2311,GIOev - WOW İstanbul Hotel,HALKA_ACIK,GIOEV,GİOEV ŞARJ İSTASYONLARI İŞLETMELERİ ANONİM ŞİRKETİ,Yeşilköy Mahallesi Atatürk Caddesi  No:15/1 Bakırköy / İSTANBUL,Soket No,Soket Tipi,Soket Türü,Soket Gücü (kW)
,,,,,,,SKT/4245,DC,DC_CCS,300
,,,,,,,SKT/4246,DC,DC_CCS,300
,,,,,,,SKT/4250,AC,AC_TYPE2,22
2,ŞRJ/2312,GIOev - Antalya - Manavgat,HALKA_ACIK,GIOEV,GİOEV ŞARJ İSTASYONLARI İŞLETMELERİ ANONİM ŞİRKETİ,Ilıca Mahallesi Antalya Bulvarı Caddesi  No:56 Manavgat / ANTALYA,Soket No,Soket Tipi,Soket Türü,Soket Gücü (kW)
,,,,,,,SKT/4247,DC,DC_CCS,300
,,,,,,,SKT/4248,DC,DC_CCS,300

Admin edit. Added backticks around text for easier copying.

[Colin]

Feeding that into a csv node gives you all the data as an array of objects, then you can adjust the data to get what you want.
The flow below gives

[{"id":"4d7b7feca8506049","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"\"1,ŞRJ/2311,GIOev - WOW İstanbul Hotel,HALKA_ACIK,GIOEV,GİOEV ŞARJ İSTASYONLARI İŞLETMELERİ ANONİM ŞİRKETİ,Yeşilköy Mahallesi Atatürk Caddesi  No:15/1 Bakırköy / İSTANBUL,Soket No,Soket Tipi,Soket Türü,Soket Gücü (kW)\\n,,,,,,,SKT/4245,DC,DC_CCS,300\\n,,,,,,,SKT/4246,DC,DC_CCS,300\\n,,,,,,,SKT/4250,AC,AC_TYPE2,22 2,ŞRJ/2312,GIOev - Antalya - Manavgat,HALKA_ACIK,GIOEV,GİOEV ŞARJ İSTASYONLARI İŞLETMELERİ ANONİM ŞİRKETİ,Ilıca Mahallesi Antalya Bulvarı Caddesi  No:56 Manavgat / ANTALYA,Soket No,Soket Tipi,Soket Türü,Soket Gücü (kW)\\n,,,,,,,SKT/4247,DC,DC_CCS,300\\n,,,,,,,SKT/4248,DC,DC_CCS,300\"","payloadType":"json","x":190,"y":2240,"wires":[["08c9647e62873ea7"]]},{"id":"52da5cd3580a619a","type":"debug","z":"bdd7be38.d3b55","name":"debug 101","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":510,"y":2240,"wires":[]},{"id":"08c9647e62873ea7","type":"csv","z":"bdd7be38.d3b55","name":"","sep":",","hdrin":"","hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":true,"include_null_values":true,"x":340,"y":2240,"wires":[["52da5cd3580a619a"]]}]

[okircali]

Can we convert it as 2 raw --> 2 object, 2 object have the last 3 raws as array?

[Colin]

For the example posted, show us exactly what form you want the data. Don't use words, show us the what the data should look like. Only then will we know we have correctly interpreted your requirement.

[okircali]

Below form is what I want;

[TotallyInformation]

No, you cannot do that directly. You need to get it into the JSON format first and then reshape it.

[E1cid]

I would just use a function to do the complete thing at this point.

Example

[{"id":"4d7b7feca8506049","type":"inject","z":"bf569dbfc3bc07ed","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":520,"wires":[["781cf45833955428"]]},{"id":"781cf45833955428","type":"template","z":"bf569dbfc3bc07ed","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"1,ŞRJ/2311,GIOev - WOW İstanbul Hotel,HALKA_ACIK,GIOEV,GİOEV ŞARJ İSTASYONLARI İŞLETMELERİ ANONİM ŞİRKETİ,Yeşilköy Mahallesi Atatürk Caddesi  No:15/1 Bakırköy / İSTANBUL,Soket No,Soket Tipi,Soket Türü,Soket Gücü (kW)\n,,,,,,,SKT/4245,DC,DC_CCS,300\n,,,,,,,SKT/4246,DC,DC_CCS,300\n,,,,,,,SKT/4250,AC,AC_TYPE2,22\n2,ŞRJ/2312,GIOev - Antalya - Manavgat,HALKA_ACIK,GIOEV,GİOEV ŞARJ İSTASYONLARI İŞLETMELERİ ANONİM ŞİRKETİ,Ilıca Mahallesi Antalya Bulvarı Caddesi  No:56 Manavgat / ANTALYA,Soket No,Soket Tipi,Soket Türü,Soket Gücü (kW)\n,,,,,,,SKT/4247,DC,DC_CCS,300\n,,,,,,,SKT/4248,DC,DC_CCS,300","output":"str","x":160,"y":560,"wires":[["4003aa5b7e7c1745"]]},{"id":"4003aa5b7e7c1745","type":"function","z":"bf569dbfc3bc07ed","name":"function 64","func":"const output = [];\nlet rindex = 0;\nconst rows = msg.payload.split(\"\\n\");\nrows.forEach(row =>{\n   let parts = row.split(\",\");\n   if (parts[0] != \"\"){\n       output[rindex] = parts.slice(0,7);\n       output[rindex].push(parts.slice(7,11));\n    rindex++;\n   }else{\n       parts[10] = parseFloat(parts[10]);\n       output[rindex - 1].push(parts.slice(7,11));\n   }\n})\nmsg.payload = output;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":620,"wires":[["52da5cd3580a619a"]]},{"id":"52da5cd3580a619a","type":"debug","z":"bf569dbfc3bc07ed","name":"debug 101","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":510,"y":620,"wires":[]}]

[edit] Add Parse number.

[TotallyInformation]

Or something amusing with JSONata in a change node.

[okircali]

When I upload the excel file in cvs format, the read node brings me the two parts of 1 column seperately. How can I get them in one object so after seperate I will get the output as above then I can manage to adjust it as I want in a function node.

[TotallyInformation]

Would appear to imply that there is something wrong in the CSV file.

But without access to the source data, it is hard to know. Maybe look for a CSV checker online and make sure that the file is actually valid CSV as a starting point.