Excel to SQL Server Add-In
The Benefits
Manage and update centralized data through Excel
For organizations using Microsoft SQL Server, who need to create solutions where end-users can manage and update their SQL Server data through Excel, SQL Spreads is an Excel to SQL Server Add-In that lets you:
- easily create solutions to manage and update SQL Server data using Microsoft Excel,
- improve data quality by utilizing automatic validation and integrity checks, and
- enable data owners to reach their centralized data through Excel.
Well-known Excel interface
Business users who are used to work with data in Microsoft Excel will be up and running in minutes with updating and managing SQL Server data using the same tool they are used to; Microsoft Excel.
No coding required
SQL Spreads does not require any coding to create data management solutions. Use existing knowledge in Microsoft Excel and SQL Server to build and update your Excel to SQL Server solution.
Easy to maintain and update
A data management solution in SQL Spreads is built using basic Excel and SQL knowledge. Solutions can be maintained in-house without the need to rely on external consultants for updates and changes further down the line.
No problems with wrong data types
When importing user data to SQL Server, wrong data types may cause problems. SQL Spreads has a built-in data check that validates the data at the same time it is entered by the user.
Simple data integrity checks
Drop-down lists make it easy for users to update data columns with foreign key relations – the user selects a text item in the drop-down list in Excel, and SQL Spreads will insert the corresponding key into the database table.
No more troublesome SSIS-imports of Excel files
Importing Excel data into SQL Server using SSIS can be hard. Invalid data types and unclosed files are a few issues that may cause the Excel to SQL Server import to fail.
When using SQL Spreads, data is instantly updated in the SQL Server database.
No more complex VBA scripts to import Excel files to SQL Server
VBA projects can be tough to maintain over time. SQL Spreads use VBA-free Excel documents together with standard database technologies to move data from Microsoft Excel to SQL Server.
Our features
End-user data management of Microsoft SQL Server data
SQL Spreads reads and writes data between Excel and Microsoft SQL Server, and is designed to create end-user applications to update and manage centralized SQL Server data using Microsoft Excel.
Insert, update and delete data in tables in SQL Server through Excel
SQL Spreads will write updates in Microsoft Excel back to SQL Server. Existing data can be updated, new rows can be added or existing rows can be deleted.
Designer and Data Editor roles
SQL Spreads makes it possible for a person with database knowledge to create an Excel document for data maintenance, and then share that Excel document with non-database skilled users so they can work with the data through Excel.
When sharing an SQL Spreads Excel document, you can install SQL Spreads in Data Editor mode. In Data Editor mode, all the Design-features in SQL Spreads will be locked down, and the user can only do changes in the data, not in the setup of how data is transferred back and forth between Excel and SQL Server.
Automatic validation for data quality
When a new value is entered into a cell in Excel, SQL Spreads instantly validates the value against the data type of the mapped column in SQL Server to prevent unsafe data entries.
Change tracking
SQL Spreads tracks which user-created and changed a row in a table in SQL Server, and the time when the change was made.
Drop-down lists for data consistency
To keep data consistent, SQL Spreads creates data-driven drop-down lists in Excel. Data-driven drop-down lists make it possible for the end-user to select a text in the drop-down lists while the corresponding key value is inserted into the database.
Tree-View data selection filter
SQL Spreads can create database import filters that are presented in a Tree-View in Excel. By selecting filter items in the Tree-View, users can select which database data to load into the spreadsheet.
The Tree-View items are fully customizable and are created using standard SQL queries to fetch filter items from the database.
Conflict detection
SQL Spreads’ built-in conflict detection ensures that collaboration works smoothly and that several users can work against the same SQL Server tables at the same time.
Pivot data between Excel and SQL Server
If you have data stored in SQL Server in a row-wise format, SQL Spreads can easily pivot those SQL Server rows into columns in Excel so that end-users can edit and update the data in a column-wise format in Excel.
When saving the data from Excel back to SQL Server, SQL Spreads will save the columns in Excel as rows in SQL Server.
SQL Spreads can update, insert and delete data in pivoted format.
Data Post-processing
Data that SQL Spreads updates in SQL Server can be post-processed for purposes such as cleaning, copying, validation, etc. Data Post-processing is done using a custom SQL query that is executed after the data has been saved to the database.
Demo videos
These demo videos show how to use the SQL Spreads Excel Add-In to set up an Excel document where end-users can update SQL Server data using Excel.
SQL Server table data maintenance
This video shows how to use SQL Spreads Designer to create an Excel document where end-users can manage and update centralized data in SQL Server using Excel.
Typical scenarios: SQL Server table data maintenance, data warehouse metadata maintenance, dimension management, etc.
Update monthly forecasting transactions
This video shows a solution for updating forecasting transactions in a SQL Server table using Excel.
The demo has an import filter where the user selects Month, Company, and Region. When the user has selected the Month, Company, and Region, the data for the selection will be loaded into Excel and is displayed as Budgets, Actuals, and previously entered forecasts.
The user can update the forecast figures and comments and save the changes back to a forecasting table in SQL Server.
Typical scenarios: Budgeting and Forecasting, Data Warehouse Project
This solution is set up using the Advanced Setup in SQL Spreads.
The demo can be downloaded as a zip-file containing the Excel file together with SQL scripts to generate the demo database.
Technical Details
Supported Excel editions
SQL Spreads is an Excel Add-In that can be installed on Excel 2019, 2016, 2013, or 2010 either 32-bit or 64-bit.
SQL Spreads can be used with the desktop version of Excel for Office 365 either 32-bit or 64-bit.
Supported databases
SQL Spreads supports Microsoft SQL Server, any version from SQL Server 2005 to SQL Server 2019, and any edition from Express or higher.
SQL Spreads also supports Azure SQL.
SQL Spreads can connect to SQL Server using either Windows Authentication or SQL Server Authentication.
Supported Windows editions
SQL Spreads can be used on Windows 10, Windows 8 or Windows 7, either 32-bit or 64-bit.
Prerequisites
Prerequisites for installing SQL Spreads are:
- .NET Framework 4.0 Client Profile
- VSTO 2010 Runtime
The SQL Spreads installer will check for prerequisites before installation and install them if necessary.