xlsx2sql v1.6.80
README
Install
npm install xlsx2sql
Description
Transform an xlsx file into a list of sql insert statement.
Columns in excel file can be ignored
title line in excel file can be ignored
You can specify a custom name for the table used in the generated sql
You can specify a custom name for each field
You can specify a custom converter if needed for each field
Date can be converted to mysql format with our converter
You can add a custom value (useful if the excel doent contain all the necessary fields)
Exemple
See exemple files for details :
usage exemple :
var xlsx2sql = require('xlsx2sql')
xlsx2sql.xlsx2sql(`${__dirname}/test.xlsx`, {
// the name if the table
name: 'product',
// the number of first lines to skip
skip: 1,
// the fields specifications
fields: [
// title is the name of the sql field. It will read the 0's column in excel file
{ index: 0, name: 'title' },
// start is the name of the sql field. It will read the 1's column in excel file, and convert it using the special date converter
{ index: 1, name: 'start', convert: xlsx2sql.ExcelDateToJSDate },
// you can also provide a custon function for convertions
{ index: 2, name: 'tva_id', convert: (v) => {
if (v == '0.1') return 60;
else if (v == '0.2') return 61;
else if (v == '0.055') return 59;
else if (v == '0') return 58;
else throw new Error('Unknown tva '+v);
}},
{ index: 3, name: 'tarifLibreTtc' },
{ index: 4, name: 'tarifLibreBoolean', convert: (v) => {
if (v == 'ON') return 1;
else return 0;
}},
{ index: 5, name: 'compta' },
{ name: 'createdAt', value: 'now()' },
{ name: 'updatedAt', value: 'now()' },
]
}, (err, sql) => {
// generated sql is :
// insert into `product` (`title`,`start`,`tva_id`,`tarifLibreTtc`,`tarifLibreBoolean`,`compta`,`createdAt`,`updatedAt`) values
// ('Privatisation complète du château avec hébergement','43466','60','0','1','74501652',now(),now()),
// ('Hébergement en chambre single','43466','60','0','1','74501652',now(),now()),
// ('Petit déjeuner','43466','60','0','1','74501652',now(),now()) ;
})
23 days ago
1 month ago
1 month ago
3 months ago
12 months ago
12 months ago
12 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
10 months ago
10 months ago
7 months ago
8 months ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
1 year ago
12 months ago
1 year ago
1 year ago
12 months ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
4 years ago
5 years ago