Data Publish Wizard

Data Publish Wizard

Preface

The Publish Wizard allows:

  • Designing and creating a database table based on Microsoft Excel table.
  • Exporting the Excel table data to the created database table.
  • Inserting a new Excel table connected to the created database table.
  • Applying base table formatting and formulas to the new Excel table.

After publishing data the regular SaveToDB functionality reload-edit-save can be used.

Other users can connect to the published table too.

This is the fastest way to get the real multi-user work with data in Microsoft Excel.

After the publishing, the Table Format Wizard can be used to save the table format and formulas in a database for distributing to other users.

The created table can be republished, for example, for changing a table structure.

However, the publishing is not a substitute for the regular mode of saving data changes that requires one click only.

See also the Data Merge Wizard that allows merging Excel table data to existing database tables.

The Publish Wizard allows publishing data on all supported database platforms:

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

Excel Table Requirements

The Publish Wizard works with Excel tables early named "Lists".

To run the wizard, any cell of the table to publish 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.

Wizard Steps

The wizard includes the following steps:

  1. Selecting a provider.
  2. Connecting to a database.
  3. Designing a destination database table.
  4. Defining a destination table schema and name.
  5. Verifying and executing the publish script.
  6. Verifying script results.
  7. Creating a new Excel table connected to the created database table.

Example Comments

The wizard applies base table formatting and formulas to a new table.

The following Excel table is used in this article to show desired features:

The table includes columns of all Excel basic types: integer, double, datetime, string, and boolean.

The table also contains the formula column that gets values of the Float column.

Microsoft SQL Server is used in the example.

Important!

The result Excel table contains the additional first column of row numbers (_RowNum in English version) used for saving data changes.

SaveToDB inserts the table at one column left, if possible, to preserve existing column positions.

Accordingly, insert at least one column left before the publishing. Otherwise, the table columns will be shifted.

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 connection.

See specific database server descriptions on the following topics:

Step 3. Designing Destination Table

This step is used to design the destination table.

Using the designer you can:

  • Add and delete destination table columns.
  • Change the column order.
  • Change the destination table column names.
  • Change the destination table column types.
  • Change primary key columns.

The Database Data Type column contains the types applicable to the source column types.
For example, if the source column contains integers only then only integer compatible SQL types are available.

The wizard selects the most proper types, but you should check and change the types if necessary.

The number in brackets after the database data type is the data length for variable length types and the data type storage size for fixed length data types.

4-byte real and float data types are not used because Microsoft Excel imports wrong values for these types.

The table must have at least one primary key (PK) column. Primary key columns must have no empty values.

If an integer column is selected as a single primary key column, then the field is created with the IDENTITY property.

If a GUID column is selected as a single primary key column, then the field is created with a GUID generation property.

The properties for IDENTITY and GUID columns are supported on all database platforms.

Step 4. Defining Table Schema and Name

This step is used to define the schema and name of the destination table.

The Table Schema field contains schemas with user's ALTER permission that allows creating new tables.
A new schema can be created if a user has CREATE SCHEMA permission.

The Table Name field contains existing tables of the selected schema.

Important! If the destination table exists, then it will be dropped before the creation of a new table.

Step 5. Verifying and Executing Script

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

You may edit the script before the execution.

However, know what you do because the wizard does not control your changes.

Useful tips:

  • You may change the data types if necessary.
  • You may copy the script and send it to your database administrator to create the table and import the data.

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.

Step 6. Verifying Script Results

The script execution results are shown in the window.

Click the Finish button to create an Excel table connected to the created database table.

Step 7. Creating New Excel Table Connected to Created Database Table

When the Finish button is clicked, the following actions are performed:

  • Creating a new worksheet.
  • Inserting a new table connected to the created database table.
  • Copying formula columns to the new table from the source table.
  • Applying formats from the source table.

Pay attention that column A is hidden. The column contains row numbers used for saving data changes. Source table columns have the same column numbers; so, you can easily transfer used source formulas.

The result message is displayed at the end:

Useful Tips

You may edit new table data: change cells, add and delete rows. Then you may save the changes back to a database using the Save button.

Other users can connect to the table using their workbooks and can save changes also.

Use the Table Format Wizard to save the table format and formulas in a database, and other users get the same table as you including formats and formulas.