Any "change node" for JSON object

General
1

Hi, I have tried to use "change node" to replace the comma (i.e. ',' to '|' for example) but it does not work with json object. If I change the json object to json string using "json node", the string creates many comma. Is there a "change node" for json object? Thx

2

Thats not how it works. A JS object is not a string.

Show us what you have and what you require and we can show you the right way.

( Context is king :slight_smile: )

3

Thx thx, here is the flow.

comma
comma1431×429 122 KB

If there is a comma in excel, I want to change it to another character, or else, it will messy up the csv file.
My whole project is just to import an excel file to mysql. However, the excel format is not standard (e.g. merge cell), so, I covert it to csv for better manipulation. Thx

excel

[{"id":"e7684686.52dbb8","type":"tab","label":"Flow 7","disabled":false,"info":""},{"id":"b5d49b5d.076e88","type":"file in","z":"e7684686.52dbb8","name":"read a spreadsheet file as binary buffer","filename":"","format":"","chunk":false,"sendError":false,"x":550,"y":200,"wires":[["4aac0e30.d7492"]]},{"id":"4aac0e30.d7492","type":"book","z":"e7684686.52dbb8","name":"","x":450,"y":240,"wires":[["3f16781a.bf00a8"]]},{"id":"b022c09f.0431f","type":"sheet-to-json","z":"e7684686.52dbb8","name":"","raw":"false","range":"","header":"default","blankrows":false,"x":470,"y":320,"wires":[["2c5d6cb0.6759e4"]]},{"id":"cbb64a62.a63cf8","type":"file","z":"e7684686.52dbb8","name":"","filename":"D:\\temp\\excel\\test.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":500,"y":400,"wires":[[]]},{"id":"2c5d6cb0.6759e4","type":"json-2-csv","z":"e7684686.52dbb8","name":"","x":500,"y":360,"wires":[["cbb64a62.a63cf8"]]},{"id":"3f16781a.bf00a8","type":"sheet","z":"e7684686.52dbb8","name":"","sheetName":"Sheet1","x":450,"y":280,"wires":[["b022c09f.0431f"]]},{"id":"4287ce47.17a65","type":"function","z":"e7684686.52dbb8","name":"example","func":"var Newmsg = msg;\nNewmsg.filename=msg.payload;\nreturn Newmsg;","outputs":1,"noerr":0,"x":460,"y":160,"wires":[["b5d49b5d.076e88"]]},{"id":"2d213257.2a3b0e","type":"inject","z":"e7684686.52dbb8","name":"example","topic":"","payload":"D:\\temp\\excel\\test.xlsx","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":"","x":320,"y":160,"wires":[["4287ce47.17a65"]]}]
4

Hi firstly, please wrap flow and code in triple backticks ``` (the forum messes with code and it becomes unusable see how to post code)

Why do you say that? As you can see in your debug output, the CSV is correctly formatted (i.e. the Name "Novak, John" is correctly surrounded with quotes because it has a comma.)

Secondly, why convert to CSV at all? As you have the values in a JS object (the first debug output) - you could simply pass that data straight to MySQL e.g...
image

Lastly - if you REALLY REALLY insist on changing a character in the name property of an the row object inside your array so you need to address each one - but - there could be an unknown amount of rows (i.e. the array size will be dynamic based on the spreadsheet content) - you will need to either loop through each row and modify each item.

This might be possible in JSONata but I dont know JSONata too well and if its a large array, standard JS loop will be much faster.

try adding a function node between the "sheet to json" and the "json to csv" node with the below...

//loop through each row 
// replace  ,   with   |   
// in the  Name  property 
// of each  object  in the  array
for (let i = 0; i < msg.payload.length; i++) {
    msg.payload[i].Name = msg.payload[i].Name.replace(",","|")
}
return msg;
5

Thx, it is working. Cheers.
My excel has merge cells which gives me wrong names of the column. (e.g. _1: "Novak, John"), That's why I use csv.

6

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