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

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

184
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$"

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.

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??

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.

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

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 HereOther wise if your using Microsoft Office 2003 use Jet.OleDb.4.0 provider

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 ...................

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??????????

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.

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

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

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

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'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.

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...

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 !!

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 .

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?

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

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.

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

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.

Hi suresh,i am trying this code but is show below error...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.

i change sheet name several times but still its give me same error on sheet name... so plz give me a gud solutions for this ASAP.. thnx for other tutorials i used that many times in my projects..

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)

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?

I have that type of problem any one help meThe 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 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 = Sheet1Sheet Name = Sheet1$Even I shared it for another user

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.

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.

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.

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

Greate saineshwar bagerisolve 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 2010The 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.

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,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

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 problemhttp://www.aspdotnet-suresh.com/2013/01/c-microsoft-office-access-database.html

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

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.

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.1234microsoft012hellohere 1234, microsoft,012,hello should go to 4 different columns

hi, this is prabhakar When i upload the XL sheet facing this Errorpls help meThe 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.

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

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 ExcelLine 33: Line 34: if (ddlValue ='DUComp')Line 35: {------------------------------------------------Code I have used:

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.

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.

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.

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.

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

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.

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