csv2sql-lite
v0.0.6
Published
nodejs stream for .csv to MySQL USE, CREATE and INSERT .sql
Downloads
3,135
Maintainers
Readme
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: 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
Testing
Run npm install && npm test
from the base directory to run tests.
License
MIT