Upload Excel to SQL through web application

SQL Data Types Mapping
ref: http://msdn2.microsoft.com/en-us/library/4e5xt97a.aspx

Set up a folder that both the web app and SQL server can get to.Do a simple file upload from the web appSet up a DTS package to periodically check the folder to do an import-or-Set up a DTS package that will do the import and call it from a StoredProc after the web app does the upload


ref: http://support.microsoft.com/?kbid=321686
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\dev\test.xls', 'SELECT * FROM [sheet1$]')

use the ADO.NET loading the excel
ref: http://davidhayden.com/blog/dave/archive/2006/05/31/2975.aspx

How to read excel sheet schema information with .net
Dim i As Integer
Dim dtXlsSchema As DataTable
Dim myConn As New OleDbConnection
Dim XlsConn As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
"Source=C:\temp\myWorksheet.xls;" & _
"Extended Properties=Excel 8.0"
'
' Open an ADO connection to the Excel file.
'
myConn.ConnectionString = XlsConn
myConn.Open()
'
' Get a list of tables (worksheets) in the XLS file.
'
dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})

For i = 0 To dtXlsSchema.Rows.Count - 1
Debug.WriteLine(dtXlsSchema.Rows(i).Item("Table_Name").ToString)
Next
'
' Get the schema for the specified table.
' Change "MyTableName" to the actual worksheet name.
'
dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "MyTableName$", "TABLE"})

For i = 0 To dtXlsSchema.Columns.Count - 1
Debug.WriteLine(dtXlsSchema.Columns(i).ToString)
Next
'
' List the columns for the specified table.
'
dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "MyTableName$", Nothing})

For i = 0 To dtXlsSchema.Rows.Count - 1
Debug.WriteLine(dtXlsSchema.Rows(i).Item("Column_Name").ToString)
Next

myConn.Close()


Ref: http://www.thescarms.com/dotnet/Schema.asp

Upload a File with asp.net 1.1
ref: http://support.microsoft.com/default.aspx?scid=kb;en-us;323245&Product=aspnet