how to import csv file into postgresql using node js?

Question

I am new to node js.

I have a csv file in my local system that I want to upload it local PostgreSQL Database using node js.

I am trying the following code:

var csv = require('csv-stream');
var request = require('request');
var fs = require('fs');

// All of these arguments are optional.
var options = {
    delimiter : '\t', // default is ,
    endLine : '\n', // default is \n,
// by default read the first line and use values found as columns 
   // columns : ['Settlement Ref No.', 'Order Type','Fulfilment Type','Seller SKU','wsn'],
    escapeChar : '"', // default is an empty string
    enclosedChar : '"' // default is an empty string
}

var csvStream = csv.createStream(options);
 fs.createReadStream('C:\\Users\\YAM\\Documents\\fk_starchi.csv').pipe(csvStream)
    .on('error',function(err){
        console.error(err);
    })
    .on('data',function(data){
        // outputs an object containing a set of key/value pair representing a line found in the csv file.
       // console.log(data);
    })
    .on('column',function(key,value){
        // outputs the column name associated with the value found
      // console.log('#' + key + ' = ' + value);
        console.log('# '   + value);

    })

Its reading data . now i want to import it on postgrsql database.

Where can I get a tutorial or any other help to do this.

Why you need node if postgres has a built-in function? Referer: http://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgres-table — vanadium23, May 07 '15 at 07:51
I also have to create a file dialog, that will not create in postgresql — , May 07 '15 at 07:53
May be you can write query `COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV;` without csv module? — vanadium23, May 07 '15 at 07:54
@RajatModi if i need the local path then what changes should i do? — , May 07 '15 at 08:15
@Shubhambatra It can not be as you're using request module and request should be called by http and https :) better you should use fs.createReadStream — Rajat Modi, May 07 '15 at 09:18
@RajatModi Thanks its reading data now but how i can store it on postgresql — , May 07 '15 at 09:37
use this it will work https://github.com/brianc/node-postgres — Rajat Modi, May 07 '15 at 10:11
The same kind of question: http://stackoverflow.com/questions/33129677/nodejs-promises-streams-processing-large-csv-files — vitaly-t, Oct 19 '15 at 21:22

Michael Cole · Answer 1 · 2015-05-07T10:07:29.563

I understand you want to import this cvs file into Postgres.

There's two steps. Reading the file. Writing the data.

1) Reading the file you've done with csv-stream. I don't quite understand what the column event does, but it looks like the 'data' event is where to start. So add your code there.

2) Writing the data.

There's two routes for this:

a) Quick and dirty. In the 'data' event, craft the SQL using strings, then run them with a thin library like node-postgres.

var sql = 'INSERT INTO table VALUES (' data.this + ',' + data.that + ',' + data.theotherthing + ');';

Check out this example for a structure to start. You're already familiar with streams, so you'll just need to manage the callbacks.

You're csv-stream will produce SQL statements faster than postgress will handle them, so you could run into 1000's of simultaneous requests. You might want to + the query strings together in batches, and/or use through2 to query, wait, then query.

The reason NOT to do this is someone could put a SQL injection into the CSV and trash your database.

b) The smart way to do this (especially if dealing with unknown CSV's) is to use an ORM like sequelize.

There isn't a copy/paste and done. A good place to start is reading their homepage.

how to import csv file into postgresql using node js?

1 Answers1