automating import of excel data to web based SQL Server 1

[jasperx]

We have a web based ASP/SQL server application that our users log into and submit monthly performance numbers by filling in a form. Many of our users accumulate their monthly figures in an excel spreadsheet. What we would like to do is take some of the pain out of data entry.

I have seen a number of suggestions about running the excel data through an Access front end and then using the SQL admin tools to import. This looks fairly clunky and also is not suited to working with our remotely hosted database. One advantage we do have is that we could have users email us files which we would then convert to tab text in any format we wished. What we need is an easy way to make this file data available to a script which can then write it to the database which lives on a hosting service on the other side of the country... we have so far avoided investing in several hundred dollars worth of SQL Server admin tools and get by well enough with scripts on asp pages.

Anyone have any ideas about how best to do this?

 

[mkal]

You might want to take a look at SQL's Data Transformation Services (DTS). If you can store your Excel files where your SQL Server can get to them you could then create a DTS package that imports the data to the database. The package could then be schedule to run when you need it to.

DTS's can be located in Enterprise Manager (client tool) by expanding out the SQL Servers name and then the Data Transformation Services. Right clicking on the Local Packages icon will give you the option to create a new DTS package.

Books Online can give you some information about creating Connections and perfoming Tasks in DTS but for the most part it's a bit thin on info. You might want to locate a book that can provide more in depth knowledge of DTS.

 

[jasperx]

Thanks for getting me pointed in the right direction. I found quite a bit of info in the WROX text on SQL Server 7. I am on my way.