csv2sql-lite

  _______ _______ ___ ___ _______ _______ _______ ___
 |   _   |   _   |   Y   |       |   _   |   _   |   |
 |.  1___|   1___|.  |   |___|   |   1___|.  |   |.  |
 |.  |___|____   |.  |   |/  ___/|____   |.  |   |.  |___
 |:  1   |:  1   |:  1   |:  1  \|:  1   |:  1   |:  1   |
 |::.. . |::.. . |\:.. ./|::.. . |::.. . |::..   |::.. . |
 `-------`-------' `---' `-------`-------`----|:.`-------' lite
                                              `--'

csv2sql-lite is a transform stream which is both writable and readable. You would write a .csv file/string into it, and read out MySQL INSERT statements. Useful for large .csv files so one does not have to buffer the .csv into memory.

Note: This repo can be found on npm here: csv2sql-lite

Note: See csv2sql-stream on npm for an alternative.

Caveat: The .csv parsing is very rudimentary, however it can be replaced easily by many modules on npm, look for lineToInsert() in the source.

Caveat: Only tested on *nix OS, YMMV on Windoze.

Usage

Install:

npm install csv2sql-lite

Use:

var CSV2SQL = require('csv2sql-lite');
var csv2sql = CSV2SQL(opts);

See below for the documentation of opts, the options object.

Example

Open up a read stream to the .csv file and a write stream to where you want the .sql file to be output:

//csv_stream.js
var fs = require('fs');
var rstream = fs.createReadStream('./data.csv');
var wstream = fs.createWriteStream('./mysql.sql');

Load the csv2sql-lite module, with options:

var CSV2SQL = require('csv2sql-lite');
var csv2sql = CSV2SQL({
  tableName: 'myTableName',
  dbName: 'myFancyDatabaseName',
});

Wire up the streams with pipe():

rstream.pipe(csv2sql).pipe(wstream);

Execute the program:

$ nodejs csv_stream.js

If you started with data.csv like this:

username,email,password
john,[email protected],p455w0rd
suzie,[email protected],ilovejohn

You'll end up with mysql.sql looking like this:

use myFancyDatabaseName;
INSERT INTO myTableName (username,email,password) VALUES
("john","[email protected]","p455w0rd")
,("suzie","[email protected]","ilovejohn")
;

Then you can easily load the .sql file into MySQL:

$ mysql -u root -p < mysql.sql

Options

You can pass an options object to csv2sql containing any of the following:

Option | Type | Default | Explanation ------------- | -------------| ------------- | ------------ tableName | String | 'undefined' | The name of the table to INSERT into dbName | String | false | Optionally insert USE dbName; at beginning of .sql file dropTable | Boolean | false | Optionally insert DROP TABLE IF EXISTS tableName; at beginning of .sql file seperator | String | ',' | Optionally specify .csv file field seperator lineSeperator | String | '\n' | Optionally specify .csv file EOL seperator isEachLineInsert | Boolean | false | Optionally insert insert into xxx () values for each line,This is especially useful when dealing with large amounts of data eachLineHandler | Function | undefined | before write to steam,you can use this function to deal the line string,example process geometry data everyLineHandler = (line)=>{return line.replace(/(\"POINT\(.+? .+?\)\")/g, "ST_GeomFromText($1)")}

Testing

Run npm install && npm test from the base directory to run tests.

License

MIT