How to export in nodejs a csv file from nodejs with a PostgreSQL

Question

the nodejs file is on Heroku and I'm using PostgreSQL as a Database I would like to export a csv file from a view :

// My PostgreSQL query

var copyTo = require('pg-copy-streams').to; 
var csv = require('csv');
var fs = require('fs');

var stream =  client.query(copyTo('COPY (SELECT * FROM v_metric) TO
STDOUT WITH CSV HEADER DELIMITER as \'|\''));

// export in csv

csv().from(stream.pipe(process.stdout,{ end: false)).to(fs.createWriteStream('sample.csv'))

I dont have any result, sample.csv is empty.

any idea?

thank you in advance

myusuf · Accepted Answer · 2017-06-02 17:35:14Z

4

You can use pg-copy-streams npm module made specifically for this:

    let data = '', copyTo = require('pg-copy-streams').to;
    pool.connect(function(pgErr, client, done) {
      if(pgErr) {
        //handle error
        return;
      }
      var stream = client.query(copyTo(`COPY (${query}) TO STDOUT With CSV HEADER`));
      stream.on('data', chunk => {
        data += chunk;
      })
      stream.on('end', response => {
        console.log(data)
        done();
      });
      stream.on('error', err => {
        done();
      })
    })

Hope this helps.

answered Jun 2, 2017 at 17:35

myusuf

12.3k12 gold badges39 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aaron Silverman · Accepted Answer · 2014-02-19 15:10:42Z

2

The SQL Server's STDOUT will be different than your node processes's. You could try streaming the query results:

var query = client.query('SELECT * FROM v_metric');
query.on('row', function(row) {
    //handle the row here
});

Then in your handler if your data isn't complicated (i.e. no delimiters or double quotes) you could skip using csv and iterate over the columns to convert them into a string that you write to the write stream. May be easier to have the column names in an array which you can pass as the SQL (via joining with ', ') and iterate over in the handler, but you could also extract the column names using Object.keys(row).

UPDATE: Example based on your comment:

var columns = ['country_cd','product_name','product_lvel','month_year','metric_name','val'];
var ws = fs.createWriteStream('sample.csv');

var query = client.query('SELECT '+columns.join(', ')+' FROM users');
query.on('row', function(row) {
    var values = [];
    // process column values; if you need to do special formatting (i.e. dates) don't loop and instead handle each one specially
    columns.forEach(function(col) {
        values = row[col];
    });
    ws.write(values.join('| '));
});

query.on('end', function(result) {
    ws.close();
});

If you do want to use csv you can create a stream that you write to write data to in the handler and pip that to csv.

One other note, , is the default delimter so if you want to use something else, like |, you will need to specify that in the options.

edited Feb 19, 2014 at 15:10

answered Feb 19, 2014 at 13:58

Aaron Silverman

22.7k21 gold badges89 silver badges106 bronze badges

2 Comments

user1921103 Over a year ago

Aaron Silverman Over a year ago

Updated with example for the non-csv way (i.e. data doesn't have " or | in its values) based on your above comment.

Collectives™ on Stack Overflow

How to export in nodejs a csv file from nodejs with a PostgreSQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related