MySQL to Spreadsheets gives error?

tony · April 12, 2020, 2:31pm

I am trying to dump SQL table to a CSV File, but for some reason it does not seems to work. The spreadsheets will work correctly if the input are from function node, but not if its from MySQL Node.

The Flow:

Output of the MySQL:

Error from the Spreadsheets:

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        150
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=SELECT * FROM d_act_dpm_check_in;"
      },
      "name": "Get All Data",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 1,
      "position": [
        450,
        150
      ],
      "credentials": {
        "mySql": "Local (MySQL)"
      }
    },
    {
      "parameters": {
        "operation": "toFile",
        "options": {}
      },
      "name": "Spreadsheet File2",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        650,
        150
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Get All Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get All Data": {
      "main": [
        [
          {
            "node": "Spreadsheet File2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Did I do something wrong?

RicardoE105 · April 12, 2020, 3:40pm

Hey @tony,

This happens because the JSON the Mysql node is returning has a property with the value null. This seems to me like a bug. I will check that out. In the meantime, you can use a function node to map all the nulls to “nulls” on the Mysql node output. Check the example below.

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "operation": "toFile",
        "options": {}
      },
      "name": "Spreadsheet File",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        910,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      name: null,\n      lastName: 'espinoza'\n    }\n  },\n  \n    {\n    json: {\n      name: 'ricrdo',\n      lastName: null\n    }\n  }\n\n]"
      },
      "name": "Mysql query",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        460,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "for (const item of items) {\n  for (const key of Object.keys(item.json)) {\n    if (item.json[key] === null) {\n        item.json[key] = 'null'\n    }\n  }\n}\n\nreturn items;"
      },
      "name": "Map null to \"null\"",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        690,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Mysql query",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Mysql query": {
      "main": [
        [
          {
            "node": "Map null to \"null\"",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Map null to \"null\"": {
      "main": [
        [
          {
            "node": "Spreadsheet File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

tony · April 12, 2020, 3:59pm

Thanks! it work beautifully

RicardoE105 · April 12, 2020, 4:04pm

@tony just sent a fix. In the next version, the function node to map the nulls to “nulls” should not be necessary.

jan · April 12, 2020, 6:27pm

Fix got released with [email protected]

tony · April 13, 2020, 1:27am

Updated, and it fixed the problem. I have separate question regarding spreadsheets (xls output), at the output give me this error:

is the extension should not be .xls? or there are some mime setting that is wrong? do note, if I select “Yes”, it still will open correctly - but since this is sent via email it seems to be a problem that the recipient will not open the file if there are this error.

Thank you!