banded

Introduction

banded is a tool to pipe csv data into a mongodb database

Installation

npm install banded

Options

• file (string required)
• model (mongoose model required)
• types (array [String, Number, Date])
• delimiter (string)
• rowDelimiter (string)
• columns (array|function)
• preTransform (function)
• postTransform (function)
• aggregate
  • rows (number required when aggregating)
  • moving (boolean)

Usage

Basic

var band = require('banded');
var Animal = require('./models/animal');

var options = {
  file: 'animals.csv',
  model: Animal
};

band(options, function() {
  // go about your buisness
  ...
});

Using Options #1

animals.csv

TYPE;NAME   dinosaur;Rex  rhino;Spike

var band = require('banded');
var Animal = require('.models/animal');

var options = {
  file: 'animals.csv',
  model: Animal,
  delimiter: ';',
  rowDelimiter: '\t',
  columns: function(header) {
    return header.toLowerCase();
  }
};

band(options, function() {
  ...
  // The following entries have been made in your db:
  // {
  //   type: 'dinosaur',
  //   name: 'Rex',
  // },
  // {
  //   type: 'rhino',
  //   name: 'Spike',
  // }
});

Using Options #2

employees.csv

name,age,joined
John,30,2015-1-1
Jane,35,2014-11-29

var band = require('banded');
var Employee = require('.models/employee');

var options = {
  file: 'employees.csv',
  model: Employee,
  types: [String, Number, Date]
};

band(options, function() {
  ...
  // The following entries have been made in your db:
  // {
  //   name: 'John',
  //   age: 30,
  //   joined: ISODate("2015-01-01T05:00:00Z")
  // },
  // {
  //   name: 'Jane',
  //   age: 35,
  //   joined: ISODate("2014-11-29T00:00:00Z")
  // }
});

Transformations

If the data in your csv file is not exactly what you want or only provides the base data to construct your models, use transformations.

Two transformations are provided to modify data both before and after it is injected into your model.

PreTransform

The preTransform option allows you to provide a function to change the raw data before it is processed and converted to your model object.

This step occurs before types have been applied, so all datum are and should be used as strings. This does, hoever provide you the flexibility to change anything about the data that you so choose.

Ex: Your data file provides far to much irrelevant information that you do not need to store. It also provides information that you would like to store differently

customer.csv

id,name,active_years,last_purchase_date,last_purchase_item,sales_rep
(^ this line should be deleted before the csv file is processed)
...

var options = {
  file: 'customer.csv',
  model: Customer,
  types: [Number, String, Date, String],
  columns: ['id', 'name', 'dateJoined', 'status'],
  preTransform: function(row) {
    var id = row[0],
        name = row[1],
        activeYears = Number(row[2]),
        lastDate = Date(row[3]);
    
    var now = new Date();
    
    var dateJoined = new Date();
    dateJoined.setFullYear(now.getFullYear() - activeYears);
    
    var status;
    
    if(now.getFullYear() - lastDate.getFullYear()) {
      status = 'inactive';
    } else {
      status = 'active';
    }
    
    return [id, name, dateJoined, status];
  }
};

Aggregates

Aggregating data allows you to combine mulitple rows of raw data in order to produce one correct row to be parsed.

Ex. You need the 10-point moving average of points supplied by your data file

date,sales
(^ this line should be deleted before the csv file is processed)
...

var options = {
  file: 'customer.csv',
  model: Customer,
  types: [Date, Date, Number],
  columns: ['start', 'end', 'avgSales'],
  aggregate: {
    rows: 10,
    moving: true
  },
  preTransform: function(rows) {
    var totalSales = rows.reduce(function(acc, curr) {
        return acc + Number(curr[1]);
    }, 0);
    
    var avgSales = totalSales / rows.length;
    var startDate = rows[0][0];
    var endDate = rows[rows.length-1][0];
    
    return [startDate, endDate, avgSales];
  }
};

PostTransform

The postTransform option allows you to provide a function to mutate the model objects created from the csv directly prior to being saved to your database.

This step occurs after types have been applied and only allows you to modify or add properties to the existing model object. It is encouraged to use this for minor changes only.

Ex: Your data file is 0-indexed, but you want the entries in your database to be 1-indexed for easier reading

var band = require('banded');
var Warehouse = require('.models/warehouse');

var options = {
  file: 'warehouses.csv',
  model: Warehouse,
  types: [Number, Number],
  columns: ['id', 'value'],
  postTransform: function(warehouse) {
    warehouse.id++;
  }
};

band(options, function() {
  ...
});

Common Issues

• Everything is stored into my database as a string

  Without specifying the types for each column the parser will assume that all types are strings.

• My column row is showing up in my database and causing errors

  If you specify the names of the columns the parser will assume that the file does not contain a header row and process all rows as though they were data. Simply remove the header row from your csv file.