Introduction
Here I will explain how to Import or insert data into SQL database from Excel spreadsheet using Sqlbulkcopy method.
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 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
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 Email
|
|||
196 comments :
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
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.
hey when already present in table thn this code n orking
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??
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
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
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
change the name in excel sheet also.at the time also not work and having one doubt why use the $ symbol in Sheet1$.
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$
k, I download your source code and create the excel and database like u, but not executed same error came. Please give the solution.
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
It is showing me this error
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
What to do?
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
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
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 ...................
hi arundhati,
Please check above comments to solve your problem i already explained about same problem
can you please help me by telling how to make excel files as default data source of odbc in a system ???????
but i'm still getting the same error.can u please tell me why this error is occuring?????
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);
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 .
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??????????
+ path +
path stand for what ?
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...
This works great, but it seems to import rows from the excel sheet twice into my table. Some type of duplication.
Any ideas?
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
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.
hey thank you very much bro it helps a lot..
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
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
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
Thanks a ton...It helped me alot!!!
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.
Hello Sir,
i'm getting this error "Could not find installable ISAM." need ur help plz check it and let us know ur comments ...
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
This code gives the error "Keyword 'provider not support'"
Tell me how to give excelConnectionString
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
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"
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.
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...
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...........
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.
Hi,
solve Error
please
Cannot update. Database or object is read-only.
after donin all above things ur code is not working...
"No value given for one or more required parameters."}
while click upon the button i am getting above error.
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....
i hope that problem because you didn't create "Excel_table" in your database. Please create table in your database with those columns...
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 !!
I am using sql compact server. can i do it in that ?
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 .
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..
good
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
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,
Hello Suresh,
This is awesome...got it to work the first time!
Thank YOU!
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?
Can we modify the excel data before uploading in asp.net
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
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
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..
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
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.
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 follwed everything what suresh said above
but its working , can u post ur excel sheet
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?
executed successfully but is not binded to the database and why u have used grid view in this sample explain me sir ......
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
worked like a charm!
Thanks!
for all office versions this connection string can be used...ms office 2010, ms office 2007, 2003, 95-2000 etc
Hi suresh,
can u explain how to import text file data to database
Thanks in advance...
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?
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?
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'";
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......
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.
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
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.
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 ?
Hi suresh,
how to i get the table name, in which i want to insert the data.
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
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.
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
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();
}
in my local system there is no oledb12.o version
what i am doing sir
thanks for this code ....
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...
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
This doesn't work in Chrome or firefox browsers!!!!!!!!!!!!!!!!!!!!
it work on firefox
Carlos Zagal A.
just check this code u can directly download from this link
https://www.box.com/s/cn3yhfr7l9uyyt5anoyn
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.
after deploy on iis 7.5 its give following error
Access to the path 'C:\inetpub\wwwroot\inventory\Uploads\sample.xls' is denied.
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
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?
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.
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.
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
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
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
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
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
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
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
working ....
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
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.
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
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
"External table is not in the expected format"
this error coming for me :(
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.
hi suresh plz solve my pb......
If I want to read data in excel file from 10th row, and insert into sql server !!.. please guide
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
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
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
An exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code
i got this error
Thanks the code is very and it is very helpfull.
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$'"
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
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.
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 .
Hi I am Rajesh here your code is very nice. it is Perfectly working.
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.
executed successfully but is not binded to the database and why u have used grid view in this sample explain me sir ......
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
How can connect from sql express sever 2005 please send code
what is code for sql connection?
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....?
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.
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...
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.
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.
Hello sir,
I want to transfer data from asp.net to excel how to do this
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.
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.
good job.i have a question :how to make values inserted from excel to sql unique and do not repeat.plz help me.
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.
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
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.
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.
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?
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."
please replay ....
how to import Ms Access database(tables) to SQL database..........................):
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:
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
{
}
}
}
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
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">
when i use export div to msword image is not show in the ms word any idea about it
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!
but how to import excel data checking row by row to database .so that blank row will not be import to database
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
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
Thank you Friend for your code.
hi suresh i want basic of asp.net
thanks suresh......u help lot of dotnet developers.....this code is for OLEDB...i want code for sqlconnecton...pls give that code
plz help me its urgent
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??
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
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?
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.
Great post, and very very simple code.
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)
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.
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...
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
my header is in 9th row. How to insert those datails?
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.
hai suresh
i want code for openoffice calc to sql server,pls help me
Hello,
how can we Import Changing Excel data(data here changes every second) into SQL DB?
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
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
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.
Cannot update. Database or object is read-only.
it showing the data from excel sheet in gridview but data is not inserting into database table why?..any one solve this problem
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 !!!
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.
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
how to Import data from EXCEL to MS Access Database in ASP.NET , Please give sample to me (sankaradass@gmail.com)
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.......
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
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);
if i have data in two sheet named sheet1 and sheet2 then what will be the code.
Hi suresh i am getting this error please solve the problem"External table is not in the expected format".
thank you soo much suresh.... your post are very informative and I am trying to learn lot of things from them.
Hi Suresh,
Can we do the same to MySQL instead of Sql server
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 ??
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.
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.
Thanks a lot !! Appreciated .. solved my work to a great extend...
Thanks , its working for me..
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
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
Note: Only a member of this blog may post a comment.