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.
Best Answer
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.