Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

how to Import data from EXCEL to SQL Database in ASP.NET

Sep 15, 2010
Introduction

Here I will explain how to Import or insert data into SQL database from Excel spreadsheet using Sqlbulkcopy method.

Description

 I have searched for so many posts that explain static manner some of the posts are not clearly some of the posts are not supporting for latest excel files so many problems i faced by using those examples I have done application that will support for all excel versions and it will work for you without having any problems and it will dynamically you can upload excel sheet from anywhere from your computer.

First of all create Excel workbook as shown in image below and insert some data into it. Please design excel sheet like whatever I shown in image



I want to copy this data into a SQL Server Database Table, called Excel_table, with the same schema. Design your tables in database like this



Design your aspx page like this


<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<span style="color: Red">*</span>Attach Excel file
</td>
<td>
<asp:FileUpload ID="fileuploadExcel" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSend" runat="server" Text="Export" onclick="btnSend_Click"  />
</td>
</tr>
</table>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>

After that write the following code in codebehind button click 


protected void btnSend_Click(object sender, EventArgs e)
{
 String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";
//file upload path
string path = fileuploadExcel.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
}

Here I will explain about this query clearly "Select [ID], [Name],[Designation] from [Sheet1$]"
By using this query we are getting data from Sheet1 of Excel sheet that's why i have circled Sheet1 in Excel sheet if you change the Sheet1 name in excel sheet u need to change the Sheet1 in query also don't forgot.

Here don't forget to close the connection of your Excel file otherwise you will get error
 
 Download sample code attached

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

196 comments :

Amos said...

Thank You for this code, but may i ask,what does this line of code do?
(private String strConnection = "Data Source=MYCBJ017550027;Initial Catalog=MySamplesDB;Integrated Security=True";)
and how come when i run the program it says that it cannot find object "sheet1$"

Thank You very much

Suresh Dasari said...

hi Amos
private String strConnection = "Data Source=MYCBJ017550027;Initial Catalog=MySamplesDB;Integrated Security=True";

this my SQL Database connection here you need use your SQL Connection and in my post i already explain what Sheet1 is please check the post clearly

If we open Excel sheet by default it will contains sheet1,sheet2 and sheet3 check below of your excel sheet i already circled in image where is the sheet1 in excel please read the post clearly you will get clear idea.

Anonymous said...

hey when already present in table thn this code n orking

Anonymous said...

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

I am getting this error.Checked with sql configuration manager, sql browser and sql server both running. any suggestions??

Suresh Dasari said...

hi this problem because of database that is not allowing to access that particular database please check your database if you have permission or not

Anonymous said...

when i run the program error occured in dreader=cmd.ExecuteReader();

the error is OleDbexception was unhandled by user code.The Microsoft Office Access database engine could not find the object 'shh$'. Make sure the object exists and that you spell its name and the path name correctly.

please tell the solution

Suresh Dasari said...

hi i think this problem is because of your excel sheet name here in my excel sheet i gave it as Sheet1 check excel image that's why i have written query like Select [ID],[Name],[Designation] from [Sheet1$] have you given excel sheet name shh or not please check it once and try

Anonymous said...

change the name in excel sheet also.at the time also not work and having one doubt why use the $ symbol in Sheet1$.

Suresh Dasari said...

hi if you remove $ from Query it won't identify your excel sheet and throws error for that reason to identify our excel sheet with name we will use Sheet1$

Anonymous said...

k, I download your source code and create the excel and database like u, but not executed same error came. Please give the solution.

Suresh Dasari said...

hi just now again i checked attached code it's working fine i think that should be the problem with your local application check it once. Thanks

Akash Saikia said...

It is showing me this error
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

What to do?

Suresh Dasari said...

hi akash have u installed Microsoft Office 2007 in your system or not if you installed and still getting this error download the .exe file from this link and install it will solve your problem Click Here
Other wise if your using Microsoft Office 2003 use Jet.OleDb.4.0 provider

Varun Maggo said...

thanks maan! you are a life saver! i was stuck with Linq 2 SQL , this code worked like charm!

i will go with sql bulk copy

Arundhati said...

i have tried your code but its giving me the following error -
"The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly."
wat to do nw?????????????
plz help its very urgent ...................

Suresh Dasari said...

hi arundhati,
Please check above comments to solve your problem i already explained about same problem

Arundhati said...

can you please help me by telling how to make excel files as default data source of odbc in a system ???????

Arundhati said...

but i'm still getting the same error.can u please tell me why this error is occuring?????

Suresh Dasari said...

hi arundhati
Does you Excel file first sheet name is: 'Sheet1' ?

Please check it once if not then you need to change the bold section:

OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);

Arundhati said...

what will be the change in the code then if using OleDbDataAdapter ?????
please kindly send me the full changed code of the button click event please using OleDbDataAdapter .

Arundhati said...

you gave a link to install Microsft Excel Driver.
i hv installed it too but still my problem is not solved. jst can't undrstnd where to write the following code????????????????
If you are application developer using ODBC to connect to Microsoft Office Excel data, set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”
can u help??????????

Anonymous said...

+ path +
path stand for what ?

prabhakar said...

hi..when i run your code i am getting this error:
The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

i can't understand this error can you explain me...

Anonymous said...

This works great, but it seems to import rows from the excel sheet twice into my table. Some type of duplication.
Any ideas?

SATISH SAHAB said...

hey Suresh i m getting same prob yar The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly." i hv assign my sheet name sheet1 bt still that is getting prob

Anonymous said...

The Microsoft Office Access database engine could not find the object 'shh$'. Make sure the object exists and that you spell its name and the path name correctly.

Amit said...

hey thank you very much bro it helps a lot..

darshan said...

The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

can u explain me this
my email id is darshandoshi_272@yahoo.com
thanks

abaroth said...

Hello,
Nice post very helpful, I download your code sample and when I open it gives a error that fileuploadExcel does not exist in the current context, its the id of the file upload control right? cant find why does that :S

Anonymous said...

Hi,

This is very helpful, I am beginning to learn ASP.net can you also post on how to delete records? the list of the records is in excel sheet and delete it in sql database.

Thanks.

Jon

Anonymous said...

Thanks a ton...It helped me alot!!!

Anonymous said...

hi Iam also getting the same error

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

After creating the excel i have not change the Sheet1, so the name as it is "Sheet1$" but still it shows this error.. can you check the code.

Ajay Patil said...

Hello Sir,
i'm getting this error "Could not find installable ISAM." need ur help plz check it and let us know ur comments ...

adinarayana said...

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data. this error is displayed

Nutan said...

This code gives the error "Keyword 'provider not support'"
Tell me how to give excelConnectionString

Anonymous said...
This comment has been removed by a blog administrator.
Santosh said...

Hi Suresh.
i am getting below error how to resolve this issue pls help...
The Microsoft Office Access database engine cannot open or write to the file 'C:\Documents and Settings\santosh\Desktop\Book1.xlsx'. It is already opened exclusively by another user, or you need permission to view and write its data

Anonymous said...

I solved that "The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly." error...

to the file path of your excel file use the extension of your file such as .xlsx etc....

path ="C:\\Users\\Erandi\\Desktop\\MarksTest.xlsx"

Anonymous said...

Hi

I'm getting following error while importing excel data to sql using sql Bulkcopy.

The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.


But I have checked the sheetname it is same as mentioned.

vandna said...
This comment has been removed by the author.
Unknown said...

hi suresh ur rocking ur coding will helped to lots of people..

hear i have one problem with this case study.
when im running ur sample code in my system, i got the folloing error
"The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."

if ur free please solve my problem.
thank you for maintaining this site...

Anonymous said...

iam getting this Error.........

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

tell me pls how to remove this error...........

Anonymous said...

pls tell me how to remove this error.....


The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

rahul Upadhyay said...

Hi,
solve Error
please
Cannot update. Database or object is read-only.

Anonymous said...

after donin all above things ur code is not working...

Anonymous said...

"No value given for one or more required parameters."}

while click upon the button i am getting above error.

Anonymous said...

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Unknown

Source Error:


Line 27: excelConnection.Open();
Line 28: OleDbDataReader dReader;
Line 29: dReader = cmd.ExecuteReader();
Line 30: SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
Line 31: //Give your Destination table name

----------
I got this error.....plz tell me what 2 do....

Suresh Dasari said...

i hope that problem because you didn't create "Excel_table" in your database. Please create table in your database with those columns...

Vineeth said...

hai,

How to give the Sql Connection string with password. It shows exception as Keyword not supported 'Encrypt Database' . How to fix the error ? When i tried the same method in a db with no password . No problem. it works fine !!

Vinz said...

I am using sql compact server. can i do it in that ?

Prasad Chitikela said...
This comment has been removed by the author.
Prasad Chitikela said...

hi
"The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."
this is the error i am getting .

Anonymous said...

If Anyone having error like "the microsoft office access database engine could not find the object 'sheet1'" than try Server.MapPath("your xlsx file name")

Thanxx sir..

Anonymous said...

good

Anonymous said...

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

Anonymous said...

The Microsoft Office Access database engine could not find the object 'Sheet1$'. I'm having the same error and don't know how to fix. Can you please give me your comments,

Steve Cone said...

Hello Suresh,
This is awesome...got it to work the first time!

Thank YOU!

ramkumar said...

I did the same way, read the content to DataTable. This DataTable contains empty rows in the end. I can do foreach loop or use where condition in query to remove those rows but it wont work to my scenario.
So please tell me if there is any other better way to remove this empty rows?

Anonymous said...

Can we modify the excel data before uploading in asp.net

Anonymous said...

Hello Sir
I am getting the same error i use your code download the code bt error in sheet1 ,,plz resolve its very urgent to me & 1 More thing if it posble plz help me if user upload excel sheets & multiple sheets exists & choose sheets 1-1 then how plz Revert ...Navneet2509@yahoo.com

Anonymous said...

1 more question sir if i want to upload excel file via fileupload then in 1 excel sheet multiple files exists & choose 1 then how can i import in sql..
i alredy create this query .in that upload excel file & save in path & thn bind in dropdown bt issue is if i select any sheet in dropdwn thn nt save ..plz help if u hve any idea hw to import data in sql from selecteed excel sheet

Devi said...

Hi ,

Thank you for code , but i looking for importing excel sheet data in diff way, like your example ,in excel sheet have ID ,Name,Designation(or it have so many coliumns) .. and in Excel_table i need to save only ID and Name ,can you help me how can i import only ID,Name from excel sheet and show in grid view.

Thanks in advance..

Unknown said...

im trying install sqlserver2008R2 in this installing process at serverconfiguration process it is asking accountname,password. im entering system name and password then it is showing error. can i know what i want to enter

Anonymous said...

This code contains error man .. plz check

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

sravan said...

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

sravan said...

i follwed everything what suresh said above
but its working , can u post ur excel sheet

Unknown said...

Great code, but I have a question.

I do not have Office products installed on my server and the Excel file version is 2003; therefore, I installed the AccessDatabaseEngine_x64 package and tweaked the code from:

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

to:

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

It does work; however, when I start a new session of importing, it crashes and does not recognize the name "Sheet1$". Upon restarting the Server it allows me to again import an Excel file into another table. Then when I run again it crashes giving the same error.

I am using UltiDev Web Server Setup, to run the asp files.

Any suggestions?

Anonymous said...

executed successfully but is not binded to the database and why u have used grid view in this sample explain me sir ......

Anonymous said...

Error om IIS:-

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Waiting reply

Jakharji@Gmail.com

Anonymous said...

worked like a charm!

Thanks!

Anonymous said...

for all office versions this connection string can be used...ms office 2010, ms office 2007, 2003, 95-2000 etc

Anonymous said...

Hi suresh,

can u explain how to import text file data to database

Thanks in advance...

Unknown said...

sir i have an error while debug the code.
error is
"The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly"
then what to do?

Sravani said...

hi
i do have the same error.My excel file sheet name is Sheet1 as you have given.but it is throwing he error

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

Please tell how to solve this?

Milind said...

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

Use this connection string to solve above error.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Your File Name") + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

Unknown said...

hi suresh ur rocking ur coding will helped to lots of people..

how to insert multiple tables at a time using 3tier architecture in asp.net,and bulk insert for gridview page......

Unknown said...

this error

The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Unknown said...

Hi every one,

I have executed this code and i am getting an error in the line:

OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);

as

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

Could u plz reply me with the solution

Rajkumar Palnati said...

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

this is my error how to rectify this ?
xl is 2007.

bbb said...

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

i am getting the above error, what does it mean ?

udham Singh said...

Hi suresh,

how to i get the table name, in which i want to insert the data.

Unknown said...

hi suresh!
The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

same error i m getting
please help me out

Anand Upadhyay said...

I have that type of problem
any one help me
The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

N.Shivesh said...

Hii Suresh Ji ,

I successfully converted data offline but online
"The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."

Problem Exists

File Name = Sheet1
Sheet Name = Sheet1$
Even I shared it for another user

but i still facing same problem....

thanking u in anticpation

Naveen Jha said...

Hi all who is having problem of "The Microsoft Office Access database engine could not find the object 'Sheet1$'"
Modify your code
protected void btnSend_Click(object sender, EventArgs e)
{
//file upload path
string path = fileuploadExcel.PostedFile.FileName ;
fileuploadExcel.SaveAs(Server.MapPath("orders.xlsx"));
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("orders.xlsx") + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

Unknown said...

in my local system there is no oledb12.o version
what i am doing sir

Unknown said...

thanks for this code ....

Unknown said...

Hi I got this Error...The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Please give me the solution...

Saineshwar bageri said...

https://www.box.com/s/cn3yhfr7l9uyyt5anoyn

Link To downLoad and See Code

Take Your Help Created some New code for People

who is having problem of "The Microsoft Office Access database engine could not find the object 'Sheet1$'"



Thanks To Suresh Dasari Sir

Carlos Zagal A. said...

This doesn't work in Chrome or firefox browsers!!!!!!!!!!!!!!!!!!!!

Saineshwar bageri said...

it work on firefox

Saineshwar bageri said...

Carlos Zagal A.

just check this code u can directly download from this link

https://www.box.com/s/cn3yhfr7l9uyyt5anoyn


Ekalavya said...

hi suresh i appreciate your gr8 wrk..m stuck up with following looking forward for a solution from u.
The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.

anil said...

after deploy on iis 7.5 its give following error
Access to the path 'C:\inetpub\wwwroot\inventory\Uploads\sample.xls' is denied.

prithviraj said...
This comment has been removed by the author.
Suresh Dasari said...

whoever is having problem like "The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data." check below article to solve that problem
http://www.aspdotnet-suresh.com/2013/01/c-microsoft-office-access-database.html

Anonymous said...

hi suresh It kapil From Faridabad and im ur big Fan.
Actually i just want to know one thing I want to see My excel file in grid on click button of export. How I can?

Anonymous said...

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Anonymous said...

Hai Sir,

Dis Is Vani, I did the same way, But i am getting the below error

It is already opened exclusively by another user, or you need permission to view its data.

Anonymous said...
This comment has been removed by a blog administrator.
Saineshwar bageri said...

It is already opened exclusively by another user, or you need permission to view its data.


just check this code u can directly download from this link

https://www.box.com/s/cn3yhfr7l9uyyt5anoyn

Unknown said...

Exception Details: System.Data.OleDb.OleDbException: The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Source Error:

Line 44: //Create OleDbCommand to fetch data from Excel
Line 45: OleDbCommand cmd = new OleDbCommand("Select [Rollno],[Name],[Marks] from [insert$]", excelConnection);
Line 46: excelConnection.Open();
Line 47: OleDbDataReader dReader;
Line 48: dReader = cmd.ExecuteReader();


Source File: d:\.net project\copy of all\WebDataConnected\StudentNew.aspx.cs Line: 46


PLEASE LET ME KNW WHY IT IS SHOWING THIS AS SOON AS POSSIBLE

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

hi very nice code ... but i need while export whether we can able to validate for ex: if already inserted data we dont want to insert so how can validate in excel any option is there

Kavitha said...

hi,

When multiple users are using the functionality and if we are using one table to store the data and flush it at the end, an issue is been created like a dead lock in sql side, since more than one user is accessing the table, this kind of issue is happening. Suresh, can you pls help

Unknown said...

Hi,

This is really helpful for me. I just want to know this line "@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
"

plz sir rply.
Thanks.
Biswanath

Unknown said...

Greate saineshwar bageri
solve
The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.
thankx so much

Santosh Pal said...

hi Suresh sir.....
i am gettiing error when i write same code in Visual Studio 2010
The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

and when i write same code in Visual studio 2008 its working perfect

Plz help

Unknown said...

working ....

Unknown said...

in my project im trying to upload data from excel into sql server and filter column .eg: pim ,rib,rms,ssrs etc sub topic names are present in one column we should group these into tower called oracle retail.likewise grouping should be done for 9 towers data.how to do this,suresh sir could you plz help me from this

Atul Enjoying with studies said...

i also get this error...
"The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly."
whoever successfully solved this plz share their views.

Unknown said...

Hello Sir this code helped me a lot, but i have a question , can i import data in more than one table in SQL ?

Thanks & Regards

Shivi

Anonymous said...

Hello,
Nice post very helpful, I download your code sample and when I open it gives a error that fileuploadExcel does not exist in the current context, its the id of the file upload control right? cant find why does that :S

Anonymous said...

"External table is not in the expected format"
this error coming for me :(

Bharath said...

plz..give me solution......for this problem it's matching with sheel1$
eventhough i got error like....

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

Bharath said...

hi suresh plz solve my pb......

Rahul Pai said...

If I want to read data in excel file from 10th row, and insert into sql server !!.. please guide

Anonymous said...

Hello Suresh

i am havin the same problem again even i have chechked ur link and had followed already............... "The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data." check below article to solve that problem
http://www.aspdotnet-suresh.com/2013/01/c-microsoft-office-access-database.html

Unknown said...

plz somebody help .. I want to insert data starting from 10th row as first 9 rows are textual information about that excel document... plzz help with query

Help said...

hi suresh when i am running this project getting an error like this
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
please help me

Anonymous said...

An exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code


i got this error

Anonymous said...

Thanks the code is very and it is very helpfull.

Anonymous said...

Guys,
Fileupload.Postedfile.filename should get complete physical path of the from local machine.But unfortunately Firefox willn't fetch complete file name instead only the file name at runtime.so use IE as default browser and try,to avoid "The Microsoft Office Access database engine could not find the object 'Sheet1$'"

Unknown said...

Hi suresh Thank U for ur code regarding this.., Daily i ll study ur Articles & i ll practice.., Here u r using Backend connection is OleDB it's But i would like to work with Sql connection,,. for this i kept as Sql connection instead of OleDB connection but i can't get dat could u please send the code please

Unknown said...

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

Unknown said...

Hi, This is Sandip.
Here I would like to give the solution for the below problem:

Problem:
"The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly."

Solution:
The file path generated with the code is not correct. Try to specify absolute path for the file the and check.

For example : instead of
string path = fileuploadExcel.PostedFile.FileName;

You could try:
string path = @"C:\Users\sandip\Desktop\abc.xslx";

Hope it will help you all .


Rajesh M Somvanshi said...

Hi I am Rajesh here your code is very nice. it is Perfectly working.

Anonymous said...

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

avinash said...

executed successfully but is not binded to the database and why u have used grid view in this sample explain me sir ......

Niraj Kumar jha said...

I have a excel file which contains text and image . I want to read it and store it into the database. Can u help me to resolve the issue

Anonymous said...

How can connect from sql express sever 2005 please send code

Anonymous said...

what is code for sql connection?

Anonymous said...

when i write data into table it thorws me error related to datetime format ...is their any way to solve this problem from asp.Net cs page directly....?

avinash said...

hi,

getting error

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

i have checked in the excel sheet as u red circle in picture it is same still getting error
bt the code is working in 2005 without any error, bt y error in 2008, what need to do.

Unknown said...

i need lock particular column ... Excel data must be yo database data ... in that if 3 columns there means one column should be read only rest of them editable ...
Plzzz help ...
Thanks in advance...

v.manikandaboopathi said...

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

i have checked in the excel sheet as u red circle in picture it is same still getting error
bt the code is working in 2007 without any error, bt y error in 2008, what need to do.

Anonymous said...

Hello sir
Please Help me..
I am facing such type of problem when i use this code..........::

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

Anonymous said...

Hello sir,

I want to transfer data from asp.net to excel how to do this

Anonymous said...

hello sir,

i want to create procedure use this procedure in asp.net after clicking the button sql data is transfer into excel format how to do this i am trying to create query to transfer data sql to excel

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

----------------------------------------------------------

USE [Excel];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\contact.xlsx;',
'SELECT * FROM [Sheet1$]')
SELECT FirstName, LastName
FROM Name
GO

------------------------------------------------

after execute they get following error

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

how to solve this .............please help me

i need this ............

anyway your all asp.net programm and example is

very helpful and nice keep it.

Anonymous said...

Hi suresh,

sometimes data is cutting while importing from Excel cell into SQL server 2008.
i think it is not imported the data which is having large size i.e greater than 255 characters. Please let me know the solution for importing the large size cell data.

Anonymous said...

good job.i have a question :how to make values ​​inserted from excel to sql unique and do not repeat.plz help me.

Anonymous said...

sir actually i learnt .NET but due to gap and loss of practise i reached such a position that iam unable to open a form in which i can do some basic validations so could u please tell me abt how to start myself again with ASP.NET... would be thankfull to you.

Anonymous said...

Hi Suresh ,
This is bit urgent,
I have millions of rows in a fixed lenght file (notepad). can you please let me know how to extract the data from the fixed length file and copy it to temporary sql server data tables.
data is in this format in a notepad.
1234microsoft012hello
here 1234, microsoft,012,hello should go to 4 different columns

Also let me know which approach is best

Thanks for your help

Anonymous said...

hi ,this is kapil,
sir want to insert the bulkdata by using 3tier technique,but i havn't idea to make it or its coding
could u plz help me out.

Unknown said...

hi, this is prabhakar When i upload the XL sheet facing this Error
pls help me
The Microsoft Office Access database engine could not find the object 'Name$'. Make sure the object exists and that you spell its name and the path name correctly.

Unknown said...

Hi Suresh,
This post is very useful for me to import excel into database. Can you help me out how to insert the multiple sheets from one excel?

Unknown said...

hi,

Its not working in godaddy server. Can u plz suggest me for this.
Its giving error:
"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

Anonymous said...

please replay ....
how to import Ms Access database(tables) to SQL database..........................):

Anonymous said...

I am getting below errors:

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS1012: Too many characters in character literal

Source Error:

Line 32: //Create OleDbCommand to fetch data from Excel
Line 33:
Line 34: if (ddlValue ='DUComp')
Line 35: {


------------------------------------------
The code I have used:

Anonymous said...

The code I just changed:
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;


public partial class _Default : System.Web.UI.Page
{
private String strConnection ="Data Source=SENPWM10519387\\SQLEXPRESS;Initial Catalog=QATest;Integrated Security=True";


protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnSend_Click(object sender, EventArgs e)
{
//file upload path
string path = fileuploadExcel.PostedFile.FileName;
//ExcelCheck
string ddlValue = ddlImportType.SelectedValue;

//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel

if (ddlValue ='DUComp')
{
OleDbCommand cmd = new OleDbCommand("Select [Deployment Unit],[Deployment Scenario],[Wave Plan], [User ID], [Country], [Computer Name (Old)], [New Computer Type], [New Tag], [Application Readiness] from [Deployment Unit Computer$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "DUComputer";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
else if (ddlValue = 'DUApp')
{
OleDbCommand cmd = new OleDbCommand("Select [User ID], [Application], [Status], [Computer Name (Old)], [Package-ID] from [Deployment Unit Apps$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "DUApplications";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
else
{
}
}
}

Peal said...

I am having this error while trying the below code, please help:
------------------------------------------------
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS1012: Too many characters in character literal

Source Error:

Line 32: //Create OleDbCommand to fetch data from Excel
Line 33:
Line 34: if (ddlValue ='DUComp')
Line 35: {
------------------------------------------------
Code I have used:

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;


public partial class _Default : System.Web.UI.Page
{
private String strConnection ="Data Source=SENPWM10519387\\SQLEXPRESS;Initial Catalog=QATest;Integrated Security=True";


protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnSend_Click(object sender, EventArgs e)
{
//file upload path
string path = fileuploadExcel.PostedFile.FileName;
//ExcelCheck
string ddlValue = ddlImportType.SelectedValue;

//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel

if (ddlValue ='DUComp')
{
OleDbCommand cmd = new OleDbCommand("Select [Deployment Unit],[Deployment Scenario],[Wave Plan], [User ID], [Country], [Computer Name (Old)], [New Computer Type], [New Tag], [Application Readiness] from [Deployment Unit Computer$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "DUComputer";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
else if (ddlValue = 'DUApp')
{
OleDbCommand cmd = new OleDbCommand("Select [User ID], [Application], [Status], [Computer Name (Old)], [Package-ID] from [Deployment Unit Apps$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "DUApplications";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
else
{
}
}
}
----------------------
In the Default.aspx page I just add dropdown list like below






Peal said...

asp:DropDownList ID="ddlImportType" runat="server" AutoPostBack="true"
asp:ListItem Text="--Select Import Type--" Selected="True" asp:ListItem
asp:ListItem Text="Deployment Unit Computers" Value="DUComp">

Anonymous said...

when i use export div to msword image is not show in the ms word any idea about it

Unknown said...

Hi,
Thank you for the solution.
I have tried your code Suresh. It is working fine in IE8. But when i tried using chrome browser,then i am getting "The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly." on dReader = cmd.ExecuteReader();

I tried to provide the absolute path of the .xlsx file where it resides on datasource. but still i am getting the same issue.

Please help me.
Thank you in advance!

Unknown said...

but how to import excel data checking row by row to database .so that blank row will not be import to database

Anonymous said...

you did a great work Mr. Suresh.
i write the same code as you did.
but in my VS2008, OleDBConnection, OleDBCommand, or OleDBDataReader doesn't exist and there are some blue underlines.

i think it about the reference.
But, when i add using System.Data.OleDB; , it still doesn't work.
i need you help about my problem
thank you

Anonymous said...

no error on my code. but it still doesn't work.
please help me.
this is my code:


protected void BtnUpload_Click(object sender, EventArgs e)
{
s = WebConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
sqlconn = new SqlConnection(s);

string path = fileupload.PostedFile.FileName;
string ExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;HDR=YES;";
OleDbConnection excelConnection = new OleDbConnection(ExcelConnectionString);
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlconn);
sqlBulk.DestinationTableName = "datafile_pesertadidik";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

i need help. thanks

jawa said...

Thank you Friend for your code.

Anonymous said...

hi suresh i want basic of asp.net

Unknown said...

thanks suresh......u help lot of dotnet developers.....this code is for OLEDB...i want code for sqlconnecton...pls give that code

Rohan Kota said...

plz help me its urgent

Unknown said...

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
Wat to do sir??

Anonymous said...

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

i got this error massage, as i use the sheet1 in query and excel file

Unknown said...

hi suresh,am using sql server instead of ms access,then i used sql conne instead of oledb,but it is showing error at provider,i also used sqloledb provider but am getting error,can u please suggest me what should i do?

Ankit Mishra said...

Hi Suresh first of all thanks for writing this blog and helping many coders and thanks for this code too,
I have a small problem specially with "Sheet1$", can we do something else for this because same thing i am designing for PC support people so they are not going to deal with the code so can you help me so that which ever excel sheet i use it should replace it with that and import the data from excel to Sql Data base.

Anonymous said...

Great post, and very very simple code.

Anonymous said...

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Arjun Dhilod said...

sir i am received this error:-
The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Unknown said...

dear Sir,

my Name is Vishnu


when i run your code i am getting this error:
The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

i can't understand this error can you explain me...

Harry said...

Bro,. This code works fine, however I have to give a validation that first it should check the ID in excel sheet is already available in database table. If the ID exist it should not import tat particular row which is already existing and rest of the rows which are not available in the database table should be imported. Please help me with this situation Bro.... email id haranblazer@gmail.com

Anonymous said...

my header is in 9th row. How to insert those datails?

Add said...

excelConnection.Open(); error

please help error description

The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Unknown said...

hai suresh

i want code for openoffice calc to sql server,pls help me

Raj666 said...

Hello,
how can we Import Changing Excel data(data here changes every second) into SQL DB?

varun khandodiya said...

hi suresh!
The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

same error i m getting
i have checked everything but error still occur..
please help me out

Unknown said...

To get rid with this error- The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Make a folder in root directory of your project then use full path of file.

string FileName = FlUploadcsv.FileName;
string path = string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));
FlUploadcsv.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbCommand command = new OleDbCommand("SELECT * FROM [Employee$]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);

OleDbcon.Open();
// Create DbDataReader to Data Worksheet
DbDataReader dr = command.ExecuteReader();

// SQL Server Connection String
string constr = @"Data Source=amit-pc;Initial Catalog=db_DARS;Integrated Security=True";

// Bulk Copy to SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy(constr);
bulkInsert.DestinationTableName = "EmployeeDetails";
bulkInsert.WriteToServer(dr);
OleDbcon.Close();

hope it will help

shilpa said...

Hi,

I'm using the same code to import data but along with the code a few rows with NULL values are also copied each time i do an import. I'm not able to figure what the problem is.

Unknown said...

Cannot update. Database or object is read-only.

Unknown said...

it showing the data from excel sheet in gridview but data is not inserting into database table why?..any one solve this problem

Administrador said...

Hi, tnx for the code, now, i dont understand where i must to write the connection and path to my sql database and table, can u help me with this?? tnx a lot !!!

Cool_Coding said...

Hi,
I have try this code, but get following error msg.

The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

kumar said...

hi suresh sir,
i am gettting an error which states that
"the Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data"
could u pleasse reply to sravan.chaganti@gmail.com

Anonymous said...

how to Import data from EXCEL to MS Access Database in ASP.NET , Please give sample to me (sankaradass@gmail.com)

Unknown said...

Hi..



i am new to asp.net,

in my project i have a dropdownlist="values to be inserted into database"
fileupload="file(excel) to be inserted into database"
submit button

how do i do bulk insertion along with the values of the dropdownlist?

i have tried with sqlbulkcopy but it does't work for me ...




please suggest me with your valueable suggetions to overcome this problem.......

Web-blogs said...

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.


why im getting above error

Web-blogs said...

To get rid with this error- The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Make a folder in root directory of your project then use full path of file.

string FileName = FlUploadcsv.FileName;
string path = string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName));
FlUploadcsv.PostedFile.SaveAs(path);

Unknown said...

if i have data in two sheet named sheet1 and sheet2 then what will be the code.

Unknown said...

Hi suresh i am getting this error please solve the problem"External table is not in the expected format".

GAURAV said...

thank you soo much suresh.... your post are very informative and I am trying to learn lot of things from them.

Unknown said...

Hi Suresh,
Can we do the same to MySQL instead of Sql server

Unknown said...

The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

as i change the permission still i get same error.. what to do ??

varun pujara said...

hello sir,
i faced error like
The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
so please give me a solution of this error.

varun pujara said...

i tried this demo and run very well but sir when i applied this code for live server that time i got error like The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
and i read your article on this error but i never uploaded excel file in any folder.so please give the solution in deeply.

Unknown said...

Thanks a lot !! Appreciated .. solved my work to a great extend...
Thanks , its working for me..

Unknown said...

Hey Can you please help me same code with help of stored procedure..
I want to perform this task with the help of stored procedure, because i have to insert some other data too.
Thanks in advance,
You can replay on this email id: akiporwal99@gmail.com

Anonymous said...

Hello Sir,
How to import excel sheet with images and data to database using asp.net c#?
I have reffered the given example but it contains only data not images ,i want to import the excel with images.
Please help me Sir,
Thanks

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.