using Excel as a user input form to a sql database

Posted on 2015-01-05
Last Modified: 2016-02-16
We currently have several spreadsheets of data that are being updated by individual users, the problem is when we would like to provide reporting across all of these spreadsheets in one report.  The data is updated biweekly with new data.  

We are currently maintaining an Excel Workbook which links to all of the workbooks and brings all of the data into one view.  This one excel workbook is linking 17 different excel workbooks.  We also have an issue when we need to add another workbook to this environment - it takes a lot of time.

I have been researching using SSIS Package to update the data in the tables, but in its basic form, it looks like I would need to delete the table and create a new table each time otherwise I am simply continuing to add additional rows tot he table each time.

We are considering that it may be easier to:
1. Import the Excel data into Sql tables and report out of Sql.
2. Link the excel spreadsheets to sql so that the data can me maintained and updated and then the reports would be out of SQL.

Please provide your thoughts and possible solutions to the above question.
Question by:btgtech
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
LVL 66

Expert Comment

by:Jim Horn
ID: 40531976
>we would like to provide reporting across all of these spreadsheets in one report

Spreadsheets are great in that they allow users to do anything.
Spreadsheets are terrible as a data source for a normalized database in that they allow users to do anything that will hose up importing that data into anything normalized.

> This one excel workbook is linking 17 different excel workbooks.
Does the phrase 'house of cards' ring any bells?  How often are there broken links that need to be fixed?  Do you have to 'scrub' the data so that it can be useful for reporting?  Invalid dates, Invalid numbers...

I HIGHLY recommend not going down this road, and instead explain for us how these 17+ users create and interact with the data that you're trying to import.  Then we can recommend a much more durable solution.

Author Comment

ID: 40531999
This solution has been working for a few years.  The users only have access to the fields on the excel spreadsheet that they are entering data into.  We have formatting in place to allow the users to enter the data properly and we also have drop downs where we can for consistency.

Each of the users has a spreadsheet were they are capturing data on a monthly basis.  the spreadsheets are all individual and have a single user assigned.

The excel spreadsheet is not in a normalized structure, so I expect that we would have to have a worksheet in the background to format the data in the appropriate data structure.

We eventually would like to develop an online application to allow the users to login and access the forms via an Online App, save the data in SQL Server and report out.

We are looking for an interim solution to allow us to begin to use the power of the SQL Server for reporting while we continue to have the users utilizing the Excel Spreadsheets.

SSIS vs Linked Excel tables vs SQL - what would be the suggested environment?
LVL 66

Expert Comment

by:Jim Horn
ID: 40532023
>The excel spreadsheet is not in a normalized structure ... worksheet in the background to format the data in the appropriate data structure.
A better idea would be to have a design session and figure out the appropriate database structure (tables, columns, relationships, etc.) to lay the groundwork for future development.  Also explain for us if there is any reason to keep data entry not normalized.

>We eventually would like to develop an online application to allow the users to login and access the forms via an Online App, save the data in SQL Server and report out.
At first whiff once the above design is nailed down, then you have a couple of options here.
Access  - Normalized database that can be split into front-ends and back-end, cheap (ish) money wise to create, can enforce data entry rules.  Not a good candidate for online, but my experience in that area is somewhat outdated so I'd defer to the opinion of any current Access expert
SQL Server - Fine, or maybe SQL Express or any other database, for storing the data.  You'll still have to figure out what the front-end is going to look like, either Access, Excel, or something else. Will eventually cost a server license.
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

LVL 66

Expert Comment

by:Jim Horn
ID: 40532026
To give you an example of how to hotwire Excel into SQL Server here's an article I wrote called Microsoft Excel & SQL Server:  Self service BI to give users the data they want.  I'm guessing there's more than a couple of experts in the Access and Excel zones that have written similar articles to give you an idea of what's possible.
LVL 38

Accepted Solution

PatHartman earned 500 total points
ID: 40532037
I'm with Jim on this one.  Excel is a hugely flexible tool and the starting point for many applications.  It seems that this one has exceeded the ability of people to manage the moving parts.   The simplest, down and dirty interim solution (and I must emphasize -DIRTY - and so recommended only in a pinch) is to substitute Access databases for the spreadsheets.  Don't even normalize the data at this point.  But, and this is important, rather than allowing the user direct access to a table, create a form - it can be in datasheet view so it looks like a spreadsheet or table view.  Using a form to control data entry allows you to impose rules and control what actions the user is allowed to do.  Create combos to provide controlled lists wherever it makes sense.

You haven't said where you really want to go with this so at this point, you have 17 Access databases rather than 17 spreadsheets which is not much of an improvement.  You still need a process to import data from 17 databases.   So, you need to start consolidating.  If all the users are on the same LAN, it will be fairly easy since you can split the database into a FE (front end with forms/reports/queries/code) and a BE (back end with tables only).  The BE is placed on a shared network folder and the FE is distributed to each user so they have their own personal copy.  They open the FE on their local drive.  It is linked to the data on the shared drive and all the data is now stored in a single table where it can be reported on more easily.

Of course, this is very rough and only temporary.  There is a lot you can do to make the data entry user friendly and prevent accidents once you start using forms.

If the users are not on the same LAN, that is more of a problem but easily solved if you have Citrix available.  Other solutions are possible but Citrix is best.

And finally, once your Access front end is up and running using forms and reports against a properly structured schema, you can switch to SQL Server as the BE if you want although it may not be necessary.

Author Comment

ID: 40532044
I should clarify, the data in the spreadsheet where the user is maintaining the data is in a partial normalized structure.

It is a table which has the months of the year down the left and the columns for data entry are across the top.  To fully normalize the structure, we would be adding several columns to the beginning of the spreadsheet so that the data can be easily queried and managed. (Company ID, Month, Year, etc.)

So I have the structure for the Database.  I will take a look at the article you attached above.  The problem is that there are quite a few articles, but none of them really speak to the pros and cons of the solutions offered.

Author Comment

ID: 40532057
I wonder if an Access Webapp would be a better solution?

Do either of you used Access Web Apps?
Is there a way to host the app without the Sharepoint Server?
LVL 38

Expert Comment

ID: 40532179
Access web apps REQUIRE the latest version of SharePoint.  Aside from that the biggest cons are - no programming language support.  You are limited to only what you can do with macros as well as a few other critical missing options such as the ability to create real reports (never ending lists are not real reports).  Also since it is browser based, you don't have Excel, Word, Outlook automation as an option.

It sounds like your problem is that the users are distributed.  If you can't use Citrix as an option, then if you can host your own Azure database, you can have Access connect to the database from anywhere similar to the way any web app would connect.  However, I have tried this with a hosted database and was never able to get reasonable performance.  Most hosting sites do not understand how to set up the database to optimize the way it works with Access.  The people who rave about the solution are the ones who host the database themselves and so have control over everything and have people to tune it correctly.  In my test, I took an app that was working successfully with 50 users against a local SQL Server database holding a few hundred thousand rows.  I uploaded only a hundred rows for testing purposes and I could watch the pixels color as the form rendered which of course was unacceptable.  I tried it from different locations to ascertain that it wasn't the network connection at one of the locations that was causing the problem.  The problem was definitely at the hosting end but they were not able to resolve the issue.
LVL 66

Expert Comment

by:Jim Horn
ID: 40532191
>and I could watch the pixels color as the form rendered which of course was unacceptable.
Pat - As an aside I have a vague memory of this happening at a previous client with 20+ distributed front-ends, and the solution ended up being installing better graphics cards.   Apparently main form - tab control with 12 tabs with lots going on was maxing out the PC's ability to render the form.
LVL 38

Expert Comment

ID: 40532840
The app ran perfectly with hundreds of thousands of rows if it was connected to a local SQL Server instance.  It was the remote Azure database with only a hundred rows that ground it to dust.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question