Some time we got stuck in some situation in which we have to import large amount of data into the database but it can be cumbersome task if it is entered from a web form. This type of problem can be solved by importing excel data into MS-SQL Sever database. Let me explain the thing one by one
The first step will be create a excel file named it “test.xls” and save the file at the root of the c drive. One thing that should be taken into consideration is that the first row of the excel file be used as reference , so in our example named it ID and Data, put some values into these fields , this data will be imported into the MS-SQL Server database.
The next step will be creating a database (textdb) with following field and field type:
1). ID Int
2). Data varchar(50)
Script for this table:
USE [test]
GO
/****** Object: Table [dbo].[excel] Script Date: 02/16/2009 23:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[excel](
[ID] [bigint] NOT NULL,
[Data] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Next we move on to the coding part, here we are following the logic, that first read the Excel sheet (having predefined fields) and then storing the read data into the MS-SQL Server. We have to refer two namespace, first is OLEDB and another is SQLClient
Coding part
Default.aspx
%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
Untitled Page
Default.aspx.cs
using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
#region Global Variables
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StrCon"].ToString());
#endregion
protected void Page_Load(object sender, EventArgs e)
{
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + TextBox1.Text + ";" +
"Extended Properties=Excel 8.0;";
// This SQL Query will extract data from the excel sheet.
// but here we have to consider one thing that column head should be macth with table heads
string select = "SELECT ID,DATA FROM [Sheet1$]";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbCommand cmd = new OleDbCommand(select, conn);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Label1.Text = dr["ID"].ToString();
// These codes will place data in SQL server Database
SqlCommand cmd1 = new SqlCommand("user_insert_data", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Clear();
cmd1.Parameters.Add("@ID", SqlDbType.BigInt).Value = Label1.Text.ToString();
cmd1.Parameters.Add("@Data", SqlDbType.VarChar, 50).Value = Label1.Text.ToString();
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
}
dr.Close();
conn.Close();
}
}
enjoy
About the author:
Planet Source Code is a place for all developer providing free source codes, articles, complete projects,complete application in PHP, C/C++, Javascript, Visual Basic, Cobol, Pascal, ASP/VBScript, AJAX, SQL, Perl, Python, Ruby, Mobile Development
- 36 views