EXCEL TO ACCESS

Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data
Public Class Form1
Dim ds1 As New DataSet
Dim ds2 As New DataSet
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
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\test.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)
‘Creat Data Structure
Dim strConnString As String = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test\test.accdb;Persist Security Info=False;”
Dim con As New OleDbConnection(strConnString)
‘Try
‘ con.Open()
‘ Dim cmd As New OleDb.OleDbCommand(“CREATE TABLE test_t ([Field1] TEXT(10), [Field2] TEXT(10))”, con)
‘ cmd.ExecuteNonQuery()
‘ con.Close()
‘Catch ex As Exception
‘End Try

‘Data Output
con.Open()
Dim zd1 As String, zd2 As String
For s = 0 To customerList.Tables(0).Rows.Count – 1
zd1 = customerList.Tables(0).Rows(s)(0).ToString
zd2 = customerList.Tables(0).Rows(s)(1).ToString

Dim Sql As String = “INSERT INTO test_t(Field1,Field2) VALUES(‘” + zd1 + “‘,'” + zd2 + “‘)”
Dim MyCommand As New OleDbCommand(Sql, con)
MyCommand.ExecuteNonQuery()
Next
con.Close()
MsgBox(“导入成功!”)

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

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 *