problem whem importing EXCEL sheet to SQL server

[zamzoum]

hi



i just wanted to ask u how can i solve the exception error "couldnot find installable ISAM"

this exception occurs when i run this code :
[CODE=vbnet]Dim excelConnection As System.Data.Ole Db.OleDbConnect ion = New
System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
Source=c:/test.xls;Extend ed Properties=Exce l 8.0;")
excelConnection .Open()

Dim excelCommand As New System.Data.ole db.OleDbCommand ("INSERT
INTO [OBDC; Driver={SQL
Server};Server= (local);Databas e=FullDate;Trus ted_C onnection=yes].[Population]
SELECT * FROM [Population$];", excelConnection )

'Dim ExcelCommand As New System.Data.Ole Db.OleDbCommand ("SELECT
INTO [ODBC;Driver={SQ L
Server};Server= (local);Databas e=FullDate;Trus ted_C onnectionÂ*=yes].[Population] FROM [Population$];", excelConnection )

excelCommand.Ex ecuteNonQuery()
excelConnection .Close()[/CODE].

ALSO i wanted to ask how can i map importing data from the excel sheet to SQL with the primary key. Can anyone give me a syntax

10x in advance

[aaronsandoval]

If you are searching for an example on how to correct your error "Could not find installable ISAM" you may want to search here: http://support.microsoft.com/kb/209805

If you are searching for an example on how to upload Excel data into SQL Server Table here is how you do it:

[CODE=vb]
Public Sub ReadDataFromExc elIntoSqlServer ()
Try
Dim excelConnection String As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=D:\RiskA uditor\Sample\P roduct.xls;Exte nded Properties=Exce l 8.0"

' Create Connection to Excel Workbook
Using connection As New OleDbConnection (excelConnectio nString)
Dim command As New OleDbCommand("S elect Name,No,Pay,Sal ary,Total from [Sheet1$]", connection)
connection.Open ()

' Create DbDataReader to Data Worksheet
Using dr As OleDbDataReader = command.Execute Reader()
' SQL Server Connection String
Dim myConnection As New SqlConnection()
myConnection.Co nnectionString = "database=Risk_ AuditNew;User ID = sa;Pwd=sa"
myConnection.Op en()

' Bulk Copy to SQL Server
Using bulkCopy As New SqlBulkCopy(myC onnection)
bulkCopy.Destin ationTableName = "ExcelData"
bulkCopy.WriteT oServer(dr)
End Using

myConnection.Cl ose()
End Using
End Using
Catch ex As Exception
Response.Write( ex.Message)
Finally
End Try
End Sub
[/CODE]

Example Code was gathered from: http://www.csharphelp.com/board2/rea...=28912&t=28912
and converted into VB.

- Aaron Sandoval