Best way for users to import to a database?

Question

I know this isn't a unique issue but I've not had much luck finding examples of how others have addressed this issue. I have an intranet asp.net application I have inherited and am building upon. One particular page is for data entry to submit a claim for approval and we have a form where one record at a time can be entered and it is validated in realtime against the database for valid product information.

I have been asked to add a feature where users can import a large number of records all at once to save them time of going through each text box and dropdown list one at a time for over 100 records sometimes.

The solution I've looked into as my first option is to create an excel spreadsheet they can open from a link on the page that I have formatted into a template that they can copy/paste their data into, save and then upload to the server and import as a linked server object into sql server.

Due to the nature of this solution and some of the data being imported I have to program some error handling into VB for the spreadsheet and if I ever find a bug to fix, there is no good way to guarentee the user is going to use the version on the page and not some local copy they saved that may break when trying to import.

I've ruled out one big text box that I attempt to parse because I have no control that way over how the user enters the data.

Is there some better and possibly more obvious solution that I am missing?

Answer 1

Upoload the file to your site parse the file out in your application, and using the same logic as your web uses (Assuming your using a domain model) create your claims. You can upload CSV files or any format you can parse on the server.

Answer 2

Perhaps this is what you meant, but rather than a spreadsheet I would use a csv (comma-separated values) file. It can still be opened and edited in Excel but can also be easily edited in other editors for those users who don't have Excel. That will also make it easier for you on the parsing side.

If you're worried about versioning, maybe you can make one of your column headers an empty column that just contains the current version.

Answer 3

Uncontrolled spreadsheet import processes are a bad thing. There are too many things that users can do to break the process. If you're going to bulk load from spreadsheets, the process should consist of the following components:

• A controlled template file, downloaded by the user and generated from the application (if the template doesn't need to be changed, it can just live in a folder somewhere).

• A check to ensure that the template has been used.

• The actual load process.

• A screen where the user can verify what they have imported, edit and commit the upload.

The check and validate part is a good way to place a control on the upload that can validate data. The user has to inspect the imported data and manually approve it. This puts the onus for correctness back on the users, and empowers them to see and fix the upload.

Answer 4

Your answer assumes that Excel is a better system for entering and managing data than you can produce on a website. However, there are plenty of editable Datagrid options in .NET that can emulate the power of Excel.

If on the other hand, you want people to be able to work with a local copy of the data without being connected to the network, you may want to consider automatically generating spreadsheets on a shared drive or in a user folder (one spreadsheet per user) which is tied back to the database using one of the data access systems.

Good luck!