excel-to-mysql

This module converts your correctly formatted Excel spreadsheet to a specified table in specified database in MYSQL. The queries can also be written in a file (BETA).

Excel Formats Supported

Supported Excel formats are XLS/XLSX/CSV.

Usage

The Database must already be created in MYSQL. A table name should be provided. Please keep the column names in String format only.

Spreadsheet Format

Please have a look at the sample Excel sheets provided to have a clear view of the File. Microsoft Sample Sheet (XLSX)

Installation

npm install excel-to-mysql --save

Testing

git clone https://github.com/ngudbhav/excel-to-mysql.git
cd excel-to-mysql/

Navigate to the folder.

cd test/
nano test.js

Now this file needs the MYSQL credentials. Provide those credentials in String format and save by pressing the following keys.

'CTRL+X'
'Y'
'Return'

Get back and test the module.

cd ..
npm test

Using

Note: Please correctly format the Excel sheet else this won't work.

var excelMysql = require('excel-to-mysql');

This module needs 3 arguments. The first one is the object with your credentials.

Database connection can be established in 2 ways.

1. Pass in your connection object which is the return of mysql.createConnection. The connection accepts connection both from mysql/mysql2. The same connection will be used to read/write data.
2. Pass in your credentials in the below format. mysql will be used to create a connection.

var credentialsForDB = {
	host: host,
	port: port || 3306,
	user: MYSQL Username,
	pass: Password for the above account,
	path: path for the excel file,
	table: Table name for creation,
	db: Your Database name,
	endConnection*: true,
	connection: <Object>
};

• Please note that endConnection false may not terminate the process.

var credentialsForFile = {
	path: path for the excel file,
	table: Table name for creation,
	db: Your Database name
};

The second one is an optional argument of options with default values as follows.

var options = {
	verbose: true // logs the steps undergoing.
	autoId: false // Automatically insert id of every row, i.e., numbering every row.
	customStartEnd: false // Custom insert the row and columns rather than full excel-file.
	startRow: <required> // Valid only if customStartEnd is true. Defines the start Row of the data.
	endRow: <required> // Valid only if customStartEnd is true. Defines the end Row of the data.
	startCol: <required> // Valid only if customStartEnd is true. Defines the start Column of the data.
	endCol: <required> // Valid only if customStartEnd is true. Defines the end Column of the data.
	safeMode: false // Backup the db to the <destination> with <db>.sql as file name.
	destination: <String> // Valid only if safeMode is true. The location of db.sql file.
}

The third argument is the callback function which will be executed only after the completion of the whole conversion.

try {
  excelMysql.covertToMYSQL(credentials, options, callback);
  excelMysql.convertToFile(credentials, options, callback);
} catch(error) {
  throw error;
}

try/catch is always recommended because of the type of errors that can occur.

convertToFile Function will write the .sql file to the current working directory if no destination is provided with db.sql as the file name.

Want to covert to MongoDB instead

We have got you covered! Github Link.

Want to use the GUI instead

We have got you covered! Github Link.