Import Data from a Text or CSV file into SQL Server

Step 1) Prepare .csv file  or export one from  yahoo contacts/gmail contacts.
         
Step 2) Create a web application and add  .csv file to this project

Step3) Create a DB table

Step 4) Read .CSV file import data into  SQL-SERVER Table(step 3) Programatically.

Step1)  Contacts file name assuming yahoo.csv.
Step 2)   Add web page for importing yahoo.csv content into DB
               Before that Add a class Contacts.cs
   


using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;




namespace WebApplication3

{

public class Contacts

{

public string FirstName;

public string Lastname;

public string Middlename;

public String NickName;

public String email;

public override string ToString()

{

return String.Format("First Name={0},Last Name={1},Middle Name={2},NickName={3},email={4}",

FirstName,Lastname,Middlename,NickName,email);

}

}

}



Step3) Create a ASP.NET Web Application

Add following content to a Web Page




List<Contacts> contList = new List<Contacts>();

protected void Page_Load(object sender, EventArgs e)

{

using (TextReader reader = new StreamReader(MapPath("yahoo_ab.csv")))

{

int i = 0;

String line;

while ((line = reader.ReadLine()) != null)

{

if (i == 0) //ignore firstline

{



++i;

continue;

}

string[] array = line.Split(new char[] { ',' });

Contacts c = new Contacts();

c.FirstName = array[0];

c.Middlename = array[01];

c.Lastname = array[02];

c.Lastname = array[03];

c.email = array[04];

contList.Add(c);




Response.Write(c.ToString()+"<br/>");

}

}




importintoDBTable();

}




void importintoDBTable()

{

SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=test;Integrated Security=True;trusted_connection=yes;");//Windows Authentication

//Database name Test Shown in Step 4.




conn.Open();

foreach (Contacts c in contList)

{




String cmdText = "INSERT INTO Contacts(FirstName,LastName,MiddleName,NickName,Email) Values('" +

c.FirstName + "'," + "'" +

c.Lastname + "','" +

c.Middlename + "','" +

c.NickName + "','" +

c.email + "');";




SqlCommand cmd = new SqlCommand(cmdText);

cmd.Connection = conn;

cmd.ExecuteNonQuery();










}

}

Step 4)  Before Executing Web Application Create a DataBase Table Called "Contacts"

Create a Contacts table in Test Database

USE [test]

GO

/****** Object:  Table [dbo].[Contacts]    Script Date: 05/07/2012 10:18:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Contacts](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[FirstName] [nchar](50) NULL,

[LastName] [nchar](50) NULL,

[MiddleName] [nchar](50) NULL,

[NickName] [nchar](50) NULL,

[Email] [nchar](50) NULL,

 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Step 5)  Now Run the Web Application.  Which will Read CSV file and put into Database.