Transform query result in csv file

Questions
1

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

2

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.

3

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

4

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.

5

Hey @fporta!

Here’s an example workflow that might help:

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:

6

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

7

@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.

8

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)

Sélection_202
Sélection_2021532×496 38.6 KB

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

9

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?

10

ok

Thanks a lot

11

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
12

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