Welcome to EMS Data Import utility!

EMS Data Import for MySQL is a powerful program to import your data quickly from MS Excel, MS Word, MS Access, DBF, TXT, CSV, Open Document, HTML files to MySQL tables. It provides adjustable import parameters, including source data formats for all the fields and destination data formats for the selected fields, commit options, number of records to skip, etc. Data Import for MySQL includes a wizard which allows you to set all the import options for different files visually, and a command-line utility to execute import in one-touch.

Visit our web-site: https://www.sqlmanager.net/ for details.

Key features

  • Import from most popular formats: MS Excel, MS Access, XML, DBF, TXT, CSV, MS Word, Open Document Format and HTML
  • Importing data to one or several tables or views from different databases
  • Automatically creates table structure
  • Special batch insert commands allow to import data at the maximum possible speed
  • A number of import modes - Insert All, Insert New, Insert or Update, and others
  • Secure Shell (SSH) and HTTP tunneling support
  • UNICODE support
  • Adjustable import parameters for each source file
  • Saving all import parameters set on current wizard session
  • Command-line utility to import data using the configuration file
  • Powerful visual options module
  • User-friendly localizable wizard interface

Product information

Homepage: | https://www.sqlmanager.net/products/mysql/dataimport
Support Ticket System: | https://www.sqlmanager.net/support
Register online at: | https://www.sqlmanager.net/products/mysql/dataimport/buy


What's new

Version | Release date
Data Import for MySQL 4.1.0 | July 12, 2023

What's new in EMS Data Import?

  • Support for Windows 11 ARM implemented.
  • Restart of the Wizard is now available at the last step.
  • Check for column name length added on creating a new table.
  • Default values for separator and quotes are available for CSV now.
  • The password is now saved correctly to the template file.
  • XML files without header are now processed with no issues.
  • Implemented localization in the command-line version.
  • Exit codes are corrected for working in the console version.
  • Other improvements and fixes.

See also:

Version history


System requirements

System requirements for Data Import for MySQL

  • Microsoft® Windows XP, Microsoft® Windows 2003 Server, Windows® 2008 Server, Microsoft® Windows Vista, Microsoft® Windows 7, Microsoft® Windows 8, Microsoft® Windows 2012 R2 Server, Microsoft® Windows 2012 Server, Microsoft® Windows 8.1, Microsoft® Windows 10, Microsoft® Windows 2016 Server, Microsoft® Windows 2019 Server, Microsoft® Windows 11, Microsoft® Windows 11 ARM
  • 512 MB RAM or more
  • 50MB of available HD space for program installation
  • Possibility to connect to any local or remote MySQL server
  • Supported MySQL server versions: from 4.1 up to 8.0


Installation

If you are installing Data Import for MySQL for the first time on your PC:

  • download the Data Import for MySQL distribution package from the download page available at our site;
  • unzip the downloaded file to any local directory, e.g. C:\unzipped;
  • run MyImportSetup.exe from the local directory and follow the instructions of the installation wizard;
  • after the installation process is completed, find the Data Import shortcut in the corresponding group of Windows Start menu.

Installation FAQs

If you want to upgrade an installed copy of Data Import for MySQL to the latest version:

  • download the Data Import for MySQL distribution package from the download page available at our site;
  • unzip the downloaded file to any local directory, e.g. C:\unzipped;
  • close Data Import application if it is running;
  • run MyImportSetup.exe from the local directory and follow the instructions of the installation wizard.

See also:

System requirements


How to buy Data Import

All purchases are provided by PayPro Global registration service. The PayPro Global order process is protected via a secure connection and makes on-line ordering by credit/debit card quick and safe.

PayPro Global is a global e-commerce provider for software and shareware sales via the Internet. It accepts payments in US Dollars, Euros, Pounds Sterling, Japanese Yen, Australian Dollars, Canadian Dollars or Swiss Franks by Credit Card (Visa, MasterCard/EuroCard, American Express, Diners Club), Bank/Wire Transfer.

If you want to review your order information, or you have questions about ordering or payments please visit our PayPro Global Shopper Support, provided by PayPro Global.

Please note that all of our products are delivered via ESD (Electronic Software Delivery) only. After purchase you will be able to immediately download the registration keys. Also you will receive a copy of registration keys by email. Please make sure to enter a valid email address in your order. If you have not received the keys within 2 hours, please, contact us at sales@sqlmanager.net.

To obtain MORE INFORMATION on this product, visit us at https://www.sqlmanager.net/products/mysql/dataimport

Product distribution | PayPro Global
EMS Data Import for MySQL (Business license) + 1-Year Maintenance* | Buy Now!
EMS Data Import for MySQL (Business license) + 2-Year Maintenance*
EMS Data Import for MySQL (Business license) + 3-Year Maintenance*
EMS Data Import for MySQL (Non-commercial license) + 1-Year Maintenance*
EMS Data Import for MySQL (Non-commercial license) + 2-Year Maintenance*
EMS Data Import for MySQL (Non-commercial license) + 3-Year Maintenance*
EMS Data Import for MySQL (Trial version) | Download Now!

*EMS Maintenance Program provides the following benefits:

  • Free software bug fixes, enhancements, updates and upgrades during the maintenance period
  • Free unlimited communications with technical staff for the purpose of reporting Software failures
  • Free reasonable number of communications for the purpose of consultation on operational aspects of the software
  • Access to personalized sqlmanager.net account
  • online, speed-through maintenance renewal
  • Advanced and exclusive notification of software promotions
  • "Maintenance Owner ONLY" product promotions

After your maintenance expires, you will not be able to update your software or get technical support. To protect your investments and have your software up-to-date, you need to renew your maintenance.

You can easily reinitiate/renew your maintenance with our online, speed-through Maintenance Reinstatement/Renewal Interface. After reinitiating/renewal you will receive a confirmation e-mail with all the necessary information.

See also:

How to register Data Import


How to register Data Import

To register your newly purchased copy of EMS Data Import for MySQL, perform the following:

  • receive the notification letter from PayPro Global with the registration info;
  • enter the Registration Name and the Registration Key from this letter;
  • make sure that the registration process has been completed successfully – check the registration information at the startup page.

See also:

How to buy Data Import


Version history

Version | Release date
Version 4.0.0 | June 6, 2022
Version 3.8.3 | November 28, 2018
Version 3.8.2 | August 30, 2017
Version 3.8.1 | April 14, 2016
Version 3.8 | March 10, 2016
Version 3.7 | June 09, 2014
Version 3.6 | July 03, 2012
Version 3.5 | November 11, 2011
Version 3.4 | August 20, 2010
Version 3.3 | November 30, 2009
Version 3.2 | February 03, 2009
Version 3.1 | September 26, 2007
Version 3.0 | March 7, 2007

Full version history is available at https://www.sqlmanager.net/products/<%DV_SERVER_LINK%>2/dataimport/news

Version 4.0.0

  • Added support for dark theme.
  • Added SSL settings.
  • Unicode object names support.
  • Updated SSH tunneling library with support for ECDSA and Ed25519 keys and the "Keyboard-interactive" authentication method.
  • Improved support for the latest server versions.
  • Added data preview for MS Access, DBF and XML Data packet formats.
  • Optimized work with XML, DOCX, XLSX, XLS, ODS, ODT files.
  • In some cases, the console version shut down with a critical error. Fixed.
  • Other fixes and improvements.

Version 3.8.3

  • The DATAFILE parameter in the console version now supports multiple files.
  • The order of files was reversed on executing the template in the console version. Fixed now.
  • File incoding for TXT files was not saved correctly in the template. Fixed now.
  • In some cases the app process was not closed after import. Fixed now.
  • The "Range check error" error on importing big CSV files has been fixed.
  • MySQL. Now data are imported correctly in CSV import mode with LOAD DATA INFILE statement.
  • Other minor bug-fixes and improvements.

Version 3.8.2

  • Processing of big files has been considerably improved.
  • Script execution option has been added.
  • ‘Out of Memory’ error fixed on importing XML, XLS and XLSX files.
  • The console version was occasionally freezing on template usage. Fixed now.
  • The error on importing MS Excel 2016 files has been fixed.
  • DBF. Type mapping has been improved for new tables.
  • VARCHAR fields were truncated in some cases on import from CSV files. Fixed now.
  • NULL values are processed correctly now.
  • Import from XLSX additional sheets is supported correctly now.
  • DATE values are imported correctly from DBF files now.
  • Scripts with quotes were not saved into templates correctly. Fixed now.
  • Search for MS SQL servers in the network has been improved.
  • Other minor improvements and bugfixes.

Version 3.8.1

  • Support of the latest version of OpenSSH added.
  • Unicode data from XLSX files is imported correctly now.
  • Data was not imported to tables containing $ symbol. Fixed now.
  • Connection via SSH with Private key failed. Fixed now.
  • The error on importing some XLSX files have been fixed.

Version 3.8

  • XML parser has been updated. All XML, Microsoft Office and Open Office documents are processed and imported considerably faster now.
  • Importing of big XML files resulted in 'Out of memory error'. Fixed now.
  • XLSX files having UNICODE data and settings for font and color were imported incorrectly. Fixed now.
  • Incorrect Native mode import when using Varchar fields as key fields has been fixed.
  • MySQL version allows working with case sensitive table names now.
  • A few errors with converting data types have been fixed.
  • Lots of other improvements and bug-fixes.

Version 3.7

  • Import rate is increased due to use of multithreading.
  • XML. Added the possibility of manual and automated identification of the type of the imported XML-file.
  • Now log contains information about the string number where the error occurred.
  • When importing from several files their logs are located at different tabs, while the main log contains general information about these processes.
  • DBF. Wrong identification of Integer type fields. Fixed now.
  • DBF. National characters were imported incorrectly. Fixed now.
  • "yyyy-mm-ddThh:nn:ss" format was recognized incorrectly. Fixed now.
  • HTML. When importing from HTML file with the links the application froze. Fixed now.
  • CSV. Errors in Native mode - CSV insert. Fixed now.
  • Other improvements and bugfixes.

Version 3.6

  • Now the imported field value can be replaced with the following values: current date, current time, current date and time, the full name of the imported file, the short name of the imported file.
  • Now each imported field value can be processed using MS JavaScript statements.
  • Now the console versions of Data Import use a localization selected in the GUI-version.
  • Now when importing to a new table, the length of string fields is calculated.
  • Added the "Import Empty Rows" option. If this option is not selected, empty rows are not imported.
  • Added the "Auto Trim Values" option. It allows to trim leading and trailing whitespace from the imported values.
  • The data from HTML files could not be imported. Fixed now.
  • Other improvements and bugfixes.

Version 3.5

  • Added the possibility to rollback the transaction, if an error occurs.
  • The interface of the third step for all types of the imported files is uniformed now.
  • Import console. Now the import process information is displayed after certain time intervals.
  • Data Import Wizard. Now the import process information is displayed after certain time intervals.
  • Now localizations are loaded when the language directory is changed.
  • On opening a new field editor is filled in with default values now.
  • Now when importing to a new table, it is possible to determine the length of the string field by default.
  • Excel 2007. Added the possibility to configure Ranges when importing from Excel 2007, the same as importing from Excel 2003.
  • Excel 2007. Import speed is increased.
  • XML Data packet. Added the possibility to select the file encoding.
  • Access. Added the dialog for entering a username.
  • XML. Importing to a new table caused an AV error. Fixed now.
  • Excel. Using the range of rows in Map caused the incorrect data import. Fixed now.
  • Excel. When parsing a file with a field containing the SUM function with one argument, an error occurred. Fixed now.
  • Excel. When opening the Map Range editor, the data in it displayed incorrectly. Fixed now.
  • Data Import for MySQL. Fixed the critical bug in the Native mode.
  • Other improvements and bugfixes.

Version 3.4

  • Added the support of Microsoft Office Access 2007 files.
  • The work with data is more convenient now. Templates for basic date formats are added.
  • Now when importing from an Access file to a new table, the structure of any Access table from a file is created.
  • Added the support of complicated date templates: mmm dd yyyy hh:nn:ss:zzzAP or yyyy-mm-ddThh:nn:ss.zzz
  • Now it is possible to set BigInteger (64bit, 19 Decimal digits) values for the generator at step 5 of the utility.
  • Some other improvements and bugfixes.

Version 3.3

  • Added the possibility to automatically form fields when importing from TXT, CSV and XML Document formats to a new table.
  • Now the user can choose the necessary encoding, including UTF8, when importing from DBF files.
  • It is now possible to map more than 256 columns when importing from MS Excel 2007 (the maximum number is 16384, it is Excel2007 restriction).
  • The console version now forms a more informative log file.
  • Some visual changes are introduced; the sizes of forms/form parts are saved now.
  • Added the possibility to save settings of the previous import procedure: formats, import mode, etc.
  • Some other improvements and bugfixes.

Version 3.2

  • Added the possibility to create separate tables for each source data file. The table structure is built on the basis of the source data analysis.
  • It is now possible to specify source data charset when importing TXT and CSV files.
  • The ability to set correspondences between source data and existing table fields is implemented. This correspondence is based on the identity of the source columns' and destination table fields' names.
  • Added the possibility to choose the file character set (None, OEM, ANSI) when importing data from DBF files.
  • The "Automatically reconnect when connection is lost" option is added.
  • Using "-log" parameter in console versions used to result in error. Fixed now.
  • Other minor improvements and bugfixes.

Version 3.1

  • Added an ability to import data from the text of embedded nodes of XML documents
  • Added an ability to explicitly specify the data file for the console version using the -datafile=<source file name> parameter. Now you can use the same template file for import from several source data files of the same type
  • Added auto-recognition of columns and auto fill of table fields for TXT files
  • Wizard window is now resizable
  • The field type is now displayed for DBF files
  • In some cases an error occurred upon import of DATE data in the Native mode CSV Insert. Fixed now
  • Other minor improvements and bug-fixes

Version 3.0

Basic features:

  • Implemented the new "Native" method of import which includes two algorithms – Single Commands and Batch Insert; the Single Commands mode is used to generate and execute single SQL commands on the server, whereas the Batch Insert mode uses native commands for a particular server: BULK INSERT (Microsoft® SQL Server™), LOAD DATA INFILE (MySQL), COPY (PostgreSQL). With the help of Native mode your data can be imported dozen (!) times faster as compared to the same operation performed in the Universal mode which was used in the previous versions
  • Unicode support: now you can import Unicode data
  • Import from new formats is available now: MS Excel 2007, MS Word 2007, HTML, Generic XML, Open Document format (Open Document Text and Open Document Spreadsheet)
  • The source code of the utility has been significantly optimized, running speed increased
  • Added an ability to import data from text files containing TAB symbols as separators

and other minor improvements and bug-fixes.

Scroll to top

See also:

What's new


EMS Data Import FAQ

Please read this page attentively if you have questions about Data Import for MySQL.

Table of contents

  • What is EMS Data Import for MySQL?
  • What do I need to start working with EMS Data Import for MySQL?
  • What is the difference between the Import feature of SQL Manager for MySQL and the Data Import for MySQL utility?
  • Are there any limitations implied in the trial version as compared with the full one?
  • What is the easiest way to configure the template files for the Data Import console application?

Question/answer list

Q: What is EMS Data Import for MySQL?

A: EMS Data Import for MySQL is a powerful tool to import your data quickly from MS Access, MS Excel, DBF, TXT, CSV, XML, MS Excel 2007, MS Word 2007, HTML, ODF files to MySQL tables. It provides adjustable import parameters, including source data formats for all the fields and destination data formats for the selected fields, commit options, number of records to skip, etc. Data Import includes a wizard which allows you to set all the import options for different files visually, and a command-line utility to execute import in one-touch.

Q: What do I need to start working with EMS Data Import for MySQL?

A: First of all, you must have a possibility to connect to some local or remote MySQL server to work with Data Import. You can download MySQL database server from https://www.mysql.com/downloads/. Besides, you need your workstation to satisfy the system requirements for Data Import for MySQL.

Q: What is the difference between the Import feature of SQL Manager for MySQL and the Data Import for MySQL utility?

A: First of all, Data Import for MySQL works faster as it is a considerably lighter application. Besides, it provides additional features for query building, namely:

  • import data to several tables simultaneously;
  • import data to tables selected from different databases;
  • the command-line version of the utility to import data using the configuration (template) file with all import settings;
  • automatically creates table structure.

Q: Are there any limitations implied in the trial version as compared with the full one?

A: Actually the trial version of the utility only allows you to import 20% of records into each table. With this limitation, you can test all the features implemented in Data Import for MySQL within the 30-day trial period.

Note: There is a 100 records (instead of 20%) limitation when importing from CSV, TXT or MS Access files.

Q: What is the easiest way to configure the template files for the Data Import console application?

A: You can configure the template files for each table visually using the Data Import Wizard application. Set the required options and select the Tools | Save Template menu item. All the options will be saved to the template file which can be used by the console application.

Scroll to top

If you still have any questions, contact us at Support Center.


Other EMS Products

Quick navigation

MySQL

Microsoft SQL Server

PostgreSQL

InterBase / FireBird

Oracle

IBM DB2

Tools & components

MySQL

 | SQL Management Studio for MySQL EMS SQL Management Studio for MySQL is a complete solution for database administration and development. SQL Studio unites the must-have tools in one powerful and easy-to-use environment that will make you more productive than ever before!
 | SQL Manager for MySQL Simplify and automate your database development process, design, explore and maintain existing databases, build compound SQL query statements, manage database user rights and manipulate data in different ways.
dataexport | Data Export for MySQL Export your data to any of 20 most popular data formats, including MS Access, MS Excel, MS Word, PDF, HTML and more.
dataimport | Data Import for MySQL Import your data from MS Access, MS Excel and other popular formats to database tables via user-friendly wizard interface.
datapump | Data Pump for MySQL Migrate from most popular databases (MySQL, PostgreSQL, Oracle, DB2, InterBase/Firebird, etc.) to MySQL.
datagenerator | Data Generator for MySQL Generate test data for database testing purposes in a simple and direct way. Wide range of data generation parameters.
dbcomparer | DB Comparer for MySQL Compare and synchronize the structure of your databases. Move changes on your development database to production with ease.
extract | DB Extract for MySQL Create database backups in the form of SQL scripts, save your database structure and table data as a whole or partially.
query | SQL Query for MySQL Analyze and retrieve your data, build your queries visually, work with query plans, build charts based on retrieved data quickly and more.
datacomparer | Data Comparer for MySQL Compare and synchronize the contents of your databases. Automate your data migrations from development to production database.
 | Scroll to top

Microsoft SQL Server

 | SQL Management Studio for SQL Server EMS SQL Management Studio for SQL Server is a complete solution for database administration and development. SQL Studio unites the must-have tools in one powerful and easy-to-use environment that will make you more productive than ever before!
 | EMS SQL Backup for SQL Server Perform backup and restore, log shipping and many other regular maintenance tasks on the whole set of SQL Servers in your company.
sqladmin | SQL Administrator for SQL Server Perform administrative tasks in the fastest, easiest and most efficient way. Manage maintenance tasks, monitor their performance schedule, frequency and the last execution result.
 | SQL Manager for SQL Server Simplify and automate your database development process, design, explore and maintain existing databases, build compound SQL query statements, manage database user rights and manipulate data in different ways.
dataexport | Data Export for SQL Server Export your data to any of 20 most popular data formats, including MS Access, MS Excel, MS Word, PDF, HTML and more
dataimport | Data Import for SQL Server Import your data from MS Access, MS Excel and other popular formats to database tables via user-friendly wizard interface.
datapump | Data Pump for SQL Server Migrate from most popular databases (MySQL, PostgreSQL, Oracle, DB2, InterBase/Firebird, etc.) to Microsoft® SQL Server™.
datagenerator | Data Generator for SQL Server Generate test data for database testing purposes in a simple and direct way. Wide range of data generation parameters.
dbcomparer | DB Comparer for SQL Server Compare and synchronize the structure of your databases. Move changes on your development database to production with ease.
extract | DB Extract for SQL Server Create database backups in the form of SQL scripts, save your database structure and table data as a whole or partially.
query | SQL Query for SQL Server Analyze and retrieve your data, build your queries visually, work with query plans, build charts based on retrieved data quickly and more.
datacomparer | Data Comparer for SQL Server Compare and synchronize the contents of your databases. Automate your data migrations from development to production database.
 | Scroll to top

PostgreSQL

 | SQL Management Studio for PostgreSQL EMS SQL Management Studio for PostgreSQL is a complete solution for database administration and development. SQL Studio unites the must-have tools in one powerful and easy-to-use environment that will make you more productive than ever before!
 | EMS SQL Backup for PostgreSQL Creates backups for multiple PostgreSQL servers from a single console. You can use automatic backup tasks with advanced schedules and store them in local or remote folders or cloud storages
 | SQL Manager for PostgreSQL Simplify and automate your database development process, design, explore and maintain existing databases, build compound SQL query statements, manage database user rights and manipulate data in different ways.
dataexport | Data Export for PostgreSQL Export your data to any of 20 most popular data formats, including MS Access, MS Excel, MS Word, PDF, HTML and more
dataimport | Data Import for PostgreSQL Import your data from MS Access, MS Excel and other popular formats to database tables via user-friendly wizard interface.
datapump | Data Pump for PostgreSQL Migrate from most popular databases (MySQL, SQL Server, Oracle, DB2, InterBase/Firebird, etc.) to PostgreSQL.
datagenerator | Data Generator for PostgreSQL Generate test data for database testing purposes in a simple and direct way. Wide range of data generation parameters.
dbcomparer | DB Comparer for PostgreSQL Compare and synchronize the structure of your databases. Move changes on your development database to production with ease.
extract | DB Extract for PostgreSQL Create database backups in the form of SQL scripts, save your database structure and table data as a whole or partially.
query | SQL Query for PostgreSQL Analyze and retrieve your data, build your queries visually, work with query plans, build charts based on retrieved data quickly and more.
datacomparer | Data Comparer for PostgreSQL Compare and synchronize the contents of your databases. Automate your data migrations from development to production database.
 | Scroll to top

InterBase / Firebird

 | SQL Management Studio for InterBase/Firebird EMS SQL Management Studio for InterBase and Firebird is a complete solution for database administration and development. SQL Studio unites the must-have tools in one powerful and easy-to-use environment that will make you more productive than ever before!
 | SQL Manager for InterBase/Firebird Simplify and automate your database development process, design, explore and maintain existing databases, build compound SQL query statements, manage database user rights and manipulate data in different ways.
dataexport | Data Export for InterBase/Firebird Export your data to any of 20 most popular data formats, including MS Access, MS Excel, MS Word, PDF, HTML and more
dataimport | Data Import for InterBase/Firebird Import your data from MS Access, MS Excel and other popular formats to database tables via user-friendly wizard interface.
datapump | Data Pump for InterBase/Firebird Migrate from most popular databases (MySQL, SQL Server, Oracle, DB2, PostgreSQL, etc.) to InterBase/Firebird.
datagenerator | Data Generator for InterBase/Firebird Generate test data for database testing purposes in a simple and direct way. Wide range of data generation parameters.
dbcomparer | DB Comparer for InterBase/Firebird Compare and synchronize the structure of your databases. Move changes on your development database to production with ease.
extract | DB Extract for InterBase/Firebird Create database backups in the form of SQL scripts, save your database structure and table data as a whole or partially.
query | SQL Query for InterBase/Firebird Analyze and retrieve your data, build your queries visually, work with query plans, build charts based on retrieved data quickly and more.
datacomparer | Data Comparer for InterBase/Firebird Compare and synchronize the contents of your databases. Automate your data migrations from development to production database.
 | Scroll to top

Oracle

 | SQL Management Studio for Oracle EMS SQL Management Studio for Oracle is a complete solution for database administration and development. SQL Studio unites the must-have tools in one powerful and easy-to-use environment that will make you more productive than ever before!
 | SQL Manager for Oracle Simplify and automate your database development process, design, explore and maintain existing databases, build compound SQL query statements, manage database user rights and manipulate data in different ways.
dataexport | Data Export for Oracle Export your data to any of 20 most popular data formats, including MS Access, MS Excel, MS Word, PDF, HTML and more.
dataimport | Data Import for Oracle Import your data from MS Access, MS Excel and other popular formats to database tables via user-friendly wizard interface.
datapump | Data Pump for Oracle Migrate from most popular databases (MySQL, PostgreSQL, MySQL, DB2, InterBase/Firebird, etc.) to Oracle
datagenerator | Data Generator for Oracle Generate test data for database testing purposes in a simple and direct way. Wide range of data generation parameters.
dbcomparer | DB Comparer for Oracle Compare and synchronize the structure of your databases. Move changes on your development database to production with ease.
extract | DB Extract for Oracle Create database backups in the form of SQL scripts, save your database structure and table data as a whole or partially.
query | SQL Query for Oracle Analyze and retrieve your data, build your queries visually, work with query plans, build charts based on retrieved data quickly and more.
datacomparer | Data Comparer for Oracle Compare and synchronize the contents of your databases. Automate your data migrations from development to production database.
 | Scroll to top

IBM DB2

 | SQL Manager for DB2 Simplify and automate your database development process, design, explore and maintain existing databases, build compound SQL query statements, manage database user rights and manipulate data in different ways.
dataexport | Data Export for DB2 Export your data to any of 20 most popular data formats, including MS Access, MS Excel, MS Word, PDF, HTML and more.
dataimport | Data Import for DB2 Import your data from MS Access, MS Excel and other popular formats to database tables via user-friendly wizard interface.
datapump | Data Pump for DB2 Migrate from most popular databases (MySQL, PostgreSQL, Oracle, MySQL, InterBase/Firebird, etc.) to DB2
datagenerator | Data Generator for DB2 Generate test data for database testing purposes in a simple and direct way. Wide range of data generation parameters.
extract | DB Extract for DB2 Create database backups in the form of SQL scripts, save your database structure and table data as a whole or partially.
query | SQL Query for DB2 Analyze and retrieve your data, build your queries visually, work with query plans, build charts based on retrieved data quickly and more.
 | Scroll to top

Tools & components

advancedexport | Advanced Data Export for RAD Studio VCL Advanced Data Export for RAD Studio VCL allows you to save your data in the most popular office programs formats.
advancedexport | Advanced Data Export .NET Advanced Data Export .NET is a component for Microsoft Visual Studio .NET that will allow you to save your data in the most popular data formats for the future viewing, modification, printing or web publication. You can export data into MS Access, MS Excel, MS Word (RTF), PDF, TXT, DBF, CSV and more! There will be no need to waste your time on tiresome data conversion - Advanced Data Export will do the task quickly and will give the result in the desired format.
advancedimport | Advanced Data Import for RAD Studio VCL Advanced Data Import for RAD Studio VCL will allow you to import your data to the database from files in the most popular data formats.
pdfgenerator | Advanced PDF Generator for RAD Studio Advanced PDF Generator for RAD Studio gives you an opportunity to create PDF documents with your applications written on Delphi or C++ Builder.
querybuilder | Advanced Query Builder for RAD Studio VCL Advanced Query Builder for RAD Studio VCL is a powerful component for Delphi and C++ Builder intended for visual building SQL statements for the SELECT, INSERT, UPDATE and DELETE clauses.
excelreport | Advanced Excel Report for RAD Studio Advanced Excel Report for RAD Studio is a powerful band-oriented generator of template-based reports in MS Excel.
advancedlocalizer | Advanced Localizer for RAD Studio VCL Advanced Localizer for RAD Studio VCL is an indispensable component for Delphi for adding multilingual support to your applications.
 | Scroll to top


Wizard application

Data Import for MySQL wizard application provides easy-to-use wizard interface to set all data import parameters visually.

Working with wizard application

Using configuration files

Setting program preferences

See also:

Console Application


Working with wizard application

Follow the steps of the wizard to import data to MySQL tables for your needs.

Getting started

Step 1 - Setting connection properties

Step 2 - Selecting files to import

Step 3 - Setting XML file type

Step 4 - Mapping fields

Step 5 - Setting base data formats

Step 6 - Setting data formats for each field

Step 7 - Specifying import mode

Step 8 - Selecting key columns to exclude from import process

Step 9 - Setting common options

Step 10 - Defining scripts to execute before and after import

Step 11 - Start of data import process

See also:

Using data import configuration files

Setting program preferences


Getting started

This is how Data Import for MySQL wizard application looks when you first start it.

This page allows you to view registration information. If you have not registered Data Import for MySQL yet, you can do it by pressing the Register button and entering your registration information.

Press the Next button to proceed to Setting connection properties.

See also:

How to buy Data Import


Step 1 - Setting connection properties

At this step you should specify necessary settings to establish connection to the target MySQL database.

Connection settings

For connection you should enter MySQL host name in the Host field. The Host drop-down list contains the list of already registered hosts. For both types you should enter MySQL port to connect through in the Connection port field.

Afterwards you should specify authorization settings: Login and Password.

The default superuser name is 'root' and the default password is empty.

If necessary, use the drop-down list to specify the preferable Client charset to be used by the application.

If you are using the EMS SQL Management Studio for MySQL version of Data Import for MySQL then the Select registered database button is available. Click this button to pick a database already registered in the EMS SQL Management Studio in the Select Host or Database dialog.

Please note that you need to have sufficient privileges to be able to write to the destination database on MySQL server.

Tunneling settings

To setup the connection via SSH tunnel, input the following values in the corresponding fields:

  • SSH host is the name of the host (IP address) where SSH server is running
  • SSH port indicates the port where SSH server is activated (default is "22")
  • SSH login stands for the user on the machine where SSH server is running (Note: it is a Linux/Windows user, not a user of MySQL server)
  • SSH password is the Linux/Windows user password

CheckBox Use Private Key for authentication

If the SSH encryption is enabled on the SSH server, a user can generate a pair of cryptographic keys (the Private key and the Public key). The Public key is placed on the SSH server, and the Private key is the part you keep secret inside a secure box that can only be opened with the correct passphrase (or an empty string as the passphrase). When you wish to access the remote system, you open the secure box with your passphrase (if any), and use the private key to authenticate yourself with the Public key on the remote Linux computer.

SSH Key file

Specify the location (the secure box) of the Private key file on your local machine.

Supported Private Key file formats are:

OpenSSH

Putty

SSH.com

Note that you need to trust your local machine not to scrape your passphrase or a copy of your Private key file while it is out of its secure box.

SSH Private Key - Passphrase

To use HTTP tunneling, just upload the tunneling script to the webserver where MySQL server is located, or to any other webserver from which direct connections to your MySQL server are allowed. This script exposes the MySQL API as a set of web-services which is used by Data Import for MySQL.

Note that the emsproxy.php script file is included into the distribution package and can be found in Data Import installation directory.

NOTE: The Tools button gets access to saving/loading import templates or adjusting program Preferences.

When you are done, press the Next button to proceed to the next step.


Selecting registered database

Use this dialog to select a database for importing data. This dialog is available only in EMS SQL Management Studio version of Data Import for MySQL.

Selecting registered database

All databases registered in EMS SQL Management Studio for MySQL are displayed in the list.

Select the necessary database and click the OK button.

Database registration information will be filled on the first step automatically.


Step 2 - Selecting files to import

At this step you should select source file(s) to be imported.

Click the iconAddFile Add File button to select the source file name using the Open file... dialog. Repeat this operation to add more source files (if necessary).

You can choose among the following types of the source data file:

  • MS Excel
  • MS Excel 2007
  • MS Word 2007
  • MS Access
  • DBF
  • TXT
  • CSV
  • HTML
  • XML Data Packet
  • XML MS Access
  • XML Doc
  • ODS
  • ODT

The open file dialog allows you to set a filter on the source file format.

If you have selected the file of a format that is not supported the Select Import Type dialog appears.

Here you can select which import type for supported file formats should be applied to import the selected file.

To delete a file from the list, select it and click the iconRemoveFile Remove File button.

You can also change the order of the source files in the list using the Up btnMoveUp and the Down btnMoveDown buttons.

Note: For the spreadsheet files: if you need to import data from different sheets into different tables you are to include that multi-sheet spreadsheet file into the list several times (equal to the number of sheets to be imported to different tables).

When a file is selected the Select Table dialog appears:

File name

Displays the name of the imported file and full path.

Database field allows you to specify the target database.

RadioButton Insert into existing table

Select this option to import data to the existing table. The active area contains the list of tables in the selected database. You can refresh the list by clicking the Refresh button.

RadioButton Create new table

Use this option to create import destination table.

To change the target MySQL table that has been already assigned to a source data file, select the table in the list and press the ellipsis btnEllipsis button to call the Select Table dialog again.

When you are done, press the Next button to proceed to the next step.


Step 3 - Setting XML file type

This step appears if you have selected *.xml file for importing data from.

Step 3 - Setting XML file type

XML file type

Select the type of *.xml file from which you are importing data:

RadioButton Auto Detect - file type will be detected automatically whether it is XML MS Access or XML data packet file;

RadioButton XML MS Access - select this option if your *.xml file has the same structure as files exported from MS Access;

RadioButton XML Data Packet - select this option if your *.xml file has data representation format used by Embarcadero;

RadioButton XML Doc File - select this option to map fields of a Generic XML document manually.

When you are done, press the Next button to proceed to the next step.


Step 4 - Mapping fields

This step of the wizard allows you to set correspondence between columns of the source file and fields of the target MySQL table, according to the source data format.

  • MS Excel 97-2003
  • MS Excel mapping
  • MS Word mapping
  • MS Access mapping
  • DBF mapping
  • XML mapping
  • XML Document mapping
  • TXT mapping
  • CSV mapping
  • HTML Document
  • ODS mapping
  • ODT mapping

To get more information about the file formats, see the Supported file formats page.

When you are done, press the Next button to proceed to the next step.


MS Excel 97-2003

Table

Select the sheet for importing data from.

Select the needed source file from the list. Then specify ranges in the grid for the target and source fields:

  • select a field of the target MySQL table in the Fields list;
  • proceed to the sheet grid: click a column caption to select the whole column or click the row number to select the whole row;
  • the selected column/row of the source file gets green highlight, and a new range indicating the source and target fields correspondence appears in the Range list;
  • repeat the operation for all the fields you need to be included in the import process.

If the source file and the destination MySQL table have the same order of columns, you can use the btnAutoFillColumnsXT Auto Fill Columns mode (default) to set correspondence between them automatically. If source file and destination MySQL table have different order of columns but identical names you can use the btnAutoFillColumnsXT Auto Fill by Captions mode to set the correspondence based on name's identity. You can also use the btnAutoFillRows Auto fill Rows to set the correspondence between destination table fields and source file rows.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

If necessary, you can choose to skip a defined number of the source file columns and/or rows using the Skip Col(s) and Skip Row(s) spin-edits.

Step 3 - Excel

If your spreadsheet file contains several sheets, you are able to set different mapping for each sheet.

To clear ranges for a field, select the field in the Fields list and press the btnClearFieldRanges Clear button.

To clear all ranges specified for the target table fields, press the btnClearAllRanges Clear All button.

To set a range of data to be imported from the file, use the btnAddRange Add range button.

To remove a range, use the btnDeleteRange Delete range button.

Using the btnMoveUp Move Up and the btnMoveDown Move Down buttons you can change the order of ranges applied to data.

Click the Next button to proceed to the Setting base data formats step of the wizard.


MS Excel

Table

Select the sheet for importing data from.

Select the needed source file from the list. Then specify ranges in the grid for the target and source fields:

  • select a field of the target MySQL table in the Fields list;
  • proceed to the sheet grid: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

If the source file and the destination MySQL table have the same order of columns, you can use the btnAutoFillColumnsXT Auto Fill Columns mode (default) to set correspondence between them automatically. If source file and destination MySQL table have different order of columns but identical names you can use the btnAutoFillColumnsXT Auto Fill by Captions mode to set the correspondence based on name's identity.

Note: The number of the column for which correspondence was set is displayed at the Col. control. You can use it for setting the fields correspondence as well.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

If necessary, you can choose to skip a defined number of the source file rows or columns using the Skip lines or Skip cols spin-edit.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Step 3 - Excel 2007

To remove a correspondence, select the field in the Fields list and press the btnClearFieldRanges Clear Field Ranges button.

To remove all correspondences, press the btnClearAllRanges Clear All Ranges button.

If your spreadsheet file contains several sheets, you are able to set different mapping for each sheet.

To clear ranges for a field, select the field in the Fields list and press the Clear btnClearFieldRanges button.

To clear all ranges specified for the target table fields, press the Clear All btnClearAllRanges button.

To set a range of data to be imported from the file, use the Add range btnAddRange button.

To remove a range, use the Delete range btnDeleteRange button.

Using the Move Up btnMoveUp and the Move Down btnMoveDown buttons you can change the order of ranges applied to data.

Click the Next button to proceed to the Setting base data formats step of the wizard.


MS Word

Specify ranges in the grid for the target and source fields:

  • select a field of the target MySQL table in the New fields list;
  • proceed to the sheet grid: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

If the source file and the destination MySQL table have the same order of columns, you can use the btnAutoFillColumnsXT Auto Fill Columns mode (default) to set correspondence between them automatically. If source file and destination MySQL table have different order of columns but identical names you can use the btnAutoFillColumnsXT Auto Fill by Captions mode to set the correspondence based on name's identity.

Table

In case the file contains several tables select the require one from the dropdown menu.

The number of the column which correspondence is set for are displayed at the Grid Column column. You can use it for setting the fields correspondence as well.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

If necessary, you can choose to skip a defined number of the source file rows using the Skip lines spin-edit.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Step 3 - Word 2007

To remove a correspondence, select the field in the New fields list and press the btnClearFieldRanges Clear button.

To remove all correspondences, press the btnClearAllRanges Clear All button.

Click the Next button to proceed to the Setting base data formats step of the wizard.


MS Access

Switch between existing Access table and custom Access SQL query to retrieve data from.

Set correspondence between the source MS Access fields and the target MySQL table fields:

  • select a field of the target MySQL table in the Table fields list;
  • proceed to the sheet grid: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

If you choose a query as the data source, you also can load a SQL query from a *.sql file or save the current query text to a file using the Load from File... and the Save to File... buttons correspondingly.

Use the Auto Fill Columns button to set correspondence between the source and target fields automatically on the basis of their order.

Step 3 - Access

To remove a correspondence, select the field in the Fields list and press the Clear button.

To remove all correspondences, press the Clear All button.

Click the Next button to proceed to the Setting base data formats step of the wizard.


DBF

Set correspondence between the source DBF columns and the target MySQL table fields:

  • select a field of the target MySQL table in the Table Fields list;
  • proceed to the grid: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Use the Auto Fill Columns button to set correspondence between the source and target fields automatically on the basis of their order.

Charset

Use this field to specify the source file character set.

CheckBox Skip deleted rows

Use the option to exclude records marked as deleted in source DBF file.

Step 3 - DBF

To remove a correspondence, select the field in the Fields list and press the Clear button.

To remove all correspondences, press the Clear All button.

Click the Next button to proceed to the Setting base data formats step of the wizard.


XML

Set correspondence between the source XML columns and the target MySQL table fields:

  • select a field of the target MySQL table in the Table Fields list;
  • proceed to the grid: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Use the Auto Fill Columns button to set correspondence between the source and target fields automatically on the basis of their order.

Encoding

Use this field to select the file encoding.

Step 3 - XML

To remove a correspondence, select the field in the Fields list and press the Clear button.

To remove all correspondences, press the Clear All button.

Click the Next button to proceed to the Setting base data formats step of the wizard.


XML Document

In order to set mapping of a Generic XML document, you should first select the desired XML Document in the Files list. The tree-like structure of source document is displayed in the area located to the right from the New fields list. Select a node in the tree to get its relative path or type the path manually in the editor below (the path must be specified in the XPath format). Upon pressing the Fill grid button the grid gets filled with Sub Nodes Text or Attributes values of the selected node.

Note: if the source XML document contains huge amount of data, building the tree may take a long time.

Set correspondence between the source XML file columns and the target MySQL table fields:

  • select a field of the target MySQL table in the Fields list;
  • proceed to the source grid viewer area: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

You can use the btnAutoFillColumns Auto Fill Columns button to set correspondence between the source and target fields automatically according to their order (mapping is started from the first attribute value in this case).

When you import data to a newly created table, the Analyze button appears. Use this button for automatic field creation.

The number of the column which correspondence is set for are displayed at the Grid Column column. You can use it for setting the fields correspondence as well.

If necessary, you can choose to skip a defined number of the source file lines using the Skip Lines spin-edit.

Step 3 - XML Document

To remove all correspondences, press the btnClearAllRanges Clear All button.

If you choose to import data to the newly created table, at this step you need to create necessary fields. Use the corresponded context menu item to manage fields.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Click the Next button to proceed to the Setting base data formats step of the wizard.


TXT

Set correspondence between the source text file columns and the target MySQL table fields:

  • select a field of the target MySQL table in the Fields list;
  • double-click in the text viewer area to add vertical separators delimiting the source column bounds;
  • click the area between the separators to assign the column to the selected target table field - the selected source column gets black highlight;
  • repeat the operation for all the fields you need to be included in the import process.

The Fields list also allows you to view the following values:

Pos represents the starting point of your selection;

Length displays the width of the selected area.

You can change these parameters manually or by moving the slider in the grid.

If necessary, you can choose to skip a defined number of the source file lines using the Skip lines spin-edit.

If the source text file and the destination MySQL table have the same order of columns, you can use the btnAutoFillColumns Auto Fill Columns button to set correspondence between them automatically.

The source file character set can be defined at the Encoding field.

CheckBox Auto scroll

This option scrolls the document automatically when you switch to the next field for mapping.

When you import data to a newly created table, the Analyze button appears. Use this button for automatic field creation.

Step 3 - TXT

To remove all correspondences, press the btnClearAllRanges Clear All button.

If you choose to import data to the newly created table, at this step you need to create necessary fields. Use the corresponded context menu item to manage fields.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Click the Next button to proceed to the Setting base data formats step of the wizard.


CSV

Set correspondence between the target table fields and the source CSV file columns:

  • select the Delimiter and Quote characters for CSV files using the corresponding drop-down lists of the CSV Parameters group;
  • select a field of the target MySQL table in the Fields list;
  • proceed to the source grid viewer area: click a caption to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

The source file character set can be defined at the Encoding field.

If the source file and the destination MySQL table have the same order of columns, you can use the btnAutoFillColumnsXT Auto Fill Columns mode (default) to set correspondence between them automatically. If source file and destination MySQL table have different order of columns but identical names you can use the btnAutoFillColumnsXT Auto Fill by Captions mode to set the correspondence based on name's identity.

When you import data to a newly created table, the Analyze button appears. Use this button for automatic field creation.

If necessary, you can choose to skip a defined number of the source file rows using the Skip lines spin-edit.

The number of the column which correspondence is set for are displayed at the Grid Column column. You can use it for setting the fields correspondence as well.

Step 3 - CSV

To remove all correspondences, press the btnClearAllRanges Clear All button.

If you choose to import data to the newly created table, at this step you need to create necessary fields. Use the corresponded context menu item to manage fields.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Click the Next button to proceed to the Setting base data formats step of the wizard.


HTML

Set correspondence between the target table fields and the source HTML file columns:

  • select a field of the target MySQL table in the New fields list;
  • proceed to the source grid viewer area: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

If the source file and the destination MySQL table have the same order of columns, you can use the btnAutoFillColumnsXT Auto Fill Columns mode (default) to set correspondence between them automatically. If source file and destination MySQL table have different order of columns but identical names you can use the btnAutoFillColumnsXT Auto Fill by Captions mode to set the correspondence based on name's identity.

The number of the column which correspondence is set for are displayed at the Grid Column column. You can use it for setting the fields correspondence as well.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

Table number

Select the number of the table for retrieving data from the dropdown menu. Can be used if the HTML file consists more than one table.

If necessary, you can choose to skip a defined number of the source file rows using the Skip lines spin-edit.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Step 3 - HTML

To remove all correspondences, press the btnClearAllRanges Clear All button.

Click the Next button to proceed to the Setting base data formats step of the wizard.


ODS

The OpenDocument format is used by Mobile Office, as well as other well-known desktop applications, such as OpenOffice, StarOffice and KOffice. ODS stands for OpenDocument Spreadsheet (*.ods).

Specify ranges in the grid for the target and source fields:

  • select a field of the target MySQL table in the New fields list;
  • proceed to the sheet grid: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

If the source file and the destination MySQL table have the same order of columns, you can use the btnAutoFillColumnsXT Auto Fill Columns mode (default) to set correspondence between them automatically. If source file and destination MySQL table have different order of columns but identical names you can use the btnAutoFillColumnsXT Auto Fill by Captions mode to set the correspondence based on name's identity.

The number of the column which correspondence is set for are displayed at the Grid Column column. You can use it for setting the fields correspondence as well.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

Table

Select the sheet for importing data from.

If necessary, you can choose to skip a defined number of the source file rows using the Skip line(s) spin-edit.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Step 3 - ODS

To remove a correspondence, select the field in the New fields list and press the btnClearFieldRanges Clear button.

To remove all correspondences, press the btnClearAllRanges Clear All button.

If your OpenDocument Spreadsheet file contains several sheets, you are able to set different mapping for each sheet.

Click the Next button to proceed to the Setting base data formats step of the wizard.


ODT

The OpenDocument format is used by Mobile Office, as well as other well known desktop applications such as OpenOffice, StarOffice and KOffice. ODT stands for OpenDocument Text (.odt).

Table

Select the sheet for importing data from.

Specify ranges in the grid for the target and source fields:

  • select a field of the target MySQL table in the New fields list;
  • proceed to the Table grid: click a column to assign the column to the selected target table field;
  • the selected column of the source file gets gray highlight;
  • repeat the operation for all the fields you need to be included in the import process.

If the source file and the destination MySQL table have the same order of columns, you can use the btnAutoFillColumnsXT Auto Fill Columns mode (default) to set correspondence between them automatically. If source file and destination MySQL table have different order of columns but identical names you can use the btnAutoFillColumnsXT Auto Fill by Captions mode to set the correspondence based on name's identity.

Table

In case the file contains several tables select the require one from the dropdown menu.

The number of the column which correspondence is set for are displayed at the Grid Column column. You can use it for setting the fields correspondence as well.

If new table creation was selected at the previous step, then fields needed for the import procedure will be created automatically.

You can manage destination table fields if needed. Use the corresponded context menu item to New/Edit/Drop field.

If necessary, you can choose to skip a defined number of the source file rows using the Skip line(s) spin-edit.

Note: If table was properly created or already exists, it will be marked with a tick Tick . If an error occurs during table creation, this table will be marked with a cross [image] .

Step 3 - ODT

To remove a correspondence, select the field in the Fields list and press the btnClearFieldRanges Clear button.

To remove all correspondences, press the btnClearAllRanges Clear All button.

If your OpenDocument Text file contains several tables, you are able to set different mapping for each table.

Click the Next button to proceed to the Setting base data formats step of the wizard.


Step 5 - Setting base data formats

This step of the wizard provides a number of options for setting base formats for each source data file. The specified format should match the source data representation.

Separators

These options set the separator for the source file to ensure correct import. Make sure that they strongly correspond to the source file data.

Decimal

Set a character to delimit the decimal parts of the imported numbers.

Thousand

Set a character to separate the digit groups in the imported numbers.

Date

Set a character to separate the year, month and day parts of date values.

Time

Set a character to separate the hour, minute and second parts of time values.

Constants

Set the values as they are represented in the source file for correct recognition.

Boolean True

Set one or more variants of TRUE value representation in the imported table, e.g. 'Yes' or '+'. Use a new line for each additional option.

Boolean False

Set one or more variants of FALSE value representation in the imported table, e.g. 'No' or '-'. Use a new line for each additional option.

Null Values

Set one or more variants of NULL value representation in the imported table, e.g. 'Null'. Use a new line for each additional option.

Date/Time formats

Use these fields to set date and time formats so that they strongly correspond to the source file format for correct processing of data.

For more information refer to the Format specifiers page.

NOTE: Date and Time formats only reflect data format view, not separators. Separators must be set in the Separators group above.

CheckBox Skip this step

Check this option to skip the current step in the future. To edit the list of skipped steps, use the Skipped Steps group available in the General section of the Preferences dialog.

When you are done, press the Next button to proceed to the next step.


Format specifiers

The following format specifiers are supported in the format string:

Float/Integer format

0

Digit place holder. If the value being formatted has a digit in the position where the '0' appears in the format string, then that digit is copied to the output string. Otherwise, a '0' is stored in that position in the output string.

#

Digit placeholder. If the value being formatted has a digit in the position where the '#' appears in the format string, then that digit is copied to the output string. Otherwise, nothing is stored in that position in the output string.

.

Decimal point. The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored.

,

Thousand separator. If the format string contains one or more ',' characters, the output will have thousand separators inserted between each group of three digits to the left of the decimal point. The placement and number of ',' characters in the format string does not affect the output, except to indicate that thousand separators are wanted.

E+

Scientific notation. If any of the strings 'E+', 'E-', 'e+', or 'e-' are contained in the format string, the number is formatted using scientific notation. A group of up to four '0' characters can immediately follow the 'E+', 'E-', 'e+', or 'e-' to determine the minimum number of digits in the exponent. The 'E+' and 'e+' formats cause a plus sign to be output for positive exponents and a minus sign to be output for negative exponents. The 'E-' and 'e-' formats output a sign character only for negative exponents.

Date/Time format

c

Displays the date using the format using the Short Date Format, followed by the time using the Long Time Format. The time is not displayed if the date-time value indicates midnight precisely.

d

Displays the day as a number without a leading zero (1-31).

dd

Displays the day as a number with a leading zero (01-31).

ddd

Displays the day as an abbreviation (Sun-Sat) using the strings of the Short Day Names.

dddd

Displays the day as a full name (Sunday-Saturday) using the strings of the Long Day Names.

ddddd

Displays the date using the Short Date Format.

dddddd

Displays the date using the Long Date Format.

e

Displays the year in the current period/era as a number without a leading zero (Japanese, Korean and Taiwanese locales only).

ee

Displays the year in the current period/era as a number with a leading zero (Japanese, Korean and Taiwanese locales only).

g

Displays the period/era as an abbreviation (Japanese and Taiwanese locales only).

gg

Displays the period/era as a full name. (Japanese and Taiwanese locales only).

m

Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.

mm

Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.

mmm

Displays the month as an abbreviation (Jan-Dec) using the strings given of the Short Month Names.

mmmm

Displays the month as a full name (January-December) using the strings of the Long Month Names.

yy

Displays the year as a two-digit number (00-99).

yyyy

Displays the year as a four-digit number (0000-9999).

h

Displays the hour without a leading zero (0-23).

hh

Displays the hour with a leading zero (00-23).

n

Displays the minute without a leading zero (0-59).

nn

Displays the minute with a leading zero (00-59).

s

Displays the second without a leading zero (0-59).

ss

Displays the second with a leading zero (00-59).

z

Displays the millisecond without a leading zero (0-999).

zzz

Displays the millisecond with a leading zero (000-999).

t

Displays the time using the Short Time Format.

tt

Displays the time using the Long Time Format.

am/pm

Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly.

a/p

Uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. The a/p specifier can use lower, upper, or mixed case, and the result is displayed accordingly.

ampm

Uses the 12-hour clock for the preceding h or hh specifier, and displays the contents of the TimeAMString global variable for any hour before noon, and the contents of the TimePMString global variable for any hour after noon.

/

Displays the date separator character using the Date Separator.

:

Displays the time separator character using the Time Separator.

'xx'/"xx"

Characters enclosed in single or double quotes are displayed as-is, and do not affect formatting.


Step 6 - Settings data formats for each field

This step of the wizard allows you to set formats for each imported field separately, in case additional formatting is required.

Select a field in the list and use the Field Customization group to adjust format options that will be applied to this field only: generator value, generator step, constant value, NULL value, default value, function, script, left/right quotation, quotation action, character case, character set.

Generator value

Use this field to set the initial value of the autoincrement field.

Generator step

Set the step of the autoincrement field. If it is 0 or if the field is of Identity one, then the value of the generator will be ignored.

Constant value

Use this field to set a constant value for the field.

Null value

Set the value which will be considered as NULL to set the default value.

Default value

Set the default value of the NULL field.

Function

Use the drop-down list to select one of the function return values that can be set in the field: Date, Time, Date&Time, Long File Name, Short File Name.

Script

Press the btnEllipsis button to add/edit the script in the Script Editor.

Step 5 - Script Editor

Use the Script Editor window to enter the script for the field processing. Here you can use all features of the MS JScript.

The picture above shows the example of the Jscript use for the field Name processing. The entered code converts the 'XXXX YYYY' string to the 'YYYY, XXXX' one, for example 'Piotr Tchaikovsky' -> 'Tchaikovsky, Piotr'.

If you want to test your code and see the result of its execution then enter the test field value in the Field Value For Testing box and click the Test Run button.

If the execution fails you see the message box with the description of an occurred mistake.

If the execution succeeds you see the message box with the result as it is shown on the picture below.

Step 5 - Script Editor - Information

Quotation

Left quotation

Set a character or a number of characters to denote quoting in the imported string.

Right quotation

Set a character or a number of characters to denote unquoting in the imported string.

Quotation action

You can select the Add item to add quotation marks to the imported string, the Remove item to remove all the quotation marks from the imported string, or the None item to save the original quotation marks.

String conversion

Char case

Set the case of the imported string. As Is keeps the original string unchanged, Upper sets the whole string to the upper case, Lower sets the whole string to the lower case, UpperFirst sets the first character of the string to the upper case, UpperFirstWord sets the first character of each word to the upper case.

Char set

Set the char set of the imported string to ANSI or OEM. As Is saves the original character set of the string.

The Replacements area allows you to set the text you need to be replaced during data import into the selected field. Press the Add Replacement... btnAddRange button to specify a new replacement options using the Add Replacement dialog.

Define the text to replace and the value to replace with in the appeared dialog window. Check the Ignore Case option to make replacement case-insensitive.

Step 5 - Add Replacement

To remove a replacement, select it in the list and click the Delete Replacement... btnDeleteRange button.

CheckBox Skip this step

Check this option to skip the current step in the future. To edit the list of skipped steps, use the Skipped Steps group available in the General section of the Preferences dialog.

When you are done, press the Next button to proceed to the next step.


Step 7 - Specifying import mode

This step of the wizard allows you to define the records processing mode as Insert All, Insert New, Update, Update or Insert, Delete, Delete or Insert mode.

Import Mode

  • Insert all: all records from the source file are inserted into the tables irrespective of whether any records exist in the destination table or not
  • Insert new: already existing records are skipped, and new records are inserted into the destination table
  • Update: all existing records are updated from the source file
  • Update or insert: already existing records are updated and new records are inserted into the destination table
  • Delete: already existing records are deleted
  • Delete or insert: existing records are deleted and new records are inserted into the destination table

Here is an example of some import modes offered by Data Import utility:

DB Table | Source file
Step 6 - tab1 | Step 6 - tab2

All import modes (except for the Insert All mode) are based on primary key values information. In order to perform import operations with these modes used you need to have matches between the source file primary key column(s) and the destination table primary key column(s).

Insert mode | Insert all | Insert new | Update
Result | Step 6 - tab3 | Step 6 - tab4 | Step 6 - tab5

Insert mode | Update or insert | Delete | Delete or insert
Result | Step 6 - tab6 | Step 6 - tab7 | Step 6 - tab8

It is applied to all other import modes, except for the Insert all mode. For all these modes (except for the Insert all mode) it is obligatory to select the primary key fields. This field (or fields) is used as key field to identify specific data in the target database.

The key columns for these operations are defined at the Selecting key columns step of the wizard.

If the Update value is selected for Import Mode, then you need to specify the Primary Key field(s) that will serve for data identification of your source file with the data of the target table. The source file must contain the column(s) that will correspond to the Primary Key field of the target table. If the target table contains a record in which the value of the primary key field coincides with the value of the corresponding column of the source table, the data stored in this table record will be updated. You can specify the field(s) which will be used for identification at Step 7 of the wizard. This field(s) will only serve for identification and will not be imported.

Native mode kind

The Native mode of uses the Single Commands method that serves to generate and execute single SQL commands on the server. If necessary, you can use the Batch Insert mode to import a data set as a batch. In the CSV Insert mode data are inserted as a set of comma-separated values.

The Duplicate solution group defines handling of imported rows that duplicate existing rows by Unique key values:

RadioButton Replace

Imported rows replace existing rows. In other words, the rows that have the same value for a Primary key or Unique index as existing rows are replaced.

RadioButton Ignore

Imported rows that duplicate existing rows by a Unique key value are skipped.

Temporary File Name

Type in or use the btnSaveAs button to specify the name and path to the temporary file which stores data to be inserted in the CSV Insert mode.

CheckBox Skip this step

Check this option to skip the current step in the future. To edit the list of skipped steps, use the Skipped Steps group available in the General section of the Preferences dialog.

When you are done, press the Next button to proceed to the next step.


Step 8 - Selecting key columns

This step of the wizard allows you to select the fields of the table to be used as the key fields for the import process.

This step is not available if the RadioButton Insert all option has been selected on the previous step.

To select a field, you need to move it from the Available Columns list to the Selected Columns list. Use the btnMoveToSelected btnMoveAllToSelected btnMoveToAvailable btnMoveAllToAvailable buttons or drag-and-drop operations to move the fields from one list to another.

CheckBox Skip this step

Check this option to skip the current step in the future. To edit the list of skipped steps, use the Skipped Steps group available in the General section of the Preferences dialog.

When you are done, press the Next button to proceed to the next step.


Step 9 - Setting common options

Using this step of the wizard you can set final import options.

Commit

CheckBox Commit when done

Check this option to commit the transaction after all records are imported.

Commit after each ... records

These control allows you to define the number of records in each block to be supplemented with the COMMIT statement.

CheckBox Rollback on error

Check this option to rollback the transaction if an error occurred.

Record Count

RadioButton Import all records

Specifies that all records of the source file will be imported.

RadioButton Import only ... record(s)

Specifies the number of records to be imported.

CheckBox Import empty rows

If this option is checked then empty records are imported.

CheckBox Auto trim values

Check this function to remove all leading and trailing white-space characters from all imported records.

CheckBox Save Result SQL Script to File

This option allows you to save the result SQL script of the import operation to an external *.sql file on your disk drive.

Specify the script file name and select its location using the btnSaveAs button which calls the Save As... dialog.

CheckBox Skip this step

Check this option to skip the current step in the future. To edit the list of skipped steps, use the Skipped Steps group available in the General section of the Preferences dialog.

When you are done, press the Next button to proceed to the next step.


Step 10 - Defining scripts

This step of the wizard allows you to define scripts to be executed before and after import process for each database.

Select a database to define the script for. Type the text of the script to be executed before the import operation in the Before Import Script area, the script to be executed after the import operation in the After Import Script area.

Select another database and add scripts for it, if necessary. By default, the After Import Script is not executed if import fails. If you need to execute it anyway, use the Execute after import script on import fail option at Step 11.

You can also save and load Before Import and After Import scripts using the corresponding Save... and Load... buttons.

CheckBox Skip this step

Check this option to skip the current step in the future. To edit the list of skipped steps, use the Skipped Steps group available in the General section of the Preferences dialog.

When you are done, press the Next button to proceed to the last step.


Step 11 - Start of data import process

This step of the wizard is intended to inform you that all import options have been set, and you can start the import process. If everything is correct, press the Import button to start the process. If you want to change something, you can return to any of the wizard steps using the Back button.

CheckBox Continue if before import script is executed with errors

Check this option to ignore errors in Before Script execution during import.

CheckBox Execute after import script on import fail

Check this option to execute After import script, specified at Step 10, not depending on import result. Otherwise the script is not executed if import fails.

Max import threads

Set the value from 0 to 64 to adjust import performance.

Save log to file

This button calls the Save file dialog which allows you to save the on-screen log to a file.

Clear log

Pressing this button clears the on-screen log area, removing all messages.

If necessary, you can save a template for future use.

Click the Import button to start the import process.


Using data import configuration files

Data Import for MySQL allows you to store its configuration settings in external *.itm template files if you need to perform the data import process repeatedly.

You can load previously saved configuration settings to the application wizard if you need to make some changes before data comparison, or you can run it with the console application for quicker data import.

Data Import templates are saved/loaded within the Save template options/Load template dialog. To open this dialog, press the Tools button and select the Save template/Load template popup menu item.

Using configuration files - Save Template

File name

When saving template, specify the template file name and select its location using the btnSaveAs button which calls the Save Template As... dialog.

Comment

The comment field of the template description.

Using configuration files - Save Template options

If you need to repeat data importing process with the same or similar settings later, it is reasonable to save all the settings you entered on the Start of data import process step of the Wizard.

To quickly return to Step 1, use the Restart Wizard button, which is only available at the last step.

Please note that loading a template is only available at the Getting started and the Setting connection properties steps of the Wizard.

If necessary, you can Save template at any step of the wizard using the corresponding popup menu item of the Tools menu.

Using configuration files - Reopen Template

See also:

Working with wizard application

Setting program preferences


Setting program preferences

Data Import for MySQL provides full customization of the program interface by setting various options within the Preferences dialog. This chapter is intended to inform you how to use all these options.

Setting general options

These options define general behavior of Data Import for MySQL.

Skipped steps

Selecting steps to skip by default.

Selecting program language

This page allows you to select a language to be applied for your copy of Data Import for MySQL.

Preferences - General

Reset

Click this button to set all the program settings to default.

See also:

Working with wizard application

Using data import configuration files


General

General

Theme

Select the main color theme for the application: Light or Dark.

CheckBox Confirmation on exit

Enables/disables confirmation upon exiting the program.

CheckBox Key columns reminder

Use this option to enable/disable prompts to define key columns.

Preferences - General

CheckBox Remember password

Setting this option allows you to save passwords used for access to the databases automatically upon closing the application. Please note that checking this option saves the latest password used for connection to the database (including the SSH server password).

CheckBox Show number of created tables

Use this option to allow/disallow appearance of the correspondent information window.

CheckBox Show number of mapped columns

Displays the number of mapped fields on proceeding to Step 4.

CheckBox Use first row as caption fields

Enable this option to use first rows as captions when mapping fields.

CheckBox Keep options at steps

If this option is checked all your import settings will remain the same the next time the program is started.

CheckBox Allow JavaScript execution

If this option is checked then the script entered on the Step 6 is always executed.

CheckBox Show databases

This option enables/disables the 'Select Database' drop-down list in 'Select Table' dialog at Step 2 of the wizard. If you check this option, you can select the database from the list, otherwise you must type the database name manually.

CheckBox Exclude autoincrement fields

If this option is checked then autoincrement fields will not be mapped on auto mapping.

Default min length for new string field

Specify the default length of the new field in case you are importing data into a new table.

Default CSV delimiter

Specify the default delimiter for CSV files format.

Default CSV quote

Specify the default quottas for CSV files format.

See also:

Selecting program language

Skipped steps


Skipped steps

Use this option group to define the Wizard application steps to be skipped.

Preferences - Skipped steps

CheckBox Skip intermediate steps

This option determines the behavior of the Next and Back buttons. When this option is disabled, clicking the Next button leads to sequential passing through the list of imported objects. When this option is enabled, the steps checked in the Skipped steps group will be skipped.

CheckBox Base Formats

Setting base data formats

CheckBox Data Formats

Settings data formats for each field

CheckBox Import Mode

Specifying import mode

CheckBox Key Columns

Selecting key columns

CheckBox Commit Options

Setting common options

CheckBox Scripts

Defining scripts

See also:

Setting general options

Languages


Languages

The Languages page is provided for managing Data Import localization files.

You can create your own *.lng files similar to those available in the %program_directory%\Languages folder, add them to the list of available languages and set the new language as the program interface language.

The table lists all the languages available for localization and the corresponding *.lng files.

Preferences - Localization

Language Directory

Use the ellipsis btnEllipsis button to specify the directory where the *.lng files are stored by default.

See also:

Setting general options

Skipped steps


Console application

Additionally to the GUI version which is implemented in the form of a wizard application, the installation package of Data Import for MySQL includes the console version which is intended for being run from Windows command line with a template file name used as the execution parameter.

Data Import for MySQL command line utility is intended for quick and powerful data import to MySQL tables.

Working with console application

Configuration file format

See also:

Wizard Application


Working with console application

All the import options are set in template (*.itm) files. A template can be also used in the Console version of Data Import for MySQL.

To create a template file, follow the instructions below:

  • start Data Import Application wizard;
  • set all the required options in all steps of the wizard;
  • test the import process at the last step;
  • save all import options in the template.

The easiest way to start Data Import for MySQL console application is to double-click the generated *.itm template. The other way is to enter the command line and type the appropriate command.

Usage:

<path to Data Import for MySQL console application>\MyImportC.exe TemplateFile [-datafile=<source file name>] [-B] [-LOG]

datafile=<filename1>;<filename2>;<filename3>

Indicates the source file(s) for data import. The values you specify replace the names of the files in the template. The number of files should strongly correspond to the number of files in the template. Multiple files are separated with a semicolon.

TemplateFile

Stands for the *.itm template file to be used as the console version execution parameter

[-B]

Use this parameter in the command line to run the console version of Data Import for MySQL in the background mode.

Example:

"C:\Program Files\EMS\Data Import for MySQL\MyImportC.exe" "C:\EMS\DataImport\Template1.itm" -B -datafile=test150283.xls

[-LOG]

Console application also generates the *.log file that contains information about the import operation completion.

Example:

"C:\Program Files\EMS\Data Import for MySQL\MyImportC.exe" "C:\EMS\DataImport\Template1.itm" -LOG="C:\EMS\DataImport\logfile.log"

Note: The following exit codes can be returned by Data Import for MySQL to the operating system after performing the latest task:

0 - successful completion;

1 - error(s) occurred during task performing;

2 - fatal error occurred. The task was not performed.

See also:

Working with wizard application

Configuration file format


Configuration file format

The configuration file is divided into several sections.

[Connection]

This section contains parameters for connecting to the server: Host, Port, Charset, tunneling settings, Login, Password (encrypted). These parameters are obligatory.

[PREFERENCES]

This section contains the general settings of the utility.

ImportToViews

1 - enabled, 0 - disabled

The following sections are unique for each imported file and contain the appropriate postfix (FILE_00, FILE_01, FILE_02, etc.)

In these sections the import options for each particular file are stored.

[FILE_00]

In the main file section the following parameters are used:

Database (destination database name), Table (destination table), FileName (the source file name)

ImportType

Indicates the format of the source file: 0 stands for MS Excel, 1 for MS Access, 2 - DBF, 3 - TXT, 4 - CSV, 5 - XML, 6 - MS Excel 2007, 7 - MS Word 2007, 8 - HTML, 9 - XML Document, 10 - ODS, 11 - ODT

The next section is specific to the file format and is named correspondingly, e.g. [FILE_00_XLS]. In this section the following specific file type options are set:

[FILE_00_XLS]

SkipRows, SkipCols - the number of rows and columns in the source file to be skipped on import.

[FILE_00_Access]

SourceType

This option indicates the Access source type - Table or query, 0 stands for table, and 1 for query.

TableName and query - these options set the source Access table name to import from if source type is 0 and the source query text if the source type is 1.

[FILE_00_TXT]

SkipRows - the number of rows in the source file to be skipped on import.

[FILE_00_CSV]

SkipRows - the number of rows in the source file to be skipped on import.

CSVDelimiter, CSVRightQuot, CSVLeftQuot - these options are used on reading the source CSV file, they store options for delimiting columns and the quotation marks.

The next section is [FILE_XX_MAP].

It is also specific to the file type, but is obligatory for all file types. In this section correspondence between the source file columns and the destination table fields is set.

If the source file is an Excel file, then the map is set in the following format:

<DB_FieldName>=CellIndex.

Use semicolon to separate multiple cells. You can define an Excel row or column as a range of cells, e.g.

Field1=A1;A4;A6-A9;B1-F1.

If the source file is an Access or DBF file, then the mapping is set in the following format:

<DB_FieldName>=<Source_Field_Name>.

If the source file is a TXT file, the mapping looks in the following way:

<DB_FieldName>=<Position>;<Size>.

This means that you should provide the starting position and the size of the source file column for each destination field.

To set mapping for a CSV file, provide a column number for each destination field, e.g.

Field1=1

Field2=4

etc.

The properties that are set in sections [FILE_XX_BASE_FORMATS] and [FILE_XX_IMPORT_OPTIONS] correspond to those set on the 'Base Formats' and the 'Options' tabs.

The values that stand for BOOLEAN TRUE and BOOLEAN FALSE values are set in sections [FILE_XX_BOOLEAN_TRUE] and [FILE_XX_BOOLEAN_FALSE].

The NULL values are set in section [FILE_XX_NULL_VALUES]

Data formats that are set for each destination field separately are stored in sections named in the following way:

[FILE_XX_DATA_FORMATS_<FIELD_NAME>],

e.g. FILE_00_DATA_FORMATS_FIELD1.

The properties within these sections also correspond to the properties set on the 'Data Formats' tab of the Settings data formats step for each field.

[FILE_00_IMPORT_OPTIONS]

This section contains parameters that define import options specified on Steps 6-9 of the wizard.

CommitAfterDone

1 - enabled, 0 - disabled

CommitRecCount

The number of records in each block to be supplemented with the COMMIT statement.

ImportRecCount

1 - enabled, 0 - disabled

ImportAllRows

1 - enabled, 0 - disabled

SaveResultSQL

1 - enabled, 0 - disabled

ResultSQLFile

The path to the result *.sql file.

ImportKind

0 - Universal Mode, 1 - Native Mode

TruncateLongString

1 - enabled, 0 - disabled

RollbackIfError

1 - enabled, 0 - disabled

ImportMode

0 - Insert All, 1 - Insert New, 2 - Update, 3 - Update or Insert, 4 - Delete, 5 - Delete or Insert

ImportAddType

1 - enabled, 0 - disabled

AllowDuplicates

1 - enabled, 0 - disabled

NativeModeKind

0 - Single Commands, 1 - Batch insert

[#General#]

This section stores information about the product name and its major version.

See also:

Working with console application


Supported file formats

RadioButton MS Excel 97-2003

The most popular e-table format used by Microsoft® Excel (*.xls). The result files are fully compatible with Microsoft® Excel versions 97-2000, 2003 and XP.

RadioButton MS Access

File of Microsoft® Access format (*.mdb, *.accdb) with an ADO connection used.

RadioButton HTML

Hyper Text Markup Language file format (*.html, *.htm), complete compatibility with HTML 4.0 specification.

RadioButton Text file

Plain text file format (*.txt).

RadioButton CSV file

Comma-Separated Value file format (*.csv).

Note: all the text formats including Text file, CSV are usually used as working or interchange formats.

RadioButton XML

A markup language for documents containing structured information (*.xml).

RadioButton DBF

Database file format (*.dbf)used by dBASE and a number of xBASE applications.

RadioButton MS Excel

The contemporary e-table format used by Microsoft® Excel (*.xlsx). The result files are fully compatible with Microsoft® Excel 2007.

RadioButton MS Word

The contemporary text processing format used by Microsoft® Word (*.docx). The result files are fully compatible with Microsoft® Word 2007.

RadioButton ODF Spreadsheets

OASIS Open Document Format for Office Applications - open document file format for spreadsheets (*.ods) used by a number of applications including OpenOffice.org and KOffice.

RadioButton ODF Text

OASIS Open Document Format for Office Applications - open document file format for word processing (*.odt) documents used by a number of applications including OpenOffice.org and KOffice.


SSH tunneling options

SSH (Secure Shell Host) protocol is used to heighten computer security when working with Unix systems on the Internet. SSH uses several encryption algorithms of different reliability. The spread of SSH is also connected with the fact that a number of Linux-like OS's (for example FreeBSD) include SSH server in their standard integration. To learn more information on this issue, please, visit https://www.openssh.com. SSH tunneling feature of SQL Manager is a means of ensuring secure connection to MySQL servers when working over insecure connection channels. You can also use SSH tunnel to get access to the remote MySQL servers when the default port is closed for external connections due to some reasons. The connection over SSH tunnel works in the following way. First, a connection is established and the process of authentication between SSH client built in SQL Manager and remote MySQL server is performed. Then all incoming and outgoing information between the program and MySQL server is transmitted through SSH server with the help of a communication port (usually it is 22), and SSH server transfers this information directly to MySQL server.


HTTP tunneling options

HTTP tunneling is a method that allows connect to a database and transmit data between the program and a MySQL server through the HTTP/HTTPS protocols using port 80, which is used by a regular internet browser. This method is used to connect to the remote MySQL server of a hosting company when direct connection is not available because of the security reasons. The HTTP tunnel works the following way: all outgoing queries and commands sent by the client's software are encoded and transmitted through the HTTP/HTTPS protocol using port 80 to the special script that decodes the received data, sends it to MySQL server for processing and then sends the result back. This method requires the HTTP server (Apache) and PHP with MySQL to be installed on the remote server. Normally this software is provided by a hosting company that offers Linux hosting solutions. Also you need to upload the special emsproxy.php script to your web-server to access it remotely (you can place it to the folder with your other PHP scripts). If your web-server complies with the requirements and the script is installed correctly, you'll see the message "EmsProxy v 2.3" (version number can be different) in your browser when opening the https://<your_webserver_name>/emsproxy.php page.

To use HTTP tunneling, just upload the tunneling script to the webserver where MySQL server is located, or to any other webserver from which direct connections to your MySQL server are allowed. This script exposes the MySQL API as a set of web-services used by Data Import for MySQL.

In case of using this connection method the response will be slower as compared to the direct connection or the SSH Tunneling method, since the data are XML encoded and HTTP is stateless by nature. However, all the features of Data Import for MySQL are available.

Note that the emsproxy.php script file is included into the distribution package and can be found in Data Import installation directory.

Appendix - HTTP tunneling options


Add/Edit field

When you import data with destination table creation, you can manage this table's fields.

Use the context menu to New/Edit/Drop a colimn.

Selecting New column or Edit column context menu item opens the field editor that allows you to view/edit the properties of the destination table fields.

Use the Column edit box to set the field name. Note that the name of the field must be unique among all the field names in the table.

The Type tab defines the type of the field data.

Size

Defines the size of the field value.

Scale

For numeric and decimal types you need to define the number of decimal to the right of the decimal point.

Default value

Define this option value if you need inserted records to get specified value.

Values

At this field you can define ENUM and SET field's values.

CheckBox Not NULL

Check this option to prevent the entry of NULL or unknown values in column.

CheckBox Autoincrement

Enable this option to generate field values automatically.

CheckBox Use dimension

Enable the option to set the dimension properties for field types having dimension properties (e.g. integer, float, or timestamp data types).

CheckBox Unsigned

Assigns the UNSIGNED attribute to the field: integer data will be displayed unsigned.

CheckBox Zerofill

Assigns the ZEROFILL attribute to the field: for columns of the numeric data type, MySQL will automatically add zeros before the first significant digit of the number stored in this column.

CheckBox Primary key

Check this option to include the field into the primary key. Note that if you include a field to a primary key, you should also make it Not Null.

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces integrity of the table.


Add/Edit Range

To set a range of data to be imported from the file, use the Add range btnAddRange button.

To remove a range, use the Delete range btnDeleteRange button.

To edit an existing range, double-click it in the Ranges list. The Range dialog allows you to set a number of options for the range being edited.

Mapping fields - Excel - Range

Select Range Type from the corresponding drop-down list. Then specify the column, row or cell number whose range is to be defined.

Start and Finish section allows you to set the range of the values to be imported from the specified row or column.

Use the RadioButton From Data Starting and RadioButton While Data Exist options to automatically define the range of the values.

Use Sheet section to define the location of the row/column for which the row was specified.

You can select either RadioButton Default sheet, or RadioButton Custom Sheet.

For custom sheet you need to select Sheet Number or Sheet Name from the appropriate drop-down list.

Note: If Cell was set as Range Type, the only thing you can specify at this dialog is its number.


Advanced connection settings

You need the installed Oracle client on the client computer where Data Import for MySQL will be used. The version of the Oracle client should be compatible with the version of Oracle server you need to connect.

You need to add the connection settings of Oracle server databases to your TNS names file (tnsnames.ora file). This is a configuration file which contains databases description.

If you use Database Client the tnsnames.ora file is located in the %HOME_name\NETWORK\ADMIN directory.

If you use Instant Client for oracle, you should create tnsnames.ora file manually. since it does not exist. File should be created in the same directory where Oracle instant client is installed (e.g. C:\OracleInstantClient\). This file can be created using any text editor (create a simple text file and then change its name and extension).

Only for Instant Client: After the tnsnames.ora file is created and database description is added, create TNS_ADMIN environment variable. For this please do the following:

  1. Right-click 'My computer'.
  2. Select 'Properties' menu item.
  3. Proceed to the 'Advanced' tab and press 'Environment Variables' button.
  4. Press 'New...' button in the 'System variables' section.
  5. Set 'Variable name:' TNS_ADMIN, 'Variable value:' C:\OracleInstantClien\tnsnames.ora
  6. Press 'OK' button to save the variable.

Find PATH variable in the same dialog, double-click it and add path to the Oracle Instant client libraries (they are located in the directory where client is installed, i.e. C:\OracleInstantClient\). Remember that the paths entries should be separated with semicolons ( ; ).

Data Import for MySQL connects to the server (with the help of Oracle client) via TCP/IP protocol. Here is an example of TCP/IP connection specified in TNS names file:

DB_Alias =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = Host_name)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = Database_Name)

)

)

PROTOCOL is the keyword that identifies the specific protocol adapter used. For this protocol, the value is TCP. The value can be entered in either uppercase or lowercase.

HOST is the host name or IP address.

PORT is the TCP/IP port number.

SERVICE_NAME the name of service on server; the database instance name may differ from the actual database name, but generally the names match.

DB_Alias any name of the connection

At the first step select Oracle client HOME in Database home dropdown list and select database from the Database dropdown list. The databases names are taken from the tnsnames.ora file.