Skip to main content

WORKSHEETS Excel Add-In

The main objective for WORKSHEETS Excel Add-In is to allow Excel users to easily load and save spreadsheet data to and from the MS SQL databases.

And we wanted to keep it as simple as possible. And we have 2 main operations to show:

  1. Load Data (from SQL Database)
  2. Save Data (into SQL database)

Video Tutorial​

Pre Requirements​

Login/Register​

Before you start you have to log in using the Worksheet Systems account or register https://app.worksheet.systems

SQL Database connections​

To query or to save data into SQL databases you need to connect to the MS SQL Database (2005+, AzureSQL) or any other public, private clouds, or in-premises behind your firewalls. Here is a link to detailed instruction how to connect to the SQL Database and create SQL Connection in WORKSHEETS Data Studio app

When you have Worksheet Systems account and connected to your SQL database, then you can leverage WORKSHEETS Excel AddIn to load data from SQL Database and save data to the SQL database

Load Data (from SQL Database)​

Click on Get Data from the home page and you will see a page with 2 drop-downs

  • Connections
  • Data Tables, Views or Stored Procedures

Docusaurus

As depicted in the screenshot above, you have to define Connection and database object Data Table or View or Stored Procedure

Query Details - for tables or views​

Docusaurus

  • Fields - a list of fields you want to select from the table - test1.SuperstoreSales
  • Top - maximum number of rows to return
  • Filter - filter string e.g. Country = 'UK' etc
  • Order By - Specify fields you want to sort by e.g.: OrderDate DESC

Aggregated query.​

In order to return an aggregated query from the database, you have to add groupBy| prefix to the fields and add aggregation functions e.g. sum(Sales). See thefollowing screenshot

Docusaurus

Stored Procedure Parameters​

If you select a stored procedure from the second drop-down, you can specify parameters:

Docusaurus

Save Data (into SQL database)​

In order to save selected sheet data to the database you have to:

  • Select cells in a sheet. (Select with Headers)
  • Select Destination Table (or Create New Table) in SQL Database
  • Press 'Save Data' button.

Docusaurus

Save to New Table​

You can save data into the new SQL table by checking Create Table checkbox

Docusaurus

We automatically recognize data types and field names.

Save to Existing Table​

In a previous section, we’ve created a new table test1.NewTable1 and we can update the existing table

Docusaurus

From Table / Source Mappings you will notice that we’ve selected fewer fields. So, Source Field is empty for some of them. This is not a problem as long as all Key fields are mapped and empty fields are not nullable

And after we’ve pressed Save button we see we have 9 records inserted and 10 records updated

Saving options​

For performance reasons you might want to specify a batch size as well as saving options

Docusaurus

  • Save (Upsert/Merge) - Based on a primary keys defined in the SQL table. It will update existing records and insert missing one.
  • Save (Append) - Append only
  • Save (Bulk Insert) - Bulk Insert. This is the fastest one, but you have to make sure all constraints and keys are properly defined

Any of those options are quite well performant and can handle large tables, depending on your SQL server setup.