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:
- Load Data (from SQL Database)
- Save Data (into SQL database)
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
- Data Tables, Views or Stored Procedures
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
- Fields - a list of fields you want to select from the table -
- 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
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
Stored Procedure Parameters
If you select a stored procedure from the second drop-down, you can specify parameters:
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.
Save to New Table
You can save data into the new SQL table by checking
Create Table checkbox
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
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
For performance reasons you might want to specify a batch size as well as saving options
- 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.