I need a way to get subjective call QA scoring into our business intelligence system.

It currently lives in Excel. The problem with a direct upload from Excel to the DB is that there is no validation or response from the db necessarily (although I guess I can program these in).

Is there a solution that already exists that allows a non-technical user to import data, and already have it validated against the database?

There are myriad data entry software solutions out there, but as far as I know, actually receiving input from sql server (These are the valid values) is something that's not very common.

MS Access is a possible solution - however, for some reason, their data entry forms do not allow for a bulk copy/ paste, which would slow down end users. You have to enter one item at a time.

  • "have it validated against the database" - that can be done right inside the database using check constraints, foreign key constraints or triggers – a_horse_with_no_name Sep 27 '16 at 6:12

There are lots of tools and methods available for loading data. But the easier you want this to be for end users the more complex and sophisticated your dataloading routine will become.

Data validation and business rules need to be defined and implemented. Somebody has to do it. No software is smart enough to do that "auto-magically".

You could build data checking into the spreadsheet. Hopefully this would stop errors before you load. It is possible to import data directly from a db query and there is a variety of lookup and validation tools in excel.

You could build an import routine in ssis and apply validations, filters, lookups and substitutions as part of the import process. It can even handle errors and redirect invalid records back to the user. But this takes time and effort.

My preferred approach would be to use a db to begin with rather than excel. That way datatypes, dependencies, rules and constraints can be enforced from the start. You could develop an interface in MS-access.

Unfortunately all of these things take time. The more sophisticated you want the app to be more time it will take to develop.

| improve this answer | |
  • Perhaps I wasn't clear. This is for other users - I would consider myself highly technical. A live database already exists - our data warehouse for our Business Intelligence software. There are many external source imports into this master warehouse. However, this is the first case where we aren't dumping from a cloud-based application, but 'Excel spreadsheets.' The main difference is that the Excel lacks validation or structured data. Now true, you can build in validation in Excel, but it's limited. For instance, an employee list (constantly changing in our DB) could not be validated in Excel – user45867 Sep 27 '16 at 14:48
  • For example we want phone scores input for Tom, Dick, and Harry. For starters, we want Tom to be called Tom each time (or employee Id 456, whatever) ... also, next month, Harry might be gone and replaced by Susan. This is all in the DB. Using Excel, we are hoping the right info is input into 'employeeID' or 'employeename' - and the Excel user won't get instant feedback (maybe an email) if something is wrong. Seems like we need a direct data entry form. Shockingly, no one has thought to sell software like this (that not only outputs, but receives input, from the DB). I may have to code my own – user45867 Sep 27 '16 at 14:51
  • Actually I guess MS Access can be a front-end 'link' to a SQL server db, but the data input interface is utterly terrible. You can't copy/ paste multi-line items; using the forms is not intuitive; on occasion you will get obtuse errors that would confuse end-users. Might be the quickest way though. – user45867 Sep 27 '16 at 14:53
  • SSIS or another ETL process would work (again would be imported from Excel) -- but the best you can do is an email back to the user (maybe) with error information. It's pretty sloppy. – user45867 Sep 27 '16 at 14:55
  • If you are going to go to that much effort i would try to eliminate the spreadsheet altogether. – Sir Swears-a-lot Sep 28 '16 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.