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 :

Dim excelConnection As System.Data.OleDb.OleDbConnection = New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:/test.xls;Extended Properties=Excel 8.0;")
excelConnection.Open()
 
Dim excelCommand As New System.Data.oledb.OleDbCommand("INSERT
INTO [OBDC; Driver={SQL
Server};Server=(local);Database=FullDate;Trusted_C onnection=yes].[Population]
SELECT * FROM [Population$];", excelConnection)
 
'Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT
INTO [ODBC;Driver={SQL
Server};Server=(local);Database=FullDate;Trusted_C onnectionÂ*=yes].[Population] FROM [Population$];", excelConnection)
 
excelCommand.ExecuteNonQuery()
excelConnection.Close()

.

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:

Public Sub ReadDataFromExcelIntoSqlServer() 
Try 
Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RiskAuditor\Sample\Product.xls;Extended Properties=Excel 8.0" 
 
' Create Connection to Excel Workbook 
Using connection As New OleDbConnection(excelConnectionString) 
Dim command As New OleDbCommand("Select Name,No,Pay,Salary,Total from [Sheet1$]", connection) 
connection.Open() 
 
' Create DbDataReader to Data Worksheet 
Using dr As OleDbDataReader = command.ExecuteReader() 
' SQL Server Connection String 
Dim myConnection As New SqlConnection() 
myConnection.ConnectionString = "database=Risk_AuditNew;User ID = sa;Pwd=sa" 
myConnection.Open() 
 
' Bulk Copy to SQL Server 
Using bulkCopy As New SqlBulkCopy(myConnection) 
bulkCopy.DestinationTableName = "ExcelData" 
bulkCopy.WriteToServer(dr) 
End Using 
 
myConnection.Close() 
End Using 
End Using 
Catch ex As Exception 
Response.Write(ex.Message) 
Finally 
End Try 
End Sub 
 

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

- Aaron Sandoval