Import data from Excel to Access using ADO.Net

Data interoperability with Microsoft office applications has become easier with ADO.Net.

The Microsoft Office System exposes objects through COM objects. Microsoft released a suite of Primary Interop Assemblies (PIAs) that are optimized for accessing COM objects from .NET-based assemblies. These generally get installed into your system when you install MS office.

The data can be easily transferred from an Excel spreadsheet to an access database using ADO.net and suite of these Microsoft office interop assemblies. Here first we will make use of Microsoft Jet OLE DB provider to establish connection to an excel spreadsheet. The process to be followed is as under:

Create an Excel sheet you want to transfer data from. Let's assume the file is named Book.xls and the first sheet is the default sheet Sheet1.

Add reference to Microsoft Office Access Interop Assembly.






Right click on added reference's property to ensure that the Path of the assembly points to GAC.

For Example:

C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Access\10.0.4504.0__31bf3856ad364e35\Microsoft.Office.Interop.Access.dll

Remove any previously created Access file and create a new one to import data into.

If File.Exists("C:\Book.mdb") Then

File.Delete("C:\ Book.mdb")

End If



Dim _accessData As Access.Application

_accessData = New Access.ApplicationClass()

_accessData.Visible = False

_accessData.NewCurrentDatabase("C:\ Book.mdb")

_accessData.CloseCurrentDatabase()

_accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)

_accessData = Nothing


Now let's establish connection to our data source (Excel file) using Microsoft Jet OLE DB provider.


Dim _filename As String = "C:\Book.xls"

Dim _conn As String

_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _fileName & ";" & "Extended Properties=Excel 8.0;"

Dim _connection As OleDbConnection = New OleDbConnection(_conn)

Use OledbCommand object to select all the data from sheet1 and execute an ExecuteNonQuery to import data into Book.mdb.

Dim _command As OleDbCommand = New OleDbCommand()

_command.Connection = _connection

Try

_command.CommandText = "SELECT * INTO [MS Access;Database=C:\Book.mdb].[Sheet1] FROM [Sheet1$]"

_connection.Open()

_command.ExecuteNonQuery()

_connection.Close()

MessageBox.Show("The import is complete!")

Catch e1 As Exception

MessageBox.Show("Import Failed, correct Column name in the sheet!")

End Try


This will create a new mdb data file called Book.mdb on your disc containing Sheet1 data. You will notice that table Sheet1 in Book.mdb file has taken the first rows of Sheet1 as the column names. That means that first row of your sheet contains header row data. The attribute "HDR=yes;" in connection string specifies this.



The full Code is here:



'Call this method by supplying it the Data Source file //name, which in the example is Book.xls

Public Shared Sub CheckUpdateDBFile(ByVal filename As String)

If File.Exists("C:\Book.mdb") Then

File.Delete("C:\Book.mdb")

End If

Dim _accessData As Access.Application

_accessData = New Access.ApplicationClass()

_accessData.Visible = False

_accessData.NewCurrentDatabase("C:\Book.mdb")

_accessData.CloseCurrentDatabase()

_accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)

_accessData = Nothing

Dim _connection As OleDbConnection = MakeExcelConnection(filename)

FillAccessDatabase(_connection)

End Sub



Private Shared Function MakeExcelConnection(ByVal fileName As String) As OleDbConnection

Dim _conn As String

_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fileName & ";" & "Extended Properties=Excel 8.0;"

Dim _connection As OleDbConnection = New OleDbConnection(_conn)

Return _connection

End Function



NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C-SHARPCORNER (http://www.c-sharpcorner.com/).