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:

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:

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:

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:

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.

Tuesday, March 17, 2009 7:40 AM - 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.

Monday, April 27, 2009 9:47 PM - m

Genious! thanks!

Saturday, May 23, 2009 8:41 AM - durgesh pratap singh

hi priya

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

Tuesday, August 11, 2009 10:03 AM - 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.

Friday, September 18, 2009 1:21 PM - 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.

Thursday, August 5, 2010 3:21 AM - Natalie

Thursday, August 5, 2010 12:43 PM - Mike

Friday, January 14, 2011 9:57 AM - sivakumar

very nicethanking you

Tuesday, July 19, 2011 8:22 AM - pinal

thank you

Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your
comment relates specifically to the article above. More general comments can be posted through the form on the
Contact page.

Please note, all comments are moderated, and I end up deleting quite a lot. The kind of things that will ensure your comment
is deleted without ever seeing the light of day are as follows:

Recent Comments

Senad Mustafa 3/31/2015 8:57 AM
In response to
ASP.NET MVC DropDownLists - Multiple Selection and Enum Support
Hi Mike,
Thanks for the articles on dropdownlists. They are really great but I think you are one...