mdbtools for Node to use MS Access database.
These aren't native bindings, they just talk to stdin/stdout/stderr of mdbtools.
NPM link: @el3um4s/mdbtools
npm install @el3um4s/mdbtools
This package requires mdbtools installed on the host system.
Windows
Download and unzip mdbtools-win (Based on mdbtools-win).
Debian
apt install mdbtools
MacOS
brew install mdbtools
This package is not meant for macOS. It may be that it works like it doesn't.
import { versionMdbTools } from "@el3um4s/mdbtools";
// in Windows
const windowsPath = "./mdbtools-win";
const versionW = await versionMdbTools(windowsPath);
console.log(versionW);
// mdbtools v1.0.0
// in Linux (Debian)
const versionL = await versionMdbTools();
console.log(versionL);
// mdbtools v0.7.1display the version of the specified file
Requires: mdbtools 0.4+
version({ database: "",windowsPath?: ""}):Promise<string>Get the version (JET 3 or 4) of an mdb fileversionMdbTools(windowsPath?: string): Promise<string>Get mdbtools version
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/fruit.mdb";
const v = await version({ database, windowsPath });
console.log(v);
// JET4List tables in the specified file
Requires: mdbtools 0.3+
tables({ database: "",windowsPath?: ""}):Promise<string[]>Get the tables in an mdb file (exclude system tables)tablesAll({ database: "",windowsPath?: ""}):Promise<string[]>Get the tables in an mdb file (include system tables)tablesSystem({ database: "",windowsPath?: ""}):Promise<string[]>Get the tables in an mdb file (only system tables)tablesToFile({ database: "",windowsPath?: "", file: string}):Promise<boolean>Save the list of tables in a file (exclude system tables)tablesAllToFile({ database: "",windowsPath?: "", file: string}):Promise<boolean>Save the list of tables in a file (include system tables)
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/fruit.mdb";
const list = await tables({ database, windowsPath });
console.log(list);
// [ "Fruit", "Fruit Salad", "Veggie Salad", "Muffin/Bread", "Dried"]
const listSystem = await tablesSystem({ database });
console.log(listSystem);
// [ "MSysObjects", "MSysACEs", "MSysQueries", "MSysRelationships", "MSysAccessObjects", "MSysNavPaneGroupCategories", "MSysNavPaneGroups", "MSysNavPaneGroupToObjects", "MSysNavPaneObjectIDs", "MSysAccessXML", "MSysNameMap" ]
const file = "./src/__tests__/__to_file__/tables-fruit.txt";
const t = await tablesToFile({ database, windowsPath, file });
console.log(t);
// true
const fileWithSystem =
"./src/__tests__/__to_file__/tables-fruit-with-system-tables.txt";
const ts = await tablesAllToFile({
database,
windowsPath,
file: fileWithSystem,
});
console.log(ts);
// trueList queries from an Access database
The currently implemented SQL subset is quite small, supporting only single table queries, no aggregates, and limited support for WHERE clauses.
Requires: mdbtools 0.9+
queries({ database: "",windowsPath?: ""}):Promise<string[]>Get the queries in an mdb filequeriesSQL({ database: "",windowsPath?: "", query: ""}):Promise<string>Get the query SQL stringqueriesToFile({ database: "",windowsPath?: "", file: string}):Promise<boolean>Save the list of queries in a filequeriesSQLToFile({ database: "",windowsPath?: "", query: "", file: string}):Promise<boolean>Save the query SQL string in a file
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const listQueries = await queries({ database, windowsPath });
console.log(listQueries);
// [ "UserA", "MainColors", "ChangeValueDogTo40", "ChangeValueDotTo4", "AddApple", "DeleteApple", "aàeèéiìoòuù"]
const s = await queriesSQL({ database, windowsPath, query: "UserA" });
console.log(s);
// SELECT Users.* FROM [Users] WHERE (((Users.UserCategory)="A"))
const file = "./src/__tests__/__to_file__/test-queries.txt";
const t = await queriesToFile({ database, windowsPath, file });
console.log(t);
// true
const query = "UserA";
const fileQuery = "./src/__tests__/__to_file__/test-queries-usera.txt";
const tq = await queriesSQLToFile({
database,
windowsPath,
query,
file: fileQuery,
});
console.log(tq);
// trueA simple SQL engine
The currently implemented SQL subset is quite small, supporting only single table queries, no aggregates, and limited support for WHERE clauses.
Requires: mdbtools 0.3+
sqlAsString({ database: "",windowsPath?: "", query: ""}):Promise<string>[]>Get a SQL Query result (like a string)sql({ database: "",windowsPath?: "", query: ""}):Promise<Record<string, unknown>[]>Get a SQL Query result (like a JSON array)sqlToFile({ database: "",windowsPath?: "", query: "", file: ""}):Promise<boolean>Save a SQL Query result in a filesqlFromFile({ database: "",windowsPath?: "", inputFile: ""}):Promise<Record<string, unknown>[]>Get a SQL Query from a file and return the result (like a JSON array)sqlFromFileAsString({ database: "",windowsPath?: "", inputFile: ""}):Promise<string>Get a SQL Query from a file and return the result (like a string)sqlFromFileToFile({ database: "",windowsPath?: "", inputFile:"", file: ""}):Promise<boolean>Get a SQL Query from a file and save the result in a file
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const s = "SELECT * FROM Colors WHERE Value > 10;";
const result = await sql({ database, windowsPath, sql: s });
console.log(result);
// [
// {
// Colors: "Blue",
// Value: "16",
// },
// {
// Colors: "Yellow",
// Value: "12",
// },
// ]
const resultAsString = await sqlAsString({ database, windowsPath, sql: s });
console.log(resultAsString);
// Colors Value
// Blue 16
// Yellow 12
const file = "./src/__tests__/__to_file__/sql result to file.csv";
const q = await sqlToFile({ database, windowsPath, sql: s, file });
console.log(q);
// true
const inputFile = "./src/__tests__/__to_file__/select colors.sql";
const f = await sqlFromFile({ database, windowsPath, inputFile });
console.log(f);
// [
// {
// Colors: "Blue",
// Value: "16",
// },
// {
// Colors: "Yellow",
// Value: "12",
// },
// ]
const fileResult = "./src/__tests__/__to_file__/sql from file to file.csv";
const rf = await sqlFromFileToFile({
database,
windowsPath,
inputFile,
file: fileResult,
});
console.log(rf);
// trueCount rows in a table
Requires: mdbtools 0.9+
count({ database: "",windowsPath?: "", table: ""}):Promise<number>Get the number of rows in a table
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const table = "Colors";
const c = await count({ database, windowsPath, table });
console.log(c);
// 7Export a table to a JSON array
Requires: mdbtools 0.9+
tableToJson({ database: "",windowsPath?: "", table: ""}):Promise<Record<string, unknown>[]>Export a table to a JSON array
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const table = "Colors";
const result = await tableToJson({ database, windowsPath, table });
console.log(result);
// [
// { Colors: "Red", Value: 10 },
// { Colors: "Green", Value: 5 },
// { Colors: "Blue", Value: 16 },
// { Colors: "Black", Value: 1 },
// { Colors: "Yellow", Value: 12 },
// { Colors: "White", Value: 10 },
// { Colors: "Others", Value: 0 },
// ]Export a table to a CSV string
Requires: mdbtools 0.1+
tableToCSV({ database: "",windowsPath?: "", table: ""}):Promise<string>Export a table to a CSV stringtableToCSVFile({ database: "",windowsPath?: "", table: "", file: "", options:""}):Promise<boolean>Export a table to a CSV file
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const table = "Colors";
const result = await tableToCSV({ database, windowsPath, table });
console.log(result);
// Colors,Value
// "Red",10
// "Green",5
// "Blue",16
// "Black",1
// "Yellow",12
// "White",10
// "Others",0
const file = "./src/__tests__/__to_file__/test-export-colors.csv";
const options = "-d; -Q";
const t = await tableToCSVFile({
database,
windowsPath,
table,
file,
options,
});
console.log(t);
// trueOptions:
| short | long | description |
|---|---|---|
| -H | --no-header | Suppress header row. |
| -d | --delimiter=char | Specify an alternative column delimiter. Default is comma. |
| -R | --row-delimiter=char | Specify a row delimiter |
| -Q | --no-quote | Don't wrap text-like fields in quotes. |
| -q | --quote=char | Use <char> to wrap text-like fields. Default is double quote. |
| -X | --escape=format | Use <char> to escape quoted characters within a field. Default is doubling. |
| -I | --insert=backend | INSERT statements (instead of CSV) |
| -N | --namespace=namespace | Prefix identifiers with namespace |
| -0 | --null=char | Use <char> to represent a NULL value |
| -B | --boolean-words | Use TRUE/FALSE in Boolean fields (default is 0/1) |
Generate DDL for the tables
Requires: mdbtools 0.1+
schema({ database: "",windowsPath?: "", table: ""}):Promise<string>Generate DLL schema for all tablesschemaTable({ database: "",windowsPath?: "", table: ""}):Promise<string>Generate schema only for a tableschemaToFile({ database: "",windowsPath?: "", table: "", file: ""}):Promise<boolean>Export DLL schema for all tables to fileschemaTableToFile({ database: "",windowsPath?: "", table: "", file: ""}):Promise<boolean>Export DLL schema only for a table to file
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const s = await schema({ database, windowsPath });
const table = "Colors";
const schemaT = await schemaTable({ database, windowsPath, table });
console.log(schemaT);
// CREATE TABLE [Colors]
// (
// [Colors] Text (50),
// [Value] Long Integer
// );Get the columns name of a table
Requires: mdbtools 0.3+
columnsName({ database: "",windowsPath?: "", table: ""}):Promise<string[]>Get the columns name of a tablecolumnsNameTables({ database: "",windowsPath?: ""}):Promise<Record<string, string[]>>Get the columns name of all tables
Examples:
const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const table = "Colors";
const columns = await columnsName({ database, windowsPath, table });
console.log(columns);
// [ "Colors", "Value" ]
const allColumns = await columnsNameTables({ database, windowsPath });
console.log(allColumns);
// {
// Users: [
// "UserID",
// "UserName",
// "UserSex",
// "UserBirthday",
// "UserTelephone",
// "UserValue",
// "UserCategory",
// ],
// Colors: ["Colors", "Value"],
// Dictionary: ["Number", "Word"],
// "Colors Table Two": ["Colors", "Value"],
// }In some Windows and Linux configurations it is not possible to read queries and tables with accented characters or symbols such as ° or µ.
Sample database used for tests and examples is from mdb.
To create this package I was inspired by:
