SQL Spreads

  • SQL Spreads Blog

  • How to Create Web-based Excel Forms in 10 Minutes

    Oct 13th, 2011

    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:

    1. 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.
    2. 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.
    3. 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:

    1. Create a new Form in SQL Spreads and use the editor to design your form’s spreadsheet template.
    2. Select the data you would like to export in the spreadsheet and map the columns to the database fields.
    3. Add measures, such as organization, where you would like to collect your data, and decide which users should be authorized for entering the data.
    4. Ready! Let your users start working with your new form!

    Conclusion

    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.

    • Liked This Article? Share:
    • Twitter

    Johannes
    The author didn't add any Information to his profile yet.


Comments (8)

Clara Ciaccia » 10. Dec, 2011

This is extremely helpful, thanks for taking your time to write this.

Elizabet Weisenhorn » 10. Dec, 2011

Thank you for sharing this superb information.

B. Peters » 19. Dec, 2011

Hi, very nice! I worked for about 10 years for leading BI companies (Corporate Planning and Infor) in germany and I think this is going to become a big hit. Can you give me an idea about the range of fees you are going to advise on a monthly basis? Best regards

admin » 19. Dec, 2011

Hi, thanks for the feedback! SQL Spreads will start at € 299 per user for small license packages (a few users). The license includes one year free support and upgrades.

Best regards
Johannes

B. Peters » 20. Dec, 2011

Hi Johannes, Is this already available then? Best regards

admin » 20. Dec, 2011

Hi,

SQL Spreads is not released yet, but there will be a public beta-release available in February. I can put you up on the beta-test-list, you will then also have the 30% discount coupon on SQL Spreads. Best regards, Johannes

B. Peters » 07. Jan, 2012

Hi Johannes, 
Would be greate to be mentioned on the list. There came up further questions while i was surfing thru your companies websites. Can you give me an idea of the differences between the new sqlspreads an the xcellplanner on obnex.se? They look quite similar.
Best regards

admin » 09. Jan, 2012

Hi,
Xcellplanner and SQL Spreads build on similar technology, though SQL Spreads has a dramatic decrease in learning curve and set up time. To create a simple Excel form in SQL Spreads and map it to a SQL Server table is done in a few minutes compared to hours in Xcellplanner. Also SQL Spreads has some really nice features such as an automatic validation with an instant check that figures entered into a spreadsheet meets the data types of the mapped database fields.
Best regards, Johannes