node-csv-loader v2.0.0

node-csv-loader

Simple library to import a .csv into MySQL (at least for now).

Install

From NPM:

npm install node-csv-loader

Notes

• The first row of the .csv, must be the column names.
• The import only can be done to one, and only one table at a time.
• The library is going to try to INSERT the .csv records. OK duplicate key ALL THE FIELDS ARE GOING TO BE UPDATED. (The PK only can be defined into the Table's definition).

Usage

var CSVLoader = require('node-csv-loader');
new CSVLoader({
  database: mysqlConfig,
  csvPath: 'path/to/file.csv',
  tableName: 'MySQLTableName',
  map: {
    'FieldToName1': {
      value: 'const'
    },
    'FieldToName2': 'const',
    'FieldToName3': {
      field: 'fieldNameOnCSV'
    },
    'FieldToName4': {
      field: 'fieldNameOnCSV',
      adapter: function(value) {
        return 'Demo: ' + value;
      }
    }
  }
}).run(function(statistics) {
  console.dir(statistics);
});

Setting up connections

The recommended way to establish a connection is this:

var mysqlConfig = {
  host: 'example.org',
  user: 'bob',
  password: 'secret',
  database: 'hello_world'
});

CSVLoader uses node-mysql as connection library, for advanced configuration see felixge/node-mysql/

Map configuration

The map is an object container the mapping between the .csv and MySql, where each attribute represents the database field, and the value represents:

Constants

{
  'DatabaseField1': <constant>,
  'DatabaseField2': {
    value: <constant>
  }
};

CSV Fields

{
  'DatabaseField1': {
    field: 'CSV Field Name'
  },
  'DatabaseField2': {
    field: 'CSV Field Name',
    adapter: function(value) {
      return 'Hello: ' + value;
    }
  }
};

Optionally you can add an "adapter" attribute to the field, which receives the following parameters:

• value: Current field value.
• rowMap: Same as described below on the filter callback.
• rowValues: Same as described below on the filter callback.

Options

blockSize

Type: integer | Default: 100

Size of the object buffer to persist into MySQL.

filter

Type: null|function | Default: null

Callback to filter the rows loaded from the .csv. The function must return a boolean.

  /*
  * @param  {columnName: index} rowMap Object containing {columnName: index}
  * loaded from .csv
  * @param  [] rowValues Array containing all the row values.
  * @return boolean Return true, if the row is valid.*/
  filter: function(rowMap, rowValues) {
    // To get an specific column value: rowValues[columnName['{fieldName}']].
    // * columnName['{fieldName}'] -> returns the index (from rowValues),
    // that matches the field.
    return rowValues[rowMap['Active']] === 'True';
  }

onSuccess

Type: null|function | Default: null Callback triggered when the import to MySQL is done.

  /*
  * @param  {totalRecords: integer,
  *         skippedRecords: integer,
  *         executionTime: integer} Object with process statistics.
  */
  onSuccess: function(statistics) {
    // To get an specific column value: rowValues[columnName['{fieldName}']].
    // * columnName['{fieldName}'] -> returns the index (from rowValues),
    // that matches the field.
    console.dir(statistics);
  }

Callbacks Notes

The filter & adapter function are executed into the library context, which allows you to access the configuration object, MySQL connection, dependencies, etc by using "this".

Todos

• Add Code Comments
• Improve dependency management
• Improve configuration
• Add PK definition support
• Improve conventions-over-configurations