Export to Database

Note: This function is only supported for MS SQL and PostGRE.

Update or Insert

Goal: Setup a workbook formula that pushes the data into the database (use the given connection string).

Input: Download table with data from here .

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product"

Hint:

  1. Use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Search for the given table by clicking on Table Name [horizontal dotted button].

  3. Select the relevant Operation.

  4. Check the columns to be exported.

  5. You can also check the output checkbox (for any column) if you want see the uploaded data.



Create Table

Goal: Create a table using Database explorer where "Company", "Currency" and "Product" are primary keys.

Input: Download table structure with data from here .

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Schema: "dbo"

Table: "product"

Hint:

  1. On SheetKraft ribbon, click on the Database Explorer tab.

  2. Select the Database Type as SqlServer

  3. Connect to the relevant datasource using above given connection string.

  4. Select the data range, for which table structure is need to be constructed.

  5. Click on Infer Columns (SheetKraft will read and interpret relevant details), make the relevant columns primary key.

  6. Provide the Schema and Table name and then click on Generate SQL

  7. After this, in the same UI click on SQL tab below the Connection String bar and in that click on Run Non-Query.


Replace Table

Goal: Setup a workbook formula that replaces the existing table data in the database (use the given connection string).

Input: Download table with data from here.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product"

Hint:

  1. Use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Search for the given table by clicking on Table Name [horizontal dotted button].

  3. Select the relevant Operation.

  4. Check the columns to be exported.

  5. You can also check the output checkbox (for any column) if you want see the uploaded data.

Batch Replace

Goal: Setup a workbook formula that pushes the data into the database using BatchReplace operation(use the given connection string).

Input: Download table with data from here.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product_batchkey"

Hint:

  1. Use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Search for the given table by clicking on Table Name [horizontal dotted button].

  3. Select the relevant Operation.

  4. Check the columns to be exported.

  5. You can also check the output checkbox (for any column) if you want see the uploaded data.



Insert All

Goal: Setup a workbook formula that pushes the data into the database using InsertAll operation(use the given connection string).

Input: Download table with data from here.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product"

Hint:

  1. Use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Search for the given table by clicking on Table Name [horizontal dotted button].

  3. Select the relevant Operation.

  4. Check the columns to be exported.

  5. You can also check the output checkbox (for any column) if you want see the uploaded data.

  6. If some data already exist in the table then an error will occur and no output will be generated



Insert New

Goal: Setup a workbook formula that pushes the data into the database using InsertNew operation(use the given connection string).

Input: Download table with data from here.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product"

Hint:

  1. Use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Search for the given table by clicking on Table Name [horizontal dotted button].

  3. Select the relevant Operation.

  4. Check the columns to be exported.

  5. You can also check the output checkbox (for any column) if you want see the uploaded data.

  6. If some data already exist in the table then an Ignore will be shown and rest of the data will be pushed into the database.



Update All

Goal: Setup a workbook formula that pushes the data into the database using UpdateAll operation(use the given connection string).

Input: Download table with data from here.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product"

Hint:

  1. Use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Search for the given table by clicking on Table Name [horizontal dotted button].

  3. Select the relevant Operation.

  4. Check the columns to be exported.

  5. You can also check the output checkbox (for any column) if you want see the uploaded data.

  6. If some data already exist in the table then an error will occur and no output will be generated.

Update Existing

Goal: Setup a workbook formula that pushes the data into the database using UpdateExisting operation(use the given connection string).

Input: Download table with data from here.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product"

Hint:

  1. Use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Search for the given table by clicking on Table Name [horizontal dotted button].

  3. Select the relevant Operation.

  4. Check the columns to be exported.

  5. You can also check the output checkbox (for any column) if you want see the uploaded data.

  6. If some data already exist in the table then an Ignore will be shown and rest of the data will be updated and pushed into the database.

Predecessor

Goal: Setup a workbook formula that pushes the data into the database and then append more data in the same table(use the given connection string).

Input: Download table with data from here.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product"

Hint:

  1. Use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  2. Search for the given table by clicking on Table Name [horizontal dotted button].

  3. Select the relevant Operation, eg: "Table Replace".

  4. Check the columns to be exported.

  5. You can also check the output checkbox (for any column) if you want see the uploaded data.

  6. Now, select the other data that need to be processed after the first export.

  7. Use the ExportToDatabase.SK UI again and click on the Predecessor checkbox and provide a link to the formula of the first Export to database.

  8. Now, in the same table push the data (use the "Update or Insert" operation).

Column Addition

Goal: Add a column in the defined table in database and then export the data in the modified table.

Input: Download table with additional column and data from here.

Connection String: Data Source=(local)\SQLEXPRESS;Integrated Security=True;User ID=SheetKraft;Initial Catalog=testdb1

Table Name: "dbo"."product"

Hint:

  1. Open SQL Management server, and connect with the local database.

  2. In the left column Object Explorer, go to Databases -> testdb1 -> Tables -> dbo.product, right click on dbo.product and then select Design.

  3. A query window will open with columns Column Name, Data Type and Allow Nulls

  4. In the empty row at the bottom, enter the details for the new column.

  5. Save the query.

  6. Open the excel containing the data, use the UI ExportToDatabase.SK, connect to the relevant datasource using above given connection string.

  7. Search for the given table by clicking on Table Name [horizontal dotted button].

  8. Select the relevant Operation, eg: "Table Replace".

  9. Check the columns to be exported.

  10. You can also check the output checkbox (for any column) if you want see the uploaded data.