This article is written for the previous version of SQL Spreads and may differ in some details.
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 web-based Excel forms that are easily published on the intranet and connected to a SQL Server database. 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 use web-based Excel forms that are published to users through a web browser. Users are authenticated using their Windows Login and can only work with Excel forms for which they are authorized.
In SQL Spreads it’s possible to lock all cells except those where the end users enter their values. Data is automatically validated when users enter the figures, but it’s still the Excel interface with formulas and users can past data directly from Microsoft Excel into the SQL Spreads forms, but only if the data is valid.
Mapping Spreadsheet Columns to SQL Server Tables
When end users save their data in spreadsheets, the data is automatically extracted and stored in a structured way in a table in SQL Server.
Mapping a range in a SQL Spreads form to a table in a database is done through three simple steps:
- Go to the Export Tab in SQL Spreads and select the spreadsheet range you would like to store in a database. Unselect rows and columns that you don’t want to save such as summary rows, etc. Click Save and switch to the Map Columns Tab.
- Select the database and table where you would like to store the data. SQL Spreads automatically tries to map the columns based on the header names in the selected spreadsheet range.
- Confirm your mappings and click Save.
SQL Spreads is now ready to export the spreadsheet data and will also validate entered spreadsheet data based on the database column types.
Assured Data Quality
To get the highest possible quality of data, several methods are used to guarantee the entered data:
- Cells in the spreadsheet are locked and only specific cells can be edited.
- When figures are entered, validation is done against the types of database columns and feedback is immediately given to the user.
- A workflow model makes it possible to commit and approve entered data.
- Customized business rules can qualify that data meets the business rules before being saved or committed by users.
Up and Running in Less than 10 Minutes
These are the steps to get started with SQL Spreads:
- Create a new Form in SQL Spreads and use the editor to design your form’s spreadsheet template.
- Select the data you would like to export in the spreadsheet and map the columns to the database fields.
- Add measures, such as organization, where you would like to collect your data, and decide which users should be authorized for entering the data.
- Ready! Let your users start working with your new form!
With SQL Spreads, you can:
- Create Excel-based forms for gathering data.
- Publish the forms on the intranet.
- Ensure that the entered data is valid.
- Automatically get data exported from the Excel forms to SQL Server.
SQL Spreads will be launched this winter. Until then, I will publish more posts here about the tool. There will also be a video posted here, showing how easy it is to map a range in Excel to a SQL Server table.