Transform query result in csv file

Hi everybody,
I would like to know if it is possible to transform the result of a postgres query like this one:
order_line_item_id sku qte status
9778452 XXX 1 CANCEL
9788453 XXXX 2 SHIPPED

in a csv file like this :
order_line_item_id/1;sku/1;qte/1;status/1;order_line_item_id/2;sku/2;qte/2;status/2
9778452;XXX;1;CANCEL;9788453;XXXX;2;SHIPPED

thanks

Hey @fporta!

Welcome to the community :sparkling_heart:

You can transform the result returned by the query into a CSV using the Spreadsheet File node.

Thanks,
I have read this documentation but I am a real noob with n8n and I confess I did not understand how to go from 2 rows of sql answers to one row in my csv file

To do that you would need a ‘set’ node to define all the columns that you want in your CSV file. You could then fill in the values by referencing specific items in the output. Perhaps @harshil1712 can provide more details.

Hey @fporta!

Here’s an example workflow that might help:

{
  "nodes": [
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "name",
              "value": "={{$json[\"name\"]}}"
            },
            {
              "name": "username",
              "value": "={{$json[\"username\"]}}"
            },
            {
              "name": "email",
              "value": "={{$json[\"email\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        650,
        290
      ]
    },
    {
      "parameters": {
        "url": "https://jsonplaceholder.typicode.com/users",
        "options": {
          "splitIntoItems": true
        }
      },
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        450,
        290
      ]
    },
    {
      "parameters": {
        "operation": "toFile",
        "fileFormat": "csv",
        "options": {}
      },
      "name": "Spreadsheet File",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        850,
        290
      ]
    }
  ],
  "connections": {
    "Set": {
      "main": [
        [
          {
            "node": "Spreadsheet File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

You can copy and paste the above code in the Editor UI. I am using the HTTP Request node to fetch dummy data from an API, in your case you will be using the Postgres node. In the Set node, I configure the values that I want to add in the CSV.

I hope this helps :slight_smile:

OK I understand what you are doing. In absolute terms it works, but I left out one important detail, the result of the query never has the same number of rows. The set node remains a static mode while I need a dynamic mode

@fporta, do you mean columns, or do you mean rows? If it’s the rows, then it is completely fine. The Set node will process all the incoming rows and the Spreadsheet File node will add all the rows to the CSV.

I realise that I have not been very clear in my explanations. My sql returns from 1 to n rows. The common point of these rows is the value of the order_id column (see screen)

I would like to achieve this:
order_id,order_line_item_id1,sku1,quantity1,status1,order_line_item_id2,sku2,quantity2,status2,order_line_item_id3,sku3,quantity3,status3
123456,978452,XXXXXXXX,2,CANCEL,978453,XXXXXXXX,2,SHIPPED,978451,1,REFUND

the number of columns is variable depending on the number of sql returns…

Thanks for your time

Thanks for giving more details. Can you share the output here, so that I can use that to generate a function that you can use?

ok

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -1850,
        -10
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "select marketplace, order_id, marketplace_id from commande_entete",
        "additionalFields": {
          "queryParams": "status='62' AND retour_info_site = 'N'"
        }
      },
      "name": "Postgres",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        -1540,
        -10
      ],
      "credentials": {
        "postgres": "Postgres_Diatly"
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "select   order_id, marketplace_id, order_line_item_id, sku,  quantity, status from commande_ligne\n",
        "additionalFields": {
          "queryParams": "=marketplace= {{$node[\"Postgres\"].json[\"marketplace\"]}} and order_id = {{$node[\"Postgres\"].json[\"order_id\"]}} and marketplace_id = {{$node[\"Postgres\"].json[\"marketplace_id\"]}}"
        }
      },
      "name": "Postgres1",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        -1280,
        -10
      ],
      "credentials": {
        "postgres": "Postgres_Diatly"
      }
    },
    {
      "parameters": {
        "operation": "toFile",
        "fileFormat": "csv",
        "options": {}
      },
      "name": "Spreadsheet File",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        -1060,
        -10
      ]
    },
    {
      "parameters": {
        "fileName": "/usr/local/ETL/CRP/Travaux/Diatly/output/shipment2.csv"
      },
      "name": "Write Binary File1",
      "type": "n8n-nodes-base.writeBinaryFile",
      "typeVersion": 1,
      "position": [
        -810,
        -10
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Postgres",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres": {
      "main": [
        [
          {
            "node": "Postgres1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres1": {
      "main": [
        [
          {
            "node": "Spreadsheet File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Spreadsheet File": {
      "main": [
        [
          {
            "node": "Write Binary File1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Thanks a lot

A function node as shown below should do it:

const response = {};

for (const [index, item] of items.entries()) {
    for (const key of Object.keys(item.json)) {
      if (key === 'order_id') {
        response[key] = item.json[key];
      } else {
        response[`${key}${index + 1}`] = item.json[key]
      }
    }
}

return [
  {
    json: response
  }
]
Example workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -260,
        210
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      order_id: 1,\n      order_line_item_id: 2,\n      sky: 'abc',\n      quantity: 20,\n      status: 'active'\n    }\n  },\n    {\n    json: {\n      order_id: 1,\n      order_line_item_id: 2,\n      sky: 'def',\n      quantity: 10,\n      status: 'inactive'\n    }\n  }\n]\n\n"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        30,
        210
      ],
      "notesInFlow": true,
      "notes": "Mockup data"
    },
    {
      "parameters": {
        "functionCode": "const response = {};\n\nfor (const [index, item] of items.entries()) {\n    for (const key of Object.keys(item.json)) {\n      if (key === 'order_id') {\n        response[key] = item.json[key];\n      } else {\n        response[`${key}${index + 1}`] = item.json[key]\n      }\n    }\n}\n\nreturn [\n  {\n    json: response\n  }\n]\n"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        280,
        210
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

@RicardoE105
Your code works perfectly. Thank you very much for this lesson.
Thanks to all of you for your help

2 Likes