End users love working in Excel; they know the tool and they are free to do what they want. That’s the heart of the much-loved Excel application, but also the start of problems for the people taking care of the data. The freedom to add cells and enter “whatever-you-like” values causes huge problems when trying to store and summarize the data in a structured way.
Collecting some “not-available-in-our-systems” data from colleagues is often done by mailing out some Excel file or putting a spreadsheet on a file share and letting people update it.
In such a solution, problems like these usually occur:
- Invalid data types in some cells
- Troublesome to extract Excel data (with a tool such as SSIS)
- Problems to summarize when users change the layout of the sheet
- Hard to keep track of previous versions
- A delay of several hours between when a users has entered the figures and when they appear in the database
- Hard to track who has changed a specific value in a sheet
In this post I will describe how SQL Spreads can be used to create an Excel document that will update SQL Server data. I will show how ranges in Excel are mapped to SQL Server tables and how to keep track of data quality.
Familiar and User-friendly Excel Interface
SQL Spreads works with standard Excel documents and adds the possibility to work with the data in SQL Server tables directly from Excel. Users are authenticated using their Windows Login and can only work with the Excel documents for which they are authorized.
In SQL Spreads, data is automatically validated when users enter their figures and data from other Microsoft Excel documents can be pasted directly into the SQL Server connected document, but only if the data is valid.
When end users save their document, the data is automatically extracted and stored in a structured way in the table in SQL Server.
Mapping Spreadsheet Columns to SQL Server Tables
The setup of an Excel document to work with SQL Server is done through three simple steps:
- Click the Table Wizard button in the SQL Spreads tab in Excel and select the database and table that should be updated through Excel.
- Follow the steps in the Wizard to select which columns to include and configure a few settings.
- The table data will be loaded into Excel and available for updating.
When you update a value in Excel, SQL Spreads will validate the entered data based on the database column types. When you save the document, all your changes will be updated in the table in SQL Server.
Assured Data Quality
To get the highest possible quality of data, several methods are used to guarantee the entered data:
- When figures are entered, validation is done against the types of the database columns and feedback is immediately given to the user.
- For any column in the database that has a foreign key relation, Drop Down Lists are automatically created in the column in the spreadsheet.
- Each changed row is tracked in the database to see when a row was changed and by who.
- When sharing the document with others, they can be given a User role to protect the settings in the Excel document.
Conclusion
With SQL Spreads, you can:
- Use Excel to work with data in SQL Server tables.
- Let non-technical users work with the SQL Server data.
- Ensure that the entered data is valid.
The new SQL Spreads trial is now available for download from this page. There is also a video available showing how you can use SQL Spreads to create an Excel document that updates the data from Excel to SQL Server.
Editors note: This blog post was originally published for a previous version of SQL Spreads and has been completely revamped and updated for accuracy and comprehensiveness.
