Automation

Command Line Interface Examples

If you need to automate or schedule Excel to MySQL conversions, you can take advantage of the special batch/shell version of Excel2MySQL which is called excel2mysqlCLI.

Simply setup a bat file which calls excel2mysqlCLI.exe for each file to be loaded.
Of course, you'll need to substitute your own values for myhost, mydb, myuser, mypass.. etc.

Example 1: Import an Excel file to a remote MySQL server.

excel2mysqlCLI.exe -f "data1.xls" -z myhost -d mydb -u myid -p mypass --append

Example 2: Import 3 Excel files with the following commands in a bat file:

excel2mysqlCLI.exe -f "data1.xls" -d mydb -u myuserid -p mypass --append
excel2mysqlCLI.exe -f "data2.xls" -d mydb -u myuserid -p mypass --append
excel2mysqlCLI.exe -f "data3.xls" -d mydb -u myuserid -p mypass --append

Example 3: If you have hundreds of files, you could use a loop in your bat file like this:

for %%f in (*.xls) do excel2mysqlCLI.exe -f "%%f" -d mydb
-u myuserid -p mypass --append

* Please refer to the Command-Line Options below for all available options.

You can then simply run your bat file as needed OR use "windows task scheduler" to schedule the bat file for running daily/hourly... etc.

You can also call the command-line version of Excel2MySQL with your favorite programming language, such as Python, Perl, C++, etc... However , a bat file is the simplest option for most users.

I would be happy to help you get your bat file configured.
Let me know if you need my help.

Command-Line Options

Parameters
-f file
--file=file
Excel file to convert (include full path) (REQUIRED)
-s sheet
--sheet=sheet
Excel sheet to convert (omit to convert all)
-z host
--dbhost=host
mysql server hostname or IP address (defaults = localhost)
-d database
--dbname=database
mysql database name (REQUIRED)
-u user
--dbuser=user
mysql user name (REQUIRED)
-p password
--dbpass=password
mysql password
-x port
--dbport=port
mysql port number (omit will default to 3306)
--sslca=pem file SSL CA file, including path (supply these values to enable SSL)
--sslcert=pem file SSL Cert file, including path
--sslkey=pem file SSL Key file, including path

Note: Parameter values that contain spaces should be enclosed in quotes (for example: -d "my database")

Switches
-h
--help
display help message and exit
--append append records to existing records in table
--empty empty table records before uploading Excel data
--drop drop table before uploading Excel data
-n
--noheader
first row is not a header (omit to indicate first row contains header)
-a
--allow
allow spaces in table & field names (omit to change spaces to underscores)
-m
--myisam
use myisam database storage engine (omit to use innodb)
-k
--keep
keep blank rows & columns (omit to remove)
-c
--unmerge
Unmerge merged cells
-t
--tableprefix
Prefix table name with Excel file name
-e
--setnull
Set empty VARCHAR as NULL (omit to set as blank)
-o
--optimize
Optimize field types
Omit to leave existing fields unchanged & new fields become VARCHAR