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 = ;var Animal = ; var options = file: 'animals.csv' model: Animal; ;
Using Options #1
animals.csv
TYPE;NAME dinosaur;Rex rhino;Spike
var band = ;var Animal = ; var options = file: 'animals.csv' model: Animal delimiter: ';' rowDelimiter: '\t' { return header; }; ;
Using Options #2
employees.csv
name,age,joined
John,30,2015-1-1
Jane,35,2014-11-29
var band = ;var Employee = ; var options = file: 'employees.csv' model: Employee types: String Number Date; ;
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' { var id = row0 name = row1 activeYears = Numberrow2 lastDate = Daterow3; var now = ; var dateJoined = ; dateJoined; var status; ifnow - lastDate 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 { var totalSales = rows; var avgSales = totalSales / rowslength; var startDate = rows00; var endDate = rowsrowslength-10; 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 = ;var Warehouse = ; var options = file: 'warehouses.csv' model: Warehouse types: Number Number columns: 'id' 'value' { warehouseid++; }; ;
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.