Data Merge Wizard

Data Merge Wizard

The Data Merge Wizard allows merging Excel table data into database tables.

You may merge Excel data loaded from web pages, text files, or other sources.

The wizard generates an SQL code to insert new data rows and to update existing data rows.

Database table primary keys are used to verify that a row exists.

In the most cases, such primary keys are natural like emails or mobile phone numbers.
For example, social network contacts can be identified by a row source like LinkedIn, Facebook or GooglePlus, and by an email.

Only common columns of an Excel table and a database table are inserted and updated.

Excel formula columns can be used to make database table columns.
For example, FirstName and LastName database columns can be calculated from the Name source column.

Database tables can contain the MergeData or merge_data datetime fields that are updated by the add-in with the script generation time.
This field allows verifying that a row exists in the latest Excel data table.

You may configure merging data configuring saving changes. In this case, users may use the Save button instead of the wizard.
Also, the custom SQL codes or stored procedures can be used to merge data.

The Data Merge Wizard allows merging Excel data into databases on all supported database platforms:

Microsoft Azure SQL Database, SQL Server, SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, Snowflake, PostgreSQL, and SQLite

Excel Table Requirements

The Data Merge Wizard works with Excel tables early named "Lists".

To run the wizard, any cell of the table to merge data must be selected.

If the wizard is disabled, create an Excel table.

To create an Excel table, select a table region and click the Insert, Table menu item.

Step 1. Selecting Provider

This step is used to select a provider to connect to a database.

The wizard shows all installed providers available for connection.

See specific provider descriptions on the following topics:

Step 2. Connecting to Database

This step is used to connect to a database.

Click the Help button or the Examples link to get the context help during the connecting.

The Next button is enabled when the connection is successful. The wizard tests the connection in the background and activates the button.

If the Next button is disabled, click the Test Connection button to test the connection.

See specific database server descriptions on the following topics:

Step 3. Selecting Target Table

This step is used to select a target database table to merge Excel data.

Step 4. Verifying and Executing Script

This step is used for verifying and executing the publish script.

You may edit the script before the execution.

Click the Execute button to execute the script.

If the script execution has been interrupted by timeout, you may increase the command timeout in the Options dialog box.

The following Excel table is used in the example above:

As mentioned above, if an Excel table does not contain primary key columns then insert commands are generated only.
However, unique records are inserted only, as the records are verified using the join on all fields. See the code above.

If the Excel table contains primary keys (natural keys in most cases), the records are verified using primary keys.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.