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
usingsytem.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
private void PopulateDatabaseTables()
{ string tableName = string.Empty; string sql = "SELECT *, name AS table_name " + " FROM sys.tables WHERE Type = 'U' ORDER BY table_name"; using (SqlConnection conn = new SqlConnection(connStr))
{ using (DataTable table = new DataTable())
{
conn.Open(); using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn))
{
dAd.Fill(table);
}
ListBox1.DataSource = table;
ListBox1.DataBind();
}
}
}
protected void ImportNow_Click(object sender, EventArgs e)
{ if (ListBox1.SelectedValue == "")
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please select table in which you want to import data from ecel sheet";
} else if ((fileuploadExcel.FileName != ""))
{ string extension = Path.GetExtension(fileuploadExcel.PostedFile.FileName); string excelConnectionString; SqlConnection conn = new SqlConnection(connStr); string tableName = ListBox1.SelectedValue; string path = fileuploadExcel.PostedFile.FileName;
//Create connection string to Excel work book if (extension == ".xls")
{
excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;Persist Security Info=False";
} else
{
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
conn.Open(); SqlCommand comm = new SqlCommand("truncate table " + tableName, conn);
SqlCommand identityChange = conn.CreateCommand();
identityChange.CommandText = "SET IDENTITY_INSERT " + tableName + " ON"; OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
excelConnection.Open(); OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
identityChange.ExecuteNonQuery(); SqlBulkCopy sqlBulk = new SqlBulkCopy(connStr); //Give your Destination table name
sqlBulk.DestinationTableName = tableName;
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
conn.Close();
lblMessage.ForeColor = Color.Green;
lblMessage.Text = "Import into table <b>" + tableName + "</b> successful!<br />";
} else
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please first upload (Select) excel file.";
}
}
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.
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 problemyou 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.....!
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."
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.
Give complete code for this
ReplyDeleteAnil Babu....I already give the complete code....It is well tested and executing properly....
DeleteJust click on show/Hide button in the above post.....
This is really useful thank you............
ReplyDeletei m finding error then select excel file and clink inport button
ReplyDelete"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."
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.
ReplyDeletei 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.
its showing error"the table is not in the expected format"
ReplyDeletethe 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