Dotnet Codes: Import Excel Sheet data in SQL server

Upload Excel file data to SQL server using SqlBulkCopy. A new feature in ADO.NET 2.0 . In this example we will import the Excel Sheet data in to SQL server.
Following are the connection strings for different version of Excel sheet.

FOR xls files: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FilePath];Extended Properties=”Excel 8.0;HDR=YES;IMEX=1

FOR xlsx files: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FilePath];Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1

Where HDR=YES specifies first row of column is header column and
IMEX=1 specifies that the driver should always read the “intermixed”
data columns as text

By SqlBulkCopy you can upload complete excel sheet data in a single instance, quick and easy.

Files
Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Excel File Uploading Demo</title>

<style type="text/css">

.style1

{

font-family: Verdana, Arial, Helvetica, sans-serif;

font-size: 12px;

}

</style>

</head>

<body>

<form id="form1" runat="server">

<div>

<table width="52%" border="0" cellpadding="2" cellspacing="2" class="style1" style="border: #666666 1px solid">

<tr>

<td width="24%" nowrap="nowrap">

<strong>Excel Upload Example</strong>

</td>

<td width="76%" nowrap="nowrap">

&nbsp;

</td>

</tr>

<tr>

<td nowrap="nowrap">

<strong>Select your file </strong>

</td>

<td nowrap="nowrap">

&nbsp;

<asp:FileUpload ID="FileUpload1" runat="server" CssClass="style1" />

&nbsp;<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="FileUpload1"

CssClass="style1" Display="Dynamic" ErrorMessage="Please select your file."></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td nowrap="nowrap">

&nbsp;

</td>

<td nowrap="nowrap">

&nbsp;

<asp:Button ID="Button1" runat="server" CssClass="style1" OnClick="Button1_Click"

Text="Upload Data" />

<br />

</td>

</tr>

<tr>

<td nowrap="nowrap">

&nbsp;

</td>

<td nowrap="nowrap">

&nbsp;

<asp:Label ID="lblMsg" runat="server" Font-Bold="True"></asp:Label>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

Default.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data.OleDb;

using System.Data.Common;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

string excelConnectionString = string.Empty;

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

UploadExclData();

}

#region UPLOAD DATA  TO SQL SERVER

private void UploadExclData()

{

try

{

 //First we will upload the file to our hard drive

//Now check if control has file

if (FileUpload1.HasFile)

{

 ///execlData folder must have read and write permission

string excelFile = FileUploadToServer.UploadFile(FileUpload1, Server.MapPath("~/execlData/"));

 //Now Create the Connection to excel File

////CONNECTION STRING FOR DIFFRENT EXCEL VERSIONS

 //FOR xls Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FilePath];Extended Properties=”Excel 8.0;HDR=YES;IMEX=1”

//FOR xlsx Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FilePath];Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1

 //HDR=Yes specifies that the first row of the data contains column names and not data.

//If you don’t want the first row to be skipped, change this to No.

//IMEX=1  specifies that the driver should always read the “intermixed” data columns as text.

 //Create connection string to Excel work book

excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/execlData/" + excelFile) + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

OleDbConnection exclConnection = new OleDbConnection(excelConnectionString);

 //NOW FETCH DATA FROM EXCEL SHEET USING OLEDB 

OleDbCommand exclCmd = new OleDbCommand("select * from [Sheet1$]", exclConnection);

exclConnection.Open();

OleDbDataReader exclReader = exclCmd.ExecuteReader();

 //NOW CONNECT THE DESTINATION DATABASE CONNECTION AND TABLE NAME

SqlBulkCopy sqlBulk = new SqlBulkCopy(SqlHelper.mainConnectionString);

sqlBulk.DestinationTableName = "empData";

 //NOW MAP THE DESTINATION AND SOURCE TABLE COLUMN MAPPING

sqlBulk.ColumnMappings.Add("company_name", "company_name");

sqlBulk.ColumnMappings.Add("Employee_Name", "Employee_Name");

sqlBulk.ColumnMappings.Add("Emp_Code", "Emp_Code");

sqlBulk.ColumnMappings.Add("Date_Of_Joining", "Date_Of_Joining");

 //WRITE THE DATA TO TABLE

sqlBulk.WriteToServer(exclReader);

lblMsg.Text = "Your data uploaded successfuly.";

}

}

catch (Exception ex)

{

throw (ex);

}

}

#endregion

}

Download Code Files

6 comments:

UnknownDecember 21, 2010 at 3:37 PM
Hi,

I have an doubt in your coding,,as i got an error kindly reply me to my mail id:sathya4260@gmail.com

The error is:Cannot read the file as it is opened, but the file is not opened, i think wen selecting the file from file upload i got the preview becoz of that it may happen kindly help me on this as soon as possible
VipinDecember 22, 2010 at 11:57 PM
Yes you can not open your file, while uploading. Try again.
UnknownFebruary 25, 2011 at 1:41 AM
great work
thank you so much
RajuAugust 17, 2011 at 6:47 PM
All these are ok, But I want to Import data with Validation in Excel Cell By Cell. So can u tell me how to validate...

Thanks & Regards,
Rajesh
VipinAugust 23, 2011 at 11:35 PM
Hi
That you can do before uploading the data you can validate all and you can use your business logic there.
YoginetFebruary 3, 2014 at 3:22 PM
thanks for post. I am working as senior web developers in WDI services India offering cheap web design & development services for past 11 years but your knowledge strength in Dot Net is amazing

Saturday, September 25, 2010

Import Excel Sheet data in SQL server

6 comments: