csv-scrubber

For node 4.2.4 and higher (uses ES6 functionality).

csv-scrubber is a library for stream transforming CSV files.

* Scrub field values.
* Add rows.
* Remove rows.
* Split one input file to multiple output files.
* Integrated logging.

``bash
npm install csv-scrubber
`

Usage

csv-scrubber uses middleware kind of like http://expressjs.com

Middleware is executed in the order it is added to the scrubber.

Each middleware function operates on a single row from the input CSV. However
since you can add or remove rows, middleware is passed an array of records.
Initially there is a single record in the array (the original CSV row). To add
or remove output rows just add or remove records in the array.

Each record is an array of strings -- one element for each CSV column. You can
access record columns by index, but it's generally easier to access
them by their column name (see addNameAccess middleware below).

If you bind your middleware to the scrubber you have access to the scrubber
log and properties like currentRow.

scrubber.useHeader runs middleware for header rows only.

scrubber.useAllRows runs middleware for all rows including header.

scrubber.use runs middleware for non-header rows only.

scrubber.js

`javascript
#!/usr/bin/env node

const scrubber = require('csv-scrubber')();

// Accessing values by index here. Generally easier to access by name as shown
// in formatBirthDate. If prior middleware may have added or removed rows,
// you will want to loop over records as show here. If not records[0] is simpler.
function removeRowIfFirstColBlankOrInteger(records, cb) {
for (let i = 0, l = records.length; i < l; i++) {
if (isBlank(records[i][0]) || isInteger(records[i][0])) {
this.log.debug(removed row: ${this.currentRow});
records.splice(i, 1);
}
}
cb(null);
}

// I know i am not going to add or remove rows, so just using records[0].
// Also accessing field by name instead of index.
function formatBirthDate(records, cb) {
records[0].birthdate = formatDate(records[0].birthdate);
}

scrubber.useAllRows(scrubber.addNameAccess.bind(scrubber));
scrubber.use(removeRowIfFirstColBlankOrInteger.bind(scrubber));
scrubber.use(formatBirthDate.bind(scrubber));

scrubber.scrub();
`

Default behavior is to pipe stdin to stdout

`bash
./scrubber.js < original.csv > some_rows_removed.csv
`

Included middleware

csv-scrubber comes with two middleware functions included.

normalizeHeader makes your header row all lowercase and replaces anything
other than alpha numerics with underscore and removes dup underscores.
It logs a warn message if you end up with duplicate column names.

addNameAccess allows you to access fields by their column name instead
of index. You can do record.first_name instead of record[3].

`javascript
const scrubber = require('csv-scrubber')();

// You may want to normalize your header. If you do, it has to come before
// addNameAccess.
scrubber.useHeader(scrubber.normalizeHeader.bind(scrubber));
// If your CSV has a header row, addNameAccess is really nice.
scrubber.useAllRows(scrubber.addNameAccess.bind(scrubber));
`

Input and output streams

You have total control over the input and output streams.

If you specify nothing, we will take input from stdin and output to
stdout.

Here are examples of how to specify input and output streams.

`
const scrubber = require('csv-scrubber')({ instream: '/some/input.csv' });
const scrubber = require('csv-scrubber')({ instream: myInputStream });

const scrubber = require('csv-scrubber')({ outstream: '/some/output.csv' });
const scrubber = require('csv-scrubber')({ outstream: myOutputStream });
const scrubber = require('csv-scrubber')({ outstream: null });
`

If you specifically pass null as outstream we don't output anything. That
may be useful if you are sending rows to database or such. It's also useful with
splits where you are only interested in outputting the splits and not the source
row.

See splits below for how to output to multiple files.

Logging

scrubber.log uses loglove and defaults to logging warn level to the file
scrub.log. https://github.com/johndstein/loglove

Multiple output files (splits)

Splits allow you to split a single input file into multipe output files.

Imagine you have a CSV file that includes both contact and company info in the
same row. You want to split them out to different output files.

`javascript
const splits = [
{ name: 'contact',
header: ['name', 'title', 'email'],
outstream: 'contact.csv' },
{ name: 'company',
header: ['name', 'address', 'fax'],
outstream: 'company.csv' } ]

const scrubber = require('csv-scrubber')({ splits: splits });
`

Assuming we pass the above splits to the scrubber constructor, scrubber will
add the following properties to the header record.

`javascript
record.contact = ['name', 'title', 'email'];
record.company = ['name', 'address', 'fax'];
`

Scrubber will add the following blank row to each non-header record.

`javascript
record.contact = ['', '', ''];
record.company = ['', '', ''];
`

You will need to add middleware functions to populate the contact and
company values from the record. Then scrubber will output contact to
contact.csv and company to company.csv.

If you are using the addNameAccess middleware, you can access split field
values by name. So you can do record.contact.name = 'Harry'`.

TODO

maybe add error on name access if you reference a column that's not in the
header.