Tuesday, May 19, 2009

How to Import Excel Spreadsheet Data into SQL Server Database using ASP.NET C#




Please visit my new Web Site WWW.Codedisplay.com



After developing an web application when you ask the operator to enter all existing data into the input interface then most of the cases the operator told you i have an Excel sheet please upload those data into the database which will reduce my time as well as human errors. This is the most common scenario. So you have to develop an interface where user can select an excel sheet to import into the SQL SERVER database.

Fortunately ADO.NET 2.0 provide us a new feature named SqlBulkCopy which gives you DTS like speed to transfer Excel sheet data into the SQL SERVER table. Here i will show you by an example how you can easily do this. At first open the Excel Sheet & determine which columns you want to transfer then if you have already a table then you need to map columns between Excel & SQL SERVER. Other create a table in your database as like as Excel Sheet. The following image shows you data structure & sample data of both SQL SERVER and Excel Sheet:


Now add an aspx page into your project. Add a button to run the import command. Let the button name is cmdImport. Now under button click event write the following code:
Don't forget to import the following two namespaces:
using System.Data.SqlClient;
using System.Data.OleDb;

protected void cmdImport_Click(object sender, EventArgs e)
{
string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
using (sSourceConnection)
{
string sql = string.Format("Select [MSISDN],[Name],[Company],[Status] FROM [{0}]", "Sheet1$");
OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName =
"tblAgent";
//You can mannualy set the column mapping by the following way.
//bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");
bulkCopy.WriteToServer(dr);
}
}
}
}

Now run the application & see the performence that how fast your application import all data into the databse. One another thing if you want to try another sheet to import then run the above code by modifying the sheet name from the sql.

Sample Output:


Want to export GridView data to EXCEL then read:
http://shawpnendu.blogspot.com/2009/03/export-gridview-data-to-excelword-text.html

23 comments:

Anonymous said...

I tried your code and it works fine in my local computer but when I published it or uploaded it into the server. It doesn't work. The problem is when loading it in my server (web host). It seems it can't open or access the xls file and prompted this error "c:\agentlist.xls is not a valid path. Make sure that the path is spelled correctly and that you are connected to the server on which the file resides" I hope you can give me a solution. Thank you.

Saion Roy said...

Server means different PC? Make sure that the file is there. If the file exist then give read permission to aspnet user. One another thing is for live deployment you have to upload the file from client PC to server PC first. Let me know if you cant.

Anonymous said...

Cool I understand it already but is there a way to insert the record while not uploading the file to the server(webhost). In any case. i would like to hear from you about how to upload it to the server and erase it after the record has been inserted. you will be my great help on this. :)

Anonymous said...

Hi,

Well I also need to add the Current DateTime ( not defined in excel file) to Database , How would I do it. i.e. How could I add Current datetime to bulkcopy although Datetime is not present in Excel File.

Saion Roy said...

Anonymous posted August 7, 2009:
First of all in general you can't process a file without uploading it. To upload the file you can read my post:
shawpnendu.blogspot.com/2009/05/file-upload-with-aspnet-c.html

To delete the file from server you have to import namespace IO Then execute File.Delete() method.

Anonymous posted August 10, 2009:
A lot of way you can do it. First of all create a column in your table named InsertedAT then set the default value to GETDATE(). So when you insert data into this table then the column automatically populated with current date. No need to modify your source code.

The 2nd option is you can modify the select sql in the following way:
string sql = string.Format("Select [MSISDN],[Name],[Company],[Status],'August 10, 2009' AS [InsertedAT] FROM [{0}]", "Sheet1$");

Since you build the sql statement from front end(asp.net C#) so that you can add any static column stated above.

Anonymous said...

thanx alot

Anonymous said...

Great one! Question for you!
My excel sheet have a banner and one header row!
My original data starts from 5th row onwards. In this case how can I manipulate excel sheet which drives only data into SQL Table.
Thank you!

Saion Roy said...

HI Anonymous,
You can try with a free control from codeproject
...KB/database/CsvReader.aspx page.

Otherwise you have to go for VSTO.

Vinay said...

hi there,
your code is working fine.but instead of given path i.e. C:\Book1.xls, i want to pass full path selected by file upload button.but i am unable to do it..i am getting error because the path generated by file upload tool, shows like this(C:\\Desktop\\Book1.xls),this "\\" is not allowing your code to import data in database.Pls help in this issue.

Thanks,
Vinay

Saion Roy said...

HI Vinay,
"\\" is not a problem at all. You can easily import excel data using asp.net FileUpload control. Code example is given below:

string sFilePath = "";
sFilePath=FileUpload1.PostedFile.FileName;
string sSourceConstr ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + sFilePath + "; Extended Properties=" + "\"Excel 8.0;HDR=YES;+\"";

Hope now you can import data.

Anonymous said...

What will happen if there is date column with date like 20091130, how will this be corrected before it got into sql server database.

mensajes claro said...

Thanks Shawpnendu , Now those litle fixes will help it a lot for other.

Anonymous said...

Above code is good and working fine.
But, my requirement is different.
I want to upload a excel file witch has some 4-5 row from A1 to A5 then columns are started and then below the data of the columns

as follows :


ADITYA BIRLA GROUP
Employee Master Report for TMS
BUSINESS_UNIT: ABG Headquarters
SORT ORDER: Emplid

SrNo PoornataId salutation function business unit location jobband firstname lastname

1 7 Mr Commercial CEMENT Rajashree Cement Mumbai 7 F7 L7
2 8 Miss Projects Metals Novellis India cell Mumbai 8 F8 L8
3 9 Mrs Spinning Aditya Birla Mgmt Co. Ltd. ABMCL Mumbai 9 F9 L9


Please help me for this my email id is
pravin.patil51@yahoo.com

descargar juegos gratis said...

those litle fixes will help it a lot for other.

mensajes claro said...

Thanks Shawpnendu .

Arconte said...

with c# visual 2008 i have a pain in the ass in the connection string to oledb. This connection string works with me
string sSourceConstr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("../Campanas/uploads/Empleados.xls") + ";" + "Extended Properties=Excel 8.0;";

but now, i have a problem in the
OleDbDataReader dr = command.ExecuteReader()

missing parameteros in the executereader

Anonymous said...

this is a great example. so how would I write this if I want it look into multiple sheets and copy information in different tables after checking if the data has been imported before or not. If the data was imported before then do not execute and if it wasnt imported before then execute the import procedure.

Your help would be greatly appreciated.

Amit Sharma said...

Hi Sir Please help me i jst want to convert excel file in to XML then upload in sql.

Please help me.

Saion Roy said...

HI Amit,
You can read the below link. Hope it will help you.

http://www.codeproject.com/KB/office/excel2xml.aspx

Thanks.

Anonymous said...

This was helpful thanks

Anonymous said...

I go through all code but i m getting error on [Sheet1$] can you please explain what is Sheet1$ and how it is used

Anonymous said...

hi i got an error in
string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;

Saion Roy said...

You have to add your database connection string in web.config file as TestConnection.

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash