Export To Database

Export to Database function is used to update/insert/replace data in an existing table in the database. This function is only supported for MS SQL and PostGRE.

SheetKraft contains the Export to Database option which can be accessed by the Export Button present in the SheetKraft Toolbar ( See figure)

The following guidelines describe how Export to Database is used.

Step 1

Click on the Export Button and select the Export to Database option to open this dialogue box.

Step 2

Select the data that needs to be exported. There are two ways by which you can select the data: 1)By manually selecting the data from the sheet. 2)By entering the cell indices of the starting and ending block, for example $A$1:$I$800.

You can select Change sheet if you want to select data from some other sheet. Also, click on the My data has headers checkbox if you have selected the headers with the data.

After selecting the data, click on OK.

Step 3

The dialogue box shown below, opens:

Click on the browse button next to Connection, to configure the database connection. If the Configuration File is already setup as explained in Desktop & Server Configurations, then this step can be skipped.

Data

Step 4

A window for configuring connection appears.

Select a database system from the Database Drop-Down box and the fields corresponding to the particular database system appears. For example, if SQL Server database is selected, the fields Data Source, Integrated Security, User ID, Password and Initial Catalog appears. You can enter these fields individually or a connection string can be entered in the Connection String field. If you are entering the fields individually the connection string gets updated automatically.

After entering the connection string you can test if the connection details are correct by clickinng on Test. Click OK after entering the connection details.

Step 5

After entering the connection settings, select the table name by clicking on the browse button next to the Table Name under Database Settings.

The database tables are listed in the format <SchemaName>.<TableName>.

Step 6

After selecting the database table, the columns in the database will be loaded.

Under Columns in Range, the column names in the data range will be listed. And corresponding to each column there is a checkbox and a dropdown window. If a particular column is needed to be exported to database the checkbox should be checked. The dropdown window contains all the columns in the database. For each column in the data range to be exported, select the corresponding column in database. If the name of the database column and the data range column is the same, the columns will be mapped automatically when the database is selected. Here only the column "Country of Incorporation" is not mapped to a database column, since the names mismatch. In such cases, if the column needs to be exported, the database column is selected manually and the the checkbox is also checked.

Under Columns in Database, the column names in the database will be listed. And corresponding to each column there are two checkboxes: Output and Key. If a particular column is the primary key then Key checkbox should be checked. If a particular column need to be reported as a part of the result, the Output checkbox should be checked.

Step 7

After selecting the columns to be exported and mapping the data range and database columns, select the operation to perform from the dropdown window.

Operation Description

UpdateOrInsert Updates the database rows in which the Primary Keys are matching with the data rows and inserts new rows into the database table if the data rows don't have matching Primary Keys. The existing rows in the database which don't match with the data rows will be retained.

TableReplace Replaces the entire database table data with the new data.

BatchReplace Replaces the database rows that matches with Batch Key.

InsertAll Inserts new rows into the database table if the data rows don't have matching Primary Keys. But if there are the database rows for which the Primary Keys are matching with the data rows, operation fails with error.

InsertNew Similar to InsertAll. But ignores if there are the database rows for which the Primary Keys are matching with the data rows.

UpdateAll Updates the database rows in which the Primary Keys are matching with the data rows. But if the data rows don't have matching Primary Keys, operation fails with an error.

UpdateExisting Similar to UpdateAll. But ignores if the data rows don't have matching Primary Keys.

Step 8

In the Predeccessor field enter the range which contains the function which should be executed first, if any.

Step 9

Click on Next. You will be asked where to save the result. To save the data in the Existing WorkSheet, you can input the index of the starting cell where you want to copy the data. Or you can save the data in a New Worksheet.

Step 10

After entering your choice, click Finish.