Quick and dirty CSV to Postgres import for DriveBC

index.js

var csv = require('csv');
var pg = require('pg');

var connectionString = 'postgres://tylor@localhost/drivebc';
var client = new pg.Client(connectionString);
client.connect();

client.query("DROP TABLE incidents");
client.query("CREATE TABLE incidents(import_id serial primary key, \"id\" varchar(255), cause varchar(255), district varchar(255), state varchar(255), severity varchar(255), localupdatetime varchar(255), advisorymessage text, isbidirectional varchar(255), trafficpattern varchar(255), head_latitude varchar(255), head_longitude varchar(255), tail_latitude varchar(255), tail_longitude varchar(255), route varchar(255), type varchar(16))");

var filenames = [ 'drivebc_events_hist_2006.csv'
                , 'drivebc_events_hist_2007.csv'
                , 'drivebc_events_hist_2008.csv'
                , 'drivebc_events_hist_2009.csv'
                , 'drivebc_events_hist_2010.csv'
                , 'drivebc_events_hist_2011.csv'
                ];
var testDone = filenames.length;

var id = 0;

for (var i = 0; i < filenames.length; i++) {
  console.log('Spin up: ' + filenames[i]);
  csv()
    .fromPath(__dirname + '/' + filenames[i])
    .on('data',function(data,index){
        //console.log('#' + index + ' ' + JSON.stringify(data));
        if (index % 1000 === 0) {
          console.log(index);
        }
        client.query("INSERT INTO incidents(\"id\", cause, district, state, severity, localupdatetime, advisorymessage, isbidirectional, trafficpattern, head_latitude, head_longitude, tail_latitude, tail_longitude, route, type) values($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)", data, function (err, result) {
          console.log('Done: ' + index);
          if (err !== null) {
            console.log(err);
          }
        });
    })
    .on('end',function(count){
        testDone--;
        if (testDone === 0) {
          //client.end(); // Exit cleanly.
        }
    })
    .on('error',function(error){
        console.log(error.message);
        client.end();
    });
}

package.json

{
  "name": "drivebc-import",
  "version": "0.0.0",
  "description": "Import all DriveBC events to PostgreSQL",
  "main": "index.js",
  "author": "Tylor Sherman",
  "license": "MIT",
  "dependencies": {
    "csv": "0.0.19",
    "pg": "~0.8.4"
  }
}