Update SQL table from excel file

[PushCode]

This is just a theory question. I'm wondering if the following is possible.

I need to create an asp.net page that will allow a user to Browse for an excel file, select the excel file, then click a button or link that will cause the excel file to be imported into an existing table in our MS SQL 2000 DB automatically. It would need to update the table with all new and changed records, and delete those that do not match up. And give the user some sort of confirmation that it is done.

The reason for all of this is that the client gets an updated list of their members (the excel file) each week.
Right now, we have to go into Enterprise Mgr and do this all manually. But they want to be able to do this themselves, via the web...they don't have Enterprise Mgr.

Does this sound doable? Has anyone done something similar?

 

[dragonwell]

That sounds doable! But here's another idea - can't Excel export straight to XML? Have tjem save the spreadsheet as XML, upload that, and you can get that into SQL in a snap.

HTH

 

[PushCode]

dragonwell,

We use Excel 2000, and I don't know of a way to 'export to xml'

I'm assuming it is an easy process with Excel 2003, but does anyone know how to do it with 2000?

 

[cbokowski]

You could use a DTS package called from your page to insert it. Many examples exist either on msdn or google "dts package import excel"