Convert excel documents to sqlite!

• Sqlite3: 5.0.0
• Xlsx: 0.16.4

Navigation

• Main
  • Loading your excel
  • Sheet names
  • Read One Sheet
    • Get sheet data
    • Save sheet to sqlite
  • Read All Sheets
    • Get data of all sheets
    • Save whole excel
  • _xlsx

Firstly, load the package.

const excelToSqlite = require("excel-to-sqlite");

The export of excel-to-sqlite is one function, with one parameter: excelPath.

The path must me absolute, like __dirname. This example uses path.

const path = require("path"); let excelPath = path.join(__dirname, "test.xlsx"); // File "test.xlsx" in the current directory let excel = excelToSqlite(excelPath);

After you used the excelToSqlite function, you can use the property sheets to get a string array of all the sheet names.

let sheets = excel.sheets; console.log(sheets);

To read one sheet, use the method readSheet afer you used the excelToSqlite function. It has one parameter: The name of the sheet.

The name of the sheet is case-sensitive!

let sheet = excel.readSheet("Sheet1"); // Read "Sheet1".

To get the data of the current sheet, use the property data on sheet.

let data = sheet.data;

To get the json data, use the method getJSON.

let json = sheet.getJSON();

[ Sheet1: [ {column_1_name: "value", column_2_name: "value", ...}, {column_1_name: "value", column_2_name: "value", ...} ] ]

To save the current sheet to sqlite, use the saveTo method. It has one parameter: The name of the sqlite file.

Warning: When saving to a database, a table with the same name as the current sheet will get deleted.

To change the name of the table in the sqlite database, set sheet._name to another string before calling the saveTo function.

This function returns a promise, that will be resolved with a database object of sqlite3.

// Save without changing the name sheet.saveTo("database.sqlite").then((database) => { console.log(`Sheet ${sheet._name} saved in "database.sqlite"!`); }); // Change the name for the table and then save sheet._name = "lol"; sheet.saveTo("database.sqlite").then((database) => { console.log(`Sheet ${sheet._name} saved in "database.sqlite"!`); });

To read all the sheets in an excel, use the readAll method.

let sheets = excel.readAll();

To get all the data of all the sheets, use the property data. To convert your data to JSON, use the method getJSON.

let data = sheets.data; let json = sheets.getJSON();

Output: See Output

To save the whole excel in sqlite, use the saveTo method. It has one parameter, the name of the database.

This function returns a promise, that will be resolved with a database object of sqlite3.

Warning: When saving to a database, a table with the same name as a sheet will get deleted.

sheets.saveTo("database.sqlite").then((database) => { console.log("Whole database saved in sqlite!"); });

The property _xlsx is the output of xlsx.readFile.