Code Master: How to convert Excel data to a table in SQL Server 2008

This article is about converting Excel data to a table. In this example I am using Ms SQL Server 2005 as the database. You can use any database by changing the connection string.

1. First of all we have an Excel file. In the Excel file the first row should contain the field names. If the Excel file contains blank rows in the top, then the field names will be Filed1, Field2,Field3â€¦and so on. For example the following shows the Excel data. Save this file and then use it.

Client Id	Client Name	Client Address	Amount
101	ABC Infotech	New York	100000
102	XYZ Systems	Manhattan	200000

2. Sometimes your column name may contain spaces. So in this example I am converting the blank space to underscore (_). Because in the database we cannot have a field name with a blank space.

3. First load the Excel file by giving a file dialog box.

Design Page:-

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

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

//Vlaidation For Excel file

function Checkfiles() {

var fup = document.getElementById('FUFile');

var fileName = fup.value;

var ext = fileName.substring(fileName.lastIndexOf('.') + 1);

if (ext == "xls" || ext == "xlsx") {

return true;

}

else {

alert("Upload Only Excel file");

fup.focus();

return false;

}

//Validation for non empty textbox

function Validate() {

var Sname = document.getElementById("<%=DDlSheet.ClientID %>");

if (Sname.value == 'Select') {

alert('Please Select Sheet');

Sname.focus();

return false;

}

var digits = "0123456789";

var Headerrow = document.getElementById("<%=TxtHeaderrow.ClientID %>");

if (Headerrow.value == "" || Headerrow.value == 0) {

alert('Enter Valid Header Row No.');

Headerrow.focus();

return false;

}

var temp;

for (var i = 0; i < Headerrow.value.length; i++) {

temp = Headerrow.value.substring(i, i + 1);

if (digits.indexOf(temp) == -1) {

alert("Header Row No Should be valid integer value");

Headerrow.value = "";

Headerrow.focus();

return false;

}

var Startrow = document.getElementById("<%=TxtStart.ClientID %>");

if (Startrow.value == "" || Startrow.value == 0) {

alert('Enter Valid Start Row No.');

Startrow.focus();

return false;

}

var temp;

for (var i = 0; i < Startrow.value.length; i++) {

temp = Startrow.value.substring(i, i + 1);

if (digits.indexOf(temp) == -1) {

alert("Start Row No Should be valid integer value");

Startrow.value = "";

Startrow.focus();

return false;

}

var Endrow = document.getElementById("<%=TxtEnd.ClientID %>");

if (Endrow.value == "" || Endrow.value == 0) {

alert('Enter Valid End Row No.');

Endrow.focus();

return false;

}

var temp;

for (var i = 0; i < Endrow.value.length; i++) {

temp = Endrow.value.substring(i, i + 1);

if (digits.indexOf(temp) == -1) {

alert("End Row No Should be valid integer value");

Endrow.value = "";

Endrow.focus();

return false;

}

//Validation for Valid Start ro no and end row no

if (parseInt(Startrow.value) <= parseInt(Headerrow.value)) {

alert('Start Row Should be greater than Header row');

Startrow.focus();

return false;

}

else if (parseInt(Startrow.value) > parseInt(Endrow.value)) {

alert('Start Row Should be less than or equal to End row');

Startrow.focus();

return false;

}

if (parseInt(Endrow.value) <= parseInt(Headerrow.value)) {

alert('End Row Should be greater than Header row');

Endrow.focus();

return false;

}

else if (parseInt(Endrow.value) < parseInt(Startrow.value)) {

alert('End Row Should be greater than Start row');

Endrow.focus();

return false;

}

</script>

</head>

<body>

<div>

                                                                                              <asp:Label ID="LblMessage" runat="server" Text="" Font-Bold="true"

style="font-family: Verdana; font-size: medium; color: #FF0000"></asp:Label>

<tr>

<td width="50%" colspan="2" align="center">Convert Excel Data to Sql Table</td>

</tr>

<tr>

<td width="25%">Select Excel File</td>

<asp:FileUpload ID="FUFile" runat="server" onChange="Checkfiles()"/><asp:Button ID="Button1" runat="server" Text="Display" OnClick="Button1_Click" /></td>

</tr>

<tr>

<td width="25%">Excel File Name</td>

<asp:TextBox ID="TxtFilename" runat="server" ReadOnly="true">

</asp:TextBox>

</td>

</tr>

<tr>

<td width="25%">Select Excel Sheet</td>

<asp:DropDownList ID="DDlSheet" runat="server" Width="210px">

</asp:DropDownList>

</td>

</tr>

<tr>

<td width="25%">Header Row No</td>

<asp:TextBox ID="TxtHeaderrow" runat="server" >

</asp:TextBox>

</td>

</tr>

<tr>

<td width="25%">Data Row Start Slno</td>

<asp:TextBox ID="TxtStart" runat="server">

</asp:TextBox>

</td>

</tr>

<tr>

<asp:TextBox ID="TxtEnd" runat="server">

</asp:TextBox>

</td>

</tr>

<tr>

<asp:Button ID="BtnShow" runat="server" Text="Show" onclick="BtnShow_Click" OnClientClick="return Validate()"/></td>

</tr>

<tr>

<td colspan="4" align="center">Data In Excel Sheet</td>

</tr>

<tr>

<asp:GridView ID="GridView1" runat="server" BackColor="White"

BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3"

ForeColor="Black" GridLines="Vertical" Width="425px">

</asp:GridView>

</td>

</tr>

<tr>

<td colspan="4" align="center">Data In Sql Database</td>

</tr>

<tr>

<asp:GridView ID="GrdSqldata" runat="server" BackColor="White"

BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3"

ForeColor="Black" GridLines="Vertical" Width="425px">

</asp:GridView>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

Note: Because you are using the Excel properties, first add the reference for Excel.

4. When you will click on the Browse button it will ask for filename. Select the file and then whatever the sheets in that workbook will be displayed in ComboBox. Select any sheet and click on save.

C# Code:-

using System;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data;

using System.Data.OleDb;

using System.Data.SqlClient;

using Microsoft.Office.Interop.Excel;

using Excel = Microsoft.Office.Interop.Excel;

using System.Configuration;

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

{

#region ObjectDeclaration

string excelfile = "",FileName="";

OleDbConnection oledbcon;

OleDbDataAdapter oledbda;

SqlDataAdapter da;

DataSet ds;

SqlConnection sqlcon;

SqlCommand sqlcom;

SqlTransaction trans;

#endregion

#region Event

protected void Page_Load(object sender, EventArgs e)

{

sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["CONN"].ConnectionString.ToString());

if (!IsPostBack)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

TxtFilename.Text = "";

DDlSheet.Items.Clear();

Excel.Application ExApp;

if (FUFile.HasFile)

{

FileName = Path.GetFileName(FUFile.PostedFile.FileName);

excelfile = Server.MapPath("UploadFile/" + FileName);

FUFile.SaveAs(Server.MapPath("UploadFile/" + FileName));

}

try

{

ExApp = new Application();

Workbook MyworkBook = ExApp.Workbooks.Open(excelfile, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, true, 0, true, false, false);

foreach (Worksheet wk in MyworkBook.Worksheets)

{

DDlSheet.Items.Add(wk.Name);

}

DDlSheet.Items.Insert(0, "Select");

TxtFilename.Text = FileName;

MyworkBook.Close(false, false, false);

}

catch (Exception ex)

{

LblMessage.Text = "Error:" + ex.ToString();

}

protected void BtnShow_Click(object sender, EventArgs e)

{

#region Variable

string fn = TxtFilename.Text;

string sn = DDlSheet.SelectedItem.ToString();

int hr = Convert.ToInt32(TxtHeaderrow.Text) - 1;

int sr = Convert.ToInt32(TxtStart.Text) - 1;

int er = Convert.ToInt32(TxtEnd.Text) - 1;

SqlConversion(fn, sn, hr, sr, er);

#endregion

}

#endregion

#region Function

protected string ExcelConnection(string filename)

{

string ex = System.IO.Path.GetExtension(filename);

string conn = "";

if (ex == ".xls")

{

conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=no;IMEX=1\"";

}

else if (ex == ".xlsx")

{

conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\"";

}

return conn;

}

protected System.Data.DataTable FetchFromSql()

{

da = new SqlDataAdapter("select * from " + DDlSheet.SelectedItem.ToString(), sqlcon);

ds = new DataSet();

da.Fill(ds);

return ds.Tables[0];

}

protected void SqlConversion(string FName,string SheetName,int HeaderRowNo,int StartRow,int EndRow)

{

string sqlquery = "";

try

{

#region RetrivefromExcelSheet

//Select Record From Excel Sheet

oledbcon = new OleDbConnection(ExcelConnection(Server.MapPath("~/UploadFile/" + FName)));

//oledbcon.Open();

oledbda = new OleDbDataAdapter("select * from [" + SheetName + "$] ", oledbcon);

ds = new DataSet();

oledbda.Fill(ds);

#endregion

if (EndRow <= ds.Tables[0].Rows.Count)

{

try

{

sqlcom = new SqlCommand();

sqlcon.Open();

trans = sqlcon.BeginTransaction();

sqlcom.Transaction = trans;

sqlcom.Connection = sqlcon;

int tt = 0, q = 0;

#region CreateTableInSql

//Create Table According to Excel Columns

//sqlcon.Open();

sqlquery = "create table " + SheetName + "(" + ds.Tables[0].Rows[HeaderRowNo][0].ToString().Replace(" ", "_") + " varchar(100)";

for (int c = 1; c < ds.Tables[0].Columns.Count; c++)

{

if (ds.Tables[0].Rows[HeaderRowNo][c].ToString() != "")

{

sqlquery += "," + ds.Tables[0].Rows[HeaderRowNo][c].ToString().Replace(" ", "_") + " varchar(100)";

}

else

{

ds.Tables[0].Columns.RemoveAt(c);

c--;

}

sqlquery += ")";

sqlcom.CommandText = sqlquery;

tt += sqlcom.ExecuteNonQuery();

sqlquery = "";

#endregion

#region InsertToSqlTable

//Insert Record To SqlTable

for (int r = StartRow; r <= EndRow; r++)

{

sqlquery = "insert into " + SheetName + " values(" + "'" + ds.Tables[0].Rows[r][0].ToString() + "'";

for (int cc = 1; cc < ds.Tables[0].Columns.Count; cc++)

{

sqlquery += ",'" + ds.Tables[0].Rows[r][cc].ToString() + "'";

}

sqlquery += ")";

sqlcom.Parameters.Clear();

sqlcom.CommandText = sqlquery;

q += sqlcom.ExecuteNonQuery();

sqlquery = "";

}

#endregion

if (q == (EndRow - StartRow) + 1)

{

trans.Commit();

trans.Dispose();

#region GridBind

LblMessage.Text = "Record Converted to Sql Sucessfully";

GridView1.DataSource = ds;

GridView1.DataBind();

GrdSqldata.DataSource = FetchFromSql();

GrdSqldata.DataBind();

#endregion

}

else

{

trans.Dispose();

LblMessage.Text = "Record Not Converted to Sql";

}

catch (Exception ex1)

{

LblMessage.Text = "Error:" + ex1.Message.ToString();

trans.Dispose();

}

else

{

ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('End Rows is higher than total rows in excelsheet');", true);

TxtEnd.Focus();

}

catch (Exception ex)

{

LblMessage.Text = "Error:" + sqlquery + "--" + ex.Message.ToString();

}

#endregion

}

Page View:-

Code Master

Thursday 12 January 2012

How to convert Excel data to a table in SQL Server 2008

No comments:

Post a Comment