Introduction
Here I will explain how to Import or insert data into SQL database from Excel spreadsheet using Sqlbulkcopy method.
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 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
Design your aspx page like this
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<span style="color: Red">*</span>Attach Excel file
</td>
<td>
<asp:FileUpload ID="fileuploadExcel" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSend" runat="server" Text="Export" onclick="btnSend_Click" />
</td>
</tr>
</table>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
|
After that write the following code in codebehind button click
protected void btnSend_Click(object sender, EventArgs e)
{
String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";
//file upload path
string path = fileuploadExcel.PostedFile.FileName;
//Create connection string to Excel work book
string 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
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
}
|
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
Here don't forget to close the connection of your Excel file otherwise you will get error
No comments:
Post a Comment