CSV Import with transformations
JavaScript
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
lib passing test...at last Sep 11, 2016
src passing test...at last Sep 11, 2016
test passing test...at last Sep 11, 2016
.babelrc add async await Sep 3, 2016
.editorconfig first commit Aug 31, 2016
.eslintrc.json give knex user rights in sql script Sep 1, 2016
.gitignore first commit Aug 31, 2016
README.md
package.json use mocha-better-spec-reporter Sep 13, 2016

README.md

KNEX-CSV-TRANSFORMER

You will need the following things properly installed on your computer.

Basic usage

Create a seed file for Knex.

knex seed:make seed_name

Use the transfomerHeader function to create an array of transformations that specify an input column from the csv file and a destination column in the database table.

Change the code to import the data from CSV.

const knex = require('knex');
const moment = require('moment');

const path = require('path');

const transformer = require('knex-csv-transformer').transformer;
const transfomerHeader = require('knex-csv-transformer').transfomerHeader;

exports.seed = transformer.seed({
  table: 'results',
  file: path.join(__dirname, '../api/csv/1998-1999.csv'),
  transformers: [
    transfomerHeader('Div', 'division'),
    transfomerHeader('Date', 'time', function(value) {
      return new moment(value, "DD/MM/YYYY").format('YYYY-MM-DDT00:00:00');
  })]
});

transformerHeader function

In order to transform rows in an input csv file to fields in a destination table, you need to create an array of transformations with each transformation created from the transformerHeader function.

A basic transformation is outlined below where a column in the csv file is specified as the first argument and a destination field in the specified table is specified in the second argument:

exports.seed = transformer.seed({
  table: 'results',
  transformers: [
    transfomerHeader('Div', 'division')
  })]

formatter function

It is possible to pass a formatter function that can perform a transformation on the data before its insertion in the destination table:

exports.seed = transformer.seed({
  table: 'results',
  transformers: [
    transfomerHeader('Date', 'time', function(value) {
      return new moment(value, "DD/MM/YYYY").format('YYYY-MM-DDT00:00:00');
  })]
});

The code above fromats the Date field in the csv file into the correct fromat that the destination time field in the database expects.

Look up values in the same database

exports.seed = transformer.seed({
  table: 'results',
  transformers: [
    transfomerHeader('Manager', 'manager_id', {
      lookUp: {
        table: 'managers',
        column: 'name',
        scalar: 'id',
        createIfNotExists: true,
      }
  })]
});

The transformer above will perform a look up query on the managers table and select the id of whatever name is in the Manager field of the csv file and use this value to insert into the manager_id field in the database. The createIfNotExists option above specifies whether or not to create the record in the managers table if it does not already exist.

A good example of what transformations are available can be found in the tests of this package.

Installation

  • npm install

Global installation of mocha

If you have mocha installed globally you need to install mocha-given globally as well.

$ npm install -g mocha mocha-given

Running Tests

  • Make sure you have phantomjs installed, npm install -g phantomjs
  • npm test