Introduction
Here I will explain how to Import or insert data into SQL database from Excel spreadsheet using Sqlbulkcopy method.
Description
Copy data from excel to grid or sql db paths.
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
Posts
No comments:
Post a Comment
Hello all, if you have any doubt feel free comment