Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to import data from Excel to Access database in ASP.NET

Author: Mike Green
Download Source Code : 1392_ImportDataFromExcelToAccess.zip

I will explain in this article that how we can import data from MS excel sheet to MS Access database table in ASP.NET.

 

Data from excel sheet can be easily transferred to access database table using ADO.NET code. You just need to set connection to excel and write a query to select data from excel and insert it to access database. For this article, I have an excel file “Employees.xlsx” and I have created access database “Employees.accdb”. 

 

  1. Open MS Visual Studio 2010
  2. File > New > Website > Visual C# or Visual Basic > ASP.NET Empty Web Site
  3. Select Web Location as File System and Click OK
  4. From Menu, Website > Add New Item > Select Web Form and Click Add.
  5. Write code below in default.aspx page

                onclick="Button1_Click" />


     
  6. Include System.Data.OleDb namespace in your code file
  7. Write below code in button click event to read data from excel sheet and insert into MS Access database.

    C#
     
    protected void Button1_Click(object sender, EventArgs e)
    {
        string connStringExcel = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Employees.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""";
        OleDbConnection excelConn = new OleDbConnection(connStringExcel);
        OleDbCommand excelCmd = new OleDbCommand();
        excelCmd.Connection = excelConn;
     
        try
        {
            excelConn.Open();
            excelCmd.CommandText = "SELECT * INTO [MS Access;Database=D:\\Employees.accdb].[Employees] FROM [Sheet1$]";
            excelCmd.ExecuteNonQuery();
            Label1.Text = "Data successfully copied to MS Access database table";
        }
        catch (Exception ex)
        {
            Label1.Text = ex.Message;
        }
        finally
        {
            excelConn.Close();
        }
    }
     
    VB.NET
     
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connStringExcel As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Employees.xlsx;Extended Properties=""Excel 12.0;HDR=YES;"""
        Dim excelConn As New OleDbConnection(connStringExcel)
        Dim excelCmd As New OleDbCommand()
        excelCmd.Connection = excelConn
     
        Try
            excelConn.Open()
            excelCmd.CommandText = "SELECT * INTO [MS Access;Database=D:\Employees.accdb].[Employees] FROM [Sheet1$]"
            excelCmd.ExecuteNonQuery()
            Label1.Text = "Data successfully copied to MS Access database table"
        Catch ex As Exception
            Label1.Text = ex.Message
        Finally
            excelConn.Close()
        End Try
    End Sub
     
    First we have written connection string for to connect to excel. This connection string should be in a special format. See how I have written connection string. We have created OledbConnection object and set connection. Then we have created OledbCommand object and set its Connection property. In Try block, I have opened connection and written a query to get data from excel and insert into access database. In query, “SELECT * FROM [Sheet1$]” selects the data from excel sheet and “[MS Access;Database=D:\Employees.accdb].[Employees]” inserts data into Employees table of Employees MS Access database. It will create a new table with a name “Employees” and then it will insert data into this table.
     
  8. Now you can browse the web site and import data from excel to MS Access database table.
   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to update two tables from GridView update command in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net