how to import the excel file to sql database using vb.net?
I have a more data in excel file .so i have to import it into sql开发者_Python百科 database using vb.net.can anyone send the source code?
Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyExcelSpreadsheet.xlsx; Extended Properties=""Excel 12.0 Xml; HDR=Yes""")
ExcelConnection.Open()
Dim expr As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=MMSQL1; Initial Catalog=DbName; User Id=UserName; Password=password;"
SQLconn.ConnectionString = ConnString
SQLconn.Open()
Using bulkCopy Asd SqlBulkCopy = New SqlBulkCopy(SQLConn)
bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER"
Try
objDR = objCmdSelect.ExecuteReader
bulCopy.WriteToServer(objDR)
objDR.Close()
SQLConn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Using
If it's a one-off job, use DTS or SSIS. No code required.
Otherwise, you can open Excel as a data source, suck up its contents and insert into your database.
精彩评论