Chapter 3. Publish Wizard

In this chapter, we will publish our table to a database.

This action is required once for every table that you want to have in a database.

Excel Tables

The tables must be an "Excel tables", not regular ranges.

To convert a range table to an Excel table, select a cell in a table, and click Insert, Table.

Excel suggests an entire region. Correct it if needed.
Check My table has headers and click OK.

 

Then locate the table left top cell at cell C3 (Chapter 6 contains details). Now we have the table like this:

Starting Publish Wizard

Now select a cell in the table and run Publish Wizard.

You see wizard step descriptions:

Read and click Next.

Selecting Provider

In the next step, we select a database provider:

You can publish tables to any supported database like SQL Server, Oracle, MySQL, and others.

Select the first provider for SQL Server.

Connecting to Database

In the next step, you have to specify a server, a database, and logon credentials received from your IT guy.

Usually, at the first connection, you have to change the password:

Fill the new password twice and click OK.

Then click Next in the connection form.

Table Design

The add-in suggests a database table structure based on actual data in the published table.

In this example, we need to change the int type of the Sum column to float, and the int type of the Comment column to nvarchar(255) as shown on the next page.

The add-in suggests the correct types for target databases and actual column types in the drop-down list.

Also, tables must have the primary key column or columns that uniquely identify table rows.

In most cases, you can add a column like ID of the integer type.

The add-in does this automatically if it does not find a column with unique values.

You can define primary key columns checking the PK column.

Also, you can add, delete, rename, and reorder columns.

It is a good idea to have column names without spaces. You can use capitalized word parts like CompanyName.

Table Schema and Name

In this step, you specify a table schema and name:

Executing Script

The add-in generates an SQL script to create a table and to insert the existing data:

You can edit the script if you need.

For example, you can delete INSERT rows if you need to create an empty table.

Also, you can create multiple tables changing table names only.

You can change column data types if you do not find it in the drop-down list in the previous step.

Click Execute when you are ready.

Finish Steps

The add-in shows execution results on the next screen.

You can repeat steps if you need. Just click Back.

You can click Cancel if you do not need to insert a new connected table into the workbook.

Now click Finish.

The add-in inserts a connected table to a new worksheet and shows a message about your success:

Editable Table

We see the following screen as a Publish Wizard result:

First of all, you see that the Save button is enabled.

You can change data, add and delete rows. When you are ready to save changes to a database, just click Save.

If you do not want to save changes, just click the Reload button.

Note that you can use the Undo command (Ctrl-Z). The add-in does not disable it, unlike macros.

You can save and close the workbook, and then open it and save the data changes to a database later.

This is useful when you work outside of the corporate network.

 

You see that the add-in inserts a new table on a new worksheet named as Sheet6.

You should know that the add-in creates "very hidden" worksheets that contain configuration data.

You can unhide the add-in sheets using SaveToDB Options, Developer Options, Show SaveToDB Data Sheets.

 

You can see the connected table name in the Active Query field.

 

In our example, let's do the following: rename the Sheet6 to payments, remove the source Sheet1, format the table, and save the workbook as a new workbook as payments.xlsx.

As a result, we have a new workbook with a table connected to a database.