Can we setup another separator in CSV generator?

Questions
1

Hi, I don’t know if the topic has already been covered but I wanted to know if in the Spreadsheets node you can choose a separator other than the comma when creating a CSV file, I’m trying to use the semicolon as a separator.

Thanks for the answers! :slight_smile:

2

Hey @RobinL!

Welcome to the community :slightly_smiling_face:

I don’t have a lot of experience with CSV files. But on a quick search, I learned that we can use semi-colon as a separator. Hence, the node will be able to create a CSV file with that separator. If you’re not getting the desired output, can you please share your workflow? This will help me replicate your issue and give a better solution.

3

Hey @harshil1712 ,
So i just take and Set some data in a Json with an HttpRequest and Set nodes. I put the data in a Spreadsheet node to generate a CSV and send it to a FTP server. When i get the .csv file and open it in a text editor, the separator are comas and i want semicolon because the .csv file will be use by a software that need semicolon to works.

4

This is yet sadly not possible with the spreadsheet node. However, you can do something like the example below.

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "operation": "toFile",
        "fileFormat": "csv",
        "options": {}
      },
      "name": "Spreadsheet File",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        750,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json : {name: '12', age: 12}\n  },\n    {\n    json: {name: '2' ,age:12}\n  }\n]"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        500,
        300
      ],
      "notesInFlow": true,
      "notes": "Mockup JSON"
    },
    {
      "parameters": {
        "functionCode": "const base64Data = items[0].binary.data.data\n\nlet data = Buffer.from(base64Data,'base64').toString()\n\ndata = data.replace(/,/g, ';')\n\nconst newBinaryData = items[0].binary.data\n\nnewBinaryData.data = Buffer.from(data).toString('base64');\n\nreturn [\n  {\n    json: {},\n    binary: {\n      data: newBinaryData\n    }\n  }\n]\n\n"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1000,
        300
      ],
      "notesInFlow": true,
      "notes": "Replace , with ;"
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Spreadsheet File": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Spreadsheet File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
5

I just tested with a file that uses comma , and one that uses semicolon ; and both files got read without a problem. So it seems to be doing a quite good job automatically. Did it not work for you @RobinL?

6

@jan I think he wants to write (create), not read.

At first glance, we could try implementing it with sheet_to_csv() using the FS (file separator) option instead of write().

7

Ah, that should then also be easy. We can simply add the option “FS”:

Should take 2 min to implement.

8

OK, I take that back. Just looked at the methods we use in the node. It is different from the ones mentioned there and would so be definitely a little bit more work. Sorry.

9

Ahh I had a look today cuz also thought it was very simple and realized the same.

10

Hello, thank you for investigating my problem. :slight_smile:

No problem guys, I think it could be a good feature if n8n users could choose the separator in an option when you choose to generate CSV with the Spreadsheet node.

4 Likes