CODEWITHSUNDEEP
vb.net
user321182
user321182

Reputation: 1

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 database using vb.net.can anyone send the source code?

Upvotes: 0

Views: 14581

Answers (2)

Mike
Mike

Reputation: 21

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

Upvotes: 2

Marcelo Cantos
Marcelo Cantos

Reputation: 186068

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.

Upvotes: 1

Related Questions