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