Import Data From Excel to Access with ASP.NET

4.47 (34 votes)

Building on my previous article on importing text files of various formats to an Access database, here's how to do the same thing simply and efficiently with Excel files.

For this particular example, I have an Excel Workbook with one Worksheet, which is named by default, Sheet1. In it, I have a header row, with FirstName, SecondName and Age as the column headings. Thereafter is a selection of data:

Excel Sheet1

I also have an Access database called contacts.mdb, with a table called Persons. The schema of the table matches the Excel sheet, with the addition of an Autonumber column:

Excel2

Both of these files are in the App_Data folder of a web site, so the only thing to do is to read the data out of Excel and into Access. As I mentioned in my previous article, the Jet 4.0 driver can connect to a variety of file types, including Access and Excel. The only difference each time is in the connection string. For Excel, a typical connection string will look like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0";

Making sure I have referenced System.Data.OleDb, here is the code that transfers the records across from Excel to Access:

[C#]
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand())
  {
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[Persons] SELECT * FROM [Sheet1$]";
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}
[VB]
Dim Access As String = Server.MapPath("App_Data/contacts.mdb")
Dim Excel As String = Server.MapPath("App_Data/Book1.xls")
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & _
    ";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
  Using cmd As New OleDbCommand()
    cmd.Connection = conn
    cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].[Persons] SELECT * FROM [Sheet1$]"
    conn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

Notice that the INSERT INTO statement points to the Access database by stating the type of application that is to be used (MS Access;), and the full path to the actual mdb file. Following that is the dot separator and the name of the target table. As with all references to worksheets in Excel, the name of the sheet in the FROM clause has a $ sign appended to it. The above statement will result in the records from Excel being appended to the relevant table in Access.

Creating a new Table

A slight alteration to the SQL will result in a new table being created in the Access database, into which the records will be copied, along with the header row as field names:

[C#]
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand())
  {
    cmd.Connection = conn;
    cmd.CommandText = "SELECT * INTO [MS Access;Database=" + Access + "].[New Table] FROM [Sheet1$]";
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}
[VB]
Dim Access As String = Server.MapPath("App_Data/contacts.mdb")
Dim Excel As String = Server.MapPath("App_Data/Book1.xls")
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & _
    ";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
  Using cmd As New OleDbCommand()
    cmd.Connection = conn
    cmd.CommandText = "SELECT * INTO [MS Access;Database=" & Access & "].[New Table] FROM [Sheet1$]"
    conn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

And the result:

 

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 88254

16 Comments

- priya

hai
i has gone through this website..i am trying to read the data from excel and display in the gridview.
while running the folling code

protected void Page_Load(object sender, EventArgs e)
{

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("s.xls") + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connectionString);
objConn.Open();
String strConString = "SELECT firstColumnName FROM [Sheet1$]";
//where date = CDate('" + DateTime.Today.ToShortDateString() + "')";
OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);
OleDbDataReader r = objCmdSelect.ExecuteReader();
GridView1.DataSource = r;
GridView1.DataBind();
objConn.Close();
}


i got this error at runtime:

The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.


- Mike

@priya

I suggest you ask you question in a general ASP.NET forum, like the one at http://forums.asp.net. But first, make sure that the sheet exists and that you have spelt its name and the name of the path to the file correctly.

- m

Genious! thanks!

- durgesh pratap singh

hi priya

u can use this code for import data from excel sheet to
griedview directly.

string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel + ";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM [Sheet1$]";
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();
conn.Close();

}
}

- serefkoca

thanks this article.
---------------------------------------
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel + ";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM [Sheet1$]";
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();
conn.Close();
------------------------------------------
I want export data gridwiev to access.

if you write export to access code . I will to be very happy

- usha

Dear Mike,
Can you pl. provide ASP code,
how to upload excel file to server pc. My ASP code is working well when i test in my localhost. But when the Accessdb is in server, the excel file selected by any user as per the access table shd. able to download data to access and display data in the table on asp page. Kindly provide me the necessary code. tq.

- Paul Watson

Hi Mike,

i am trying to append excel data into an access table just like you have done above, my cmd object reads exactly the same as yours, except that i ahve changed the excel sheet and access table names to suit my needs, however, instead of the data appending to the Table, a OledbException is firing stating that The table i am trying to append to already exists.

i'm a little confused.

Thank you

Paul

- Paul Watson

I have it working now, sorry to be a nuisance.

- Madhukar

Excellent

- Fern

Thanks so much for suggestion.

- Natalie

What about impor text file with delimeter ',' into Access?

- Mike

@Natalie,

This article covers that for you: Reading Text files into Access with ASP.NET

- sivakumar

very nice
thanking you

- pinal

thank you

- Rajasekar

While use this code i'm facing on error: "Unrecognized Database format C:\mydabase.accdb"

can any one help me...

[VB]
Dim Access As String = Server.MapPath("App_Data/contacts.mdb")
Dim Excel As String = Server.MapPath("App_Data/Book1.xls")
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & _
";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
Using cmd As New OleDbCommand()
cmd.Connection = conn
cmd.CommandText = "SELECT * INTO [MS Access;Database=" & Access & "].[New Table] FROM [Sheet1$]"
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using

- Mike

@Rajasekar

You need to use the ACE provider to connect to accdb files. not JET.

Recent Comments

Pam 30/08/2017 11:30
In response to Sending Email in Razor Pages
Mike, RazorPages sound like a nice choice for somebody still working in ASP classic who wants to to...

Robby Robson 15/08/2017 00:43
In response to Routing in Razor Pages
Mike: great stuff. Now that .Core Standard 2.0 is formally out, how soon will you rewrite your book...

Satyabrata Mohapatra 28/07/2017 08:59
In response to Sending Email in Razor Pages
Bit off topic, but congratulation sir for your MVP award. You deserve it !!!...

Satyabrata Mohapatra 23/07/2017 16:43
In response to Razor Pages - The Elevator Pitch
@Dale Severin You can continue to build apps using asp.net web pages....

Satyabrata Mohapatra 23/07/2017 16:40
In response to Sending Email in Razor Pages
Thanks for sharing...learned a lot...

Gfw 22/07/2017 11:53
In response to Sending Email in Razor Pages
Question... Does System.Net.Mail support SSL?...

Dale Severin 20/07/2017 03:38
In response to Razor Pages - The Elevator Pitch
I work with razor web pages extensively. I appreciate the rapid development it permits me to I am as...

Obinna Okafor 14/07/2017 01:19
In response to Routing in Razor Pages
Thank you, Mike. Good post....

Satyabrata Mohapatra 11/07/2017 16:02
In response to Routing in Razor Pages
Very powerful routing system!!...

Cyrus 05/07/2017 03:41
In response to Razor Pages - Getting Started With The Preview
How can I trim packages and services as much as possible to use just razor pages? I don’t want to to...