Save Insert Export Import Excel Data Into Sql Server SqlBulkCopy ASP.NET

This post explains How To Save Insert Or Export Import Excel Data In to Sql Server Database Table Using SqlBulkCopy In ASP.NET

First of all create a Excel workbook as shown in image below and insert some data into it.

Export Import Insert Excel Data Into Sql Server Using SqlBulkCopy

Create a table in SQL database with following schema



Now write this code to insert data into SQL table

public partial class _Default : System.Web.UI.Page
{
string strConnection = ConfigurationManager.ConnectionStrings
["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
//Create connection string to Excel work book
string excelConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Details.xls;
Extended Properties=""Excel 8.0;HDR=YES;""";

//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],[Location] from [Detail$]",
excelConnection);

excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Details";
//sqlBulk.ColumnMappings.Add("ID", "ID");
//sqlBulk.ColumnMappings.Add("Name", "Name");
sqlBulk.WriteToServer(dReader);
}
}


If there are more columns in your database table or excel workbook and you want to insert data in some of them than you need to add ColumnMappings like this
sqlBulk.ColumnMappings.Add("ID", "ID");
sqlBulk.ColumnMappings.Add("Name", "Name");


End result will be like this


Hope this helps

Download the sample code attached



Other Posts:
1. Detecting Session Timeout and Redirect to Login Page in ASP.NET
2. JavaScript window.close() not working / does not work in firefox
3. Merging GridView Headers to have multiple Headers in GridView
4. Detect Browser refresh to avoid events getting fired again in ASP .NET
5. Search records in GridView and highlight result with AJAX

Other SQL Server articles:
The backup set holds a backup of a database other than the existing database-Sql Server Error 3154
Install configure and troubleshooting sql server reporting services 2005
Ms sql server bulk insert method to import bulk csv data into database
If you like this post than join us or share

43 comments:

Vick said...

download link is not working....can you please give the code for the app...


Vick said...

I am unable to download the sample code....please help


amiT jaiN said...

@Vick:

I've fixed the download link
You can now download the source code


Niko said...

still not working...


amiT jaiN said...

@Niko:

please tell me what's not working for you ?


Anonymous said...

hi

can i use the same code to import from a csv file and if not can i just change the excel conection string


amiT jaiN said...

@Above:

If you want to Insert CSV data in ms sql server thenread this post


Anonymous said...

how to import excel data into sql table using asp code


amiT jaiN said...

@Above

I have provided asp.net code in the article itself


zahid said...

i have used your code sample. every thing is fine. but it giving me error , when executing reader. the error is .

"The Microsoft Jet database engine could not find the object 'Details'. Make sure the object exists and that you spell its name and the path name correctly."

the file name is Details.xls

Please Explain


Anonymous said...

Hi there,wondering to import excel data from certain rows (let say 3rd row to end of data rows)because of my header! please advice and Can we filter the data based on certain values of a excel column. ex: In the column countries, would like to import only rows with USA.
Your help really appreciated.


pk.narang@yahoo.ca said...

This is a simple chit.Do you have code to write to excel cells.


Anonymous said...

Hey,

If we run the same example it insert records two time. what changes we need to do if we want to update the same records in SQL. thanks


gerardo said...

Hi, I´ve tried the code but it sends me the next error.

System.Data.OleDb.OleDbException: No specified values for some of the required parameters.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()


devajit said...

i have used your code sample. every thing is fine. but it giving me error when i click the button. the error is .
"No value given for one or more required parameters".plz send me devajit.debu.mca@gmail.com


Anonymous said...

i am facing the same error as mentioned above:


Anonymous said...

Muchas gracias!!
Googleando encontre tu post
me sirvio mucho

Gracias!


mensajes claro said...

Thanks.


mensajes claro said...

This really help me a lot , Thanks for sharing.


Anonymous said...

this really helped me a lot , Thanks for sharing.


Chitrali said...

What happens if the data in excel file contains single quote '. Will you kindly provide me the solution as my data in excel file has single quotes.


Anonymous said...

I need some help, I run this code and i found an error
"'Detail$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long." could you explain why it happen?


amiT jaiN said...

@Anonymous: Plz change Detail$ to Details$ in the code behind and it should run fine


serim said...

its 'details' in every row but gives the same error dude?


Anonymous said...

hi i am using this code working fine.if i add any records in the Excel Sheet then it is not updating in the database .what should i do?
My Requirement is:I am using button called browse then after selecting a file what are the modifications we done in the Excel sheet then it has to be update in the database table.the previous data should be deleted.please help me quick .Thank you


Anonymous said...

hi,thanx for this article.but it is working when I add any rows in Excel Sheet.
Can you Help me Please?


piyush said...

when we rename the excel file than its not working,it is giving a error. "'Sheet$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."


amiT jaiN said...

@Piyush : i have hard coded the excel file name (Details.xls) in code for this article so obviously when u change the excel file name, make changes accordingly in code as well else u'll get error


piyush said...

@amit: Thanx for it but now have another problem.I have a database in which i am using id that is identity and primary key that will generate automatically.i will not take any id in excel sheet. now how to insert data into database.


amiT jaiN said...

@Piyush : having id column in database won't be a problem, just insert the data in other columns as shown in code, DB will auto generate ID,

If this doesn't work then u can map columns and then insert data as shown below

sqlBulk.ColumnMappings.Add("ID", "ID");
sqlBulk.ColumnMappings.Add("Name", "Name");


Arundhati said...

i'm getting the following error-
"'Sheet$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."

when i'm running the code.please help !!!!!
its very urgent.


Arundhati said...

i'm having the error-"'Details$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."

what to do ????
please help
its very important


shivani said...

@Piyush : your code is correct but I want to access the excel file which is placed on the client machine.
Client will browse the file from his machine and on the button click the data from the exfcel file is uploaded in the database.
Your code is working fine on the local machine when the file and the code both are on the same machine. Kindly help!


Anonymous said...

does it work if i want to import image?


kshitij said...

When I run this code it gives error as below


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

Plz help me , its very urgent.


amiT jaiN said...

@kshitij: Please make sure you have put your excel file in C drive with name Details.xls

or if you excel file is somewhere else then change the location is excel connection string accordingly

string excelConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Details.xls;


Gaurav Balyan said...

i want to say that the detail.xls name refer to the name of the excelsheet and the pathis given in connectionstring to the file but the name detail$ refers to the name of the excelsheet(Sheet11,Sheet2,Sheeet3) inside the ExcelFile do careful in assigning the name to file hope it helps


K.Rani said...

hi,
we have a internal office application.
we are maintaining the database for that particular appln.,
now we want to update the exsting tables in additional information.
please help me in this regard.
we want to update through Toad s/w.


Anonymous said...

Thanks so much, your code helped me a lot of.


Suneel said...

Hi,

Can you help in case we want to do the opposite (I want to write data from a C sharp application using MySQL to excel). I tried some solutions but they don't work for huge amounts of data.

Thanks in advance.


Anonymous said...

Hi,

When I import into an SQL table, the data for the row order do not follow the row order of the Excel file, this is especially obvious when Excel row counts are huge. It may for example, the table's first fow is row number 61 of the Excel table.

Coluld you help? Thank.

Regards,
Richard


mayank singh said...

please send me code for exporting data into MS Excel from Sql Table in C#


Anonymous said...

This comment has been removed by a blog administrator.


Find More Articles