EXCEL TO SQL SERVER

Imports System.Data.SqlClient
‘Imports System.Data.Sql
‘Imports System.Data.OleDb
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.Show()
‘Read From Excel
Dim connectionString As String ‘ Used to store the connection string
Dim customerList As New DataSet ‘ Used to store the temp records readed from the Excel file
Dim excelData As OleDb.OleDbDataAdapter
Dim fileName As String = “c:\test\n.xlsx”
connectionString = String.Format(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=””Excel 12.0;HDR=YES””;”, fileName)
excelData = New OleDb.OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, connectionString)
excelData.Fill(customerList)
DataGridView1.ColumnHeadersVisible = False
DataGridView1.DataSource = customerList.Tables(0)
‘Export to Datatable
Dim dt As New DataTable()
dt = customerList.Tables(0)
Dim strConnString As String = “Server=192.168.1.101;Database=database_alan;User=sa;Pwd=yXXXXXXX1;”
‘Data Type
‘MsgBox(dt.Columns(0).DataType.ToString)

‘Data Structure
Dim sql As String
sql = “CREATE TABLE User_Info(MOBILE_NO numeric(18, 0) NULL,CUS_NAME varchar(50) NULL)”
Dim cmd As New SqlCommand(sql, New SqlConnection(strConnString))
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
‘Data Output
Dim xRowsLoaded As Long = 0
Try
Using xSqlCon As New SqlConnection(strConnString)
Dim xBcp As New SqlBulkCopy(xSqlCon, SqlBulkCopyOptions.[Default], Nothing)
xBcp.DestinationTableName = “User_Info”
xBcp.BatchSize = dt.Rows.Count
xBcp.BulkCopyTimeout = 0
If xSqlCon.State = ConnectionState.Closed Then
xSqlCon.Open()
End If
xBcp.WriteToServer(dt)
xRowsLoaded = dt.Rows.Count
xBcp.Close()
xSqlCon.Close()
End Using
Catch Ex As Exception
End Try
MessageBox.Show(String.Format(“Inserted {0} Rows at a Time!”, xRowsLoaded))

End Sub
End Class

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *