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.3;Database=database_alan;User=sa;Pwd=xxxxxxxx;”
‘Data Type
‘MsgBox(dt.Columns(0).DataType.ToString)

Try
‘Data Structure
Dim sql As String
sql = “CREATE TABLE Comics2(MOBILE_NO numeric(18, 0) NULL,CUS_NAME nchar(50) NULL,L1 nchar(50) NULL,L2 nchar(50) NULL,L3 varchar(100) NULL)”
Dim cmd As New SqlCommand(sql, New SqlConnection(strConnString))
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
Catch ex As Exception
End Try
‘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 = “Comics2”
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
MsgBox(Ex.ToString)
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 *