mariadb-csv-to-db
v2.1.4
Published
Bulk insert csv file to table, focused on convenience rather than performance.
Downloads
15
Maintainers
Readme
mariadb-csv-to-db
Bulk insert csv file to table, focused on convenience rather than performance.
Quick Example
const mariadb = require('mariadb')
const batch = require('mariadb-csv-to-db')
process.nextTick(async () => {
let conn
try {
// Property for CSV to Database
const props = {
file: {
path: 'Your csv file path',
encoding: 'utf-8',
quote: '',
delimiter: ','
},
database: {
table: 'test.WOW'
},
import: {
skipHeader: true,
sizePerTime: 1000,
modifyFields: fields => fields.splice(0, 1)
}
}
// Databse connection
conn = await mariadb.createConnection({ host: 'HolyHost', user: 'Kimchi', password: '????' })
await conn.beginTransaction()
console.log(await batch(conn, props)) // { totalCount: 1000, totalAffectedRows: 1000, match: true }
await conn.commit()
} catch (e) {
await conn.rollback()
console.error(e)
} finally {
if (conn) {
await conn.end()
}
process.exit()
}
})
Flow
- Access your csv file
- Decoding your csv file
- Parsing your csv record → fields(array) using props(quote, delimiter, escape)
- Modifying your fields
- Set parameter if props.database.columns is array
- Set parameter using header (modified fields) if props.database.columns is null
- Skip header true ? not pushing header into values(array)
- Pushing your fields to values until values.length < sizePerTime
- Batch values
*if values.length !== affetcedRows(batch insert result) ? then throw Error - [7]~[8] loop
- Batch rest values
- Return total file line count and total affected row count (match always true because [9])
batch(conn, props)
conn
object(required), mariadb connectionprops
object
file
object
path
string(required), csv file path
encoding
string, file encoding, default utf-8
quote
string, csv quote, default (empty string)
delimiter
string, csv delimiter, default ,
escape
string, csv escape, default false
database
object
table
string(required), target table name
columns
array, column names, default null
import
object
skipHeader
boolean, first line of the file is excluded from the batch, default true
sizePerTime
number, number of rows inserted per time, default 1000
modifyFields
function, fields => return fields, modifying fields, must return fields, default null
Another Example
const mariadb = require('mariadb')
const batch = require('mariadb-csv-to-db')
process.nextTick(async () => {
let conn
try {
// Property for CSV to Database
const props = {
file: {
path: 'Your csv file path',
encoding: 'utf-8',
quote: '',
delimiter: ','
},
database: {
table: 'test.WOW',
// if columns: null ? insert into table values (?, ?)
// if columns array ? insert into table (columns.join(',')) values (?, ?)
columns: ['`a`', '`b`']
},
import: {
skipHeader: true,
sizePerTime: 1000,
modifyFields: fields => [fields[0], fields[1]] // returned fields length must be equal to the props.database.columns length
}
}
// Databse connection
conn = await mariadb.createConnection({ host: 'HolyHost', user: 'Kimchi', password: '????' })
await conn.beginTransaction()
console.log(await batch(conn, props)) // { totalCount: 1000, totalAffectedRows: 1000, match: true }
await conn.commit()
} catch (e) {
await conn.rollback()
console.error(e)
} finally {
if (conn) {
await conn.end()
}
process.exit()
}
})