How to Import data from excel to SQL database using Asp.Net C#

Introduction
Here, I will explain how to import data from excel file to database using Asp.Net C#.

Description
There are lot of methods, to import data from Microsoft Excel worksheets into Microsoft SQL Server databases.For read more click here.
But we discussed this topic using Asp.net, For this we have to remember one thing, the naming of field names in excel and database table must be same. For an example we have an excel sheet with nine field, naming as challanno, challandate, sitename, customername, status, tonnercode, issuedqty, rejectedqty, & consumedqty. Now we create new table with same naming like:




Now we have to design .aspx page as shown below. The page, having a FileUpload control with upload button, on selecting the excel file you needs to click on Upload button, having one ListBox control to display the list of tables in your database and also having one GridView control to display the upload data.
Place the below code in you .aspx page.




Now, fisrt of all we have to bind our ListBox control on page load event in code behind. After that you have to write a code, to insert values from excel sheet to database, in OnClick event on code behind. here you can see that we are using OLEDB connection to read data from excel sheet. For this, include namespace shown below, in code behind

using sytem.data.Oledb

If we have excel work book in .xls (97-2003) format then we have to use Microsoft.Jet.OLEDB.4.0 and you have .xlsx (2007) format then we have to use Microsoft.ACE.OLEDB.12.0 connection. In the below code we are using both OLEDB connections to explain you. Place the below code in you code behind




In the above, we use viewdata_Click event to bind the gridview and  gvdetails_PageIndexChanging event for page index changing.

Now you can run application, it runs properly. May be you get an error like 'Microsoft.ACE.OLEDB.4.0' and 'Microsoft.ACE.OLEDB.12.0'. Don't to worried about your code, it's OK.

Error1:
When uploading .xls file your getting this error (The 'Microsoft .jet.OLEDB.4.0' provider is not registered on the local machine.

To solve this problem you have to do the following steps:
1. Download "2007 Office System Driver: Data Connectivity Components" on the web server from the below link.

http://www.microsoft.com/en-us/download/details.aspx?id=23734
2. After download installed these driver on your web server.




Also you have to "Enable 32-bit Applications to True" in IIS Application Pool for the web application if you want to upload the .xls files.

See the comprehensive Video Tutorial on YouTube to solve this problem:

https://www.youtube.com/watch?v=m4BwtCjs3z8





Error 2:

When uploading .xlsx file your getting this error (
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
To solve this problem you have to do the following steps:
1. Download  and Installed the "Microsoft Access Database Engine 2010" on the web server from the below link.
http://www.microsoft.com/en-us/download/details.aspx?id=13255





Output
:



I hope this post will help you.....if you like this post then share it with your friends.....!

7 comments:

  1. Give complete code for this

    ReplyDelete
    Replies
    1. Anil Babu....I already give the complete code....It is well tested and executing properly....

      Just click on show/Hide button in the above post.....

      Delete
  2. This is really useful thank you............

    ReplyDelete
  3. i m finding error then select excel file and clink inport button
    "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."

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

    i have 2 excel file on 2 format xls and xlsx, each has 1 sheet name abc but when i tried to import i got this error.

    ReplyDelete
  5. its showing error"the table is not in the expected format"

    ReplyDelete
  6. the sql table column names and excel sheet column names are same..then also it is showing error that the table is not in the expected format..

    ReplyDelete