Home > Documentation > MS Excel to MySQL

Command line version of MS Excel to MySQL converter

To perform batch conversion or call the conversion procedure from an automation script you can use console version of Excel-to-MySQL X2SAGENT.EXE. Find this file in Excel-to-MySQL installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--charset=...   MySQL character set
--csv_delim=...   select the delimiter that separates CSV field
(0 - tab, 1 - semicolon, 2 - comma, 3 - space)
--dest=...   MySQL database name
--dump   convert MS Excel spreadsheet into MySQL dump file
--engine=...   MySQL storage engine (MyISAM, MEMORY, InnoDB, BerkeleyDB, ARCHIVE, FEDERATED, etc). Please refer to MySQL documentation for the further information.
--extr_fn   extract field names from the first row
--from_csv   data source is a CSV file (MS Excel spreadsheet by default)
--help   display help message and exit
--host=...   MySQL server IP address or network name
--logfile=...   path to the logfile where execution traces will be written
--mode=...   how to process an existing MySQL database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge)
--no_refine   disable improving type conversion (see Refine type conversion article for related information)
--port=...   MySQL port
--profile=...   path to a profile to load conversion settings from
--pswd=...   MySQL user password
--silent   use this option to disable program output
--src=...   CSV file or MS Excel spreadsheet name
--tab_file=...   file containing the list of worksheets to convert
(one worksheet name per line)
--tab_name=...   MySQL table name (when converting from CSV file)
--user=...   MySQL user name

Notes:

  1. You can omit 'host' parameter to connect local MySQL server
  2. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  3. If you omit 'tab_file' parameter, all worksheets will be converted
  4. If you omit 'mode' parameter, default mode will be used (it is 'overwrite the entire database' when converting xls file or 'overwrite existing tables only' when converting csv file)
  5. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")

Sample 1. The program converts MS Excel spreadsheet "c:\book1.xls" to MySQL database "book1_from_excel" on the remote MySQL server "mysqlhost" using table names file "c:\tabfile1.txt":

X2SAGENT.EXE --src=c:\book1.xls --dest=book1_from_excel --host=mysqlhost 
--user=administrator --pswd=the_passsword --tab_file=c:\tabfile1.txt

Worksheet names file should be formatted as follows:

Table_1
Table_2
...
Table_N

Sample 2. The program converts CSV file "c:\sheet1.csv" to MySQL database "Northwind" on the local MySQL server:

X2SAGENT.EXE --src=c:\sheet1.csv --dest=Northwind --tab_name=sheet1 
--user=administrator --pswd=the_passsword --from_csv --extr_fn --csv_delim=2

MySQL character sets

This parameter specifies correct encoding of text values in the destination MySQL database. It can have one of the following values:

value   Description     value   Description
big5   Big5 Traditional Chinese     dec8   DEC West European
cp850   DOS West European     hp8   HP West European
koi8r   KOI8-R Relcom Russian     latin1   cp1252 West European
latin2   ISO 8859-2 Central European     swe7   7bit Swedish
ascii   US ASCII     ujis   EUC-JP Japanese
sjis   Shift-JIS Japanese     hebrew   ISO 8859-8 Hebrew
tis620   TIS620 Thai     euckr   EUC-KR Korean
koi8u   KOI8-U Ukrainian     gb2312   GB2312 Simplified Chinese
greek   ISO 8859-7 Greek     cp1250   Windows Central European
gbk   GBK Simplified Chinese     latin5   ISO 8859-9 Turkish
armscii8   ARMSCII-8 Armenian     utf8   UTF-8 Unicode
ucs2   UCS-2 Unicode     cp866   DOS Russian
keybcs2   DOS Kamenicky Czech-Slovak     macce   Mac Central European
macroman   Mac West European     cp852   DOS Central European
latin7   ISO 8859-13 Baltic     cp1251   Windows Cyrillic
cp1256   Windows Arabic     cp1257   Windows Baltic
binary   Binary pseudo charset     geostd8   GEOSTD8 Georgian
cp932   SJIS for Windows Japanese     eucjpms   UJIS for Windows Japanese

Please refer to MySQL documentation to better understand the concept of character sets and collations.