Skip to content

Manually setup an Import of data from SQL Server to Excel

← Advanced Setup



Create a new Database Import


NOTE: For a quick start, use the SQL Spreads Designer to create a setup and then modify the Database Import and Database Mapping that was created by the Designer.
  1. In the SQL Spreads tab in Excel, open the Advanced Setup menu and click the Database Import button



  2. This will bring up the Database Imports dialog where you can see your existing Database Imports
  3. Click New, to create a new import


  4. Click in a cell in the spreadsheet to select where the imported data should be inserted


  5. Enter a SQL Query in the Import SQL Query textbox and click Execute to test run your query


  6. When the SQL Query returns the desired results, click OK

  7. In the Manage Database Import dialog, click OK again

  8. Save your Excel document and press Refresh to load the Import Data into your spreadsheet

NOTE: You can create as many Database Imports as you like. 

Using parameters in a Database Import query

There are three types of parameters that you can use in your SQL query:

Current User's Windows Login
Insert the parameter @WindowsUser into your SQL query to insert the current user's Windows Login name. 

The value of the @WindowsUser parameter will be in the format DOMAIN\WINDOWS_LOGIN_NAME

Insert cell values into your SQL query
To insert a value from a cell into your SQL query, you can add a parameter referencing a cell in any Worksheet in the current Workbook.

The parameter is entered in the following format:
@SSCellParameter_SHEET[MySheet]_CELL[A5]  

where A5 is the cell reference and MySheet is the worksheet where to fetch the value.
 
Selected item in a Database Import Filter
Read more under the Database Import Filter section.

Import Settings


Insert Header Row
If checked, SQL Spreads will insert the Column names from the table in SQL Server as header names above the first row with data in Excel.

Clear rows before import
To clear all old data before import, check this checkbox. All cells below the columns in the header row will be cleared.

Formula Rows
There are two ways to add formula rows into a SQL Spreads worksheet. 

The first and easiest way is to add the formulas rows above the Excel table. Then the Formula rows will be independent of how many rows that are fetched from the table.

The second (and a  bit more complex) way, is to add formula rows within a Data Mapping by specifying the row numbers in the corresponding Database Import dialog.

The row numbers are entered in a comma-separated list in the Formula Rows field at the bottom of the Database Import dialog. Row number 1 is the first row below the header row. To preserve the formulas in row 1, 10 and 15 enter the comma-separated list "1,10,15" without quotes.

Formulas in these rows are then kept and the values from the formula are inserted or updated in the database if there is a matching row key.

Minimum number of returned rows
If the returned rows from the database are fewer than the specified value, the imported data will be filled out with blank rows. 

Parameters in Imports
Parameters are used together with Import Data Filters to apply the filter values on the imported data. See the Working with Database Import Filters section for more information.

Feedback and Knowledge Base

(thinking…)