Import CSV file through a dataset (ODBC or OLEDB) to SQL Server


Through ODBC:

protected void Button1_Click(object sender, EventArgs e)

{

//ODBC to get DataSet from a CSV file for Source

DataSet ds = new DataSet(); 

string str = System.IO.Path.GetDirectoryName(MapPath(".\\App_Data\\countrylist.csv"));

string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + str + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";

OdbcConnection con = new OdbcConnection();

con.ConnectionString = strConnString;

OdbcCommand cmd = new OdbcCommand("SELECT [SortOrder],[CommonName] from countrylist.csv", con);

OdbcDataAdapter daSource = new OdbcDataAdapter(cmd);

daSource.AcceptChangesDuringFill = false;

daSource.Fill(ds,

"TransferData");

//A table with same schema in the SQL database

String qSelect = "SELECT [SortOrder], [CommonName] FROM countrylist";

String connectionString = ConfigurationManager.ConnectionStrings["test_for_forumConnectionString"].ConnectionString;

SqlConnection connection = new SqlConnection(connectionString);

SqlDataAdapter daDestination = new SqlDataAdapter();

SqlCommand command = new SqlCommand(qSelect, connection);

daDestination.SelectCommand = command;

// Create the InsertCommand.

String qInsert = "INSERT INTO countrylist ([SortOrder], [CommonName]) VALUES (@SortOrder, @CommonName)";

command =

new SqlCommand(qInsert, connection);

// Add the parameters for the InsertCommand.

command.Parameters.Add("@SortOrder", SqlDbType.NVarChar, 255, "SortOrder");

command.Parameters.Add(

"@CommonName", SqlDbType.NVarChar, 255, "CommonName");

daDestination.InsertCommand = command;

 

//daDestination.AcceptChangesDuringFill = true;

daDestination.Update(ds,

"TransferData");

 

GridView2.DataSource = ds;

GridView2.DataBind();

//dLabel1.Text = "Data Tansfered!";

 

//Refer links: http://www.knowdotnet.com/articles/datasetmerge.html

// http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.insertcommand(VS.80).aspx

//http://pranavgupta.wordpress.com/2008/06/13/get-csv-file-data-in-to-gridview/

}

 

Or through OLEDB

protected void Button1_Click(object sender, EventArgs e)

{

string str = System.IO.Path.GetDirectoryName(MapPath(".\\App_Data\\countrylist.csv"));

string strConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + str + "; Extended Properties=\"Text;HDR=No;\"";

OleDbConnection oCon = new OleDbConnection(strConString);

oCon.Open();

//F1, F2, F3 …. in the place of column names

string strSql = "SELECT F1 as [SortOrder], F2 as [CommonName] FROM countrylist.csv";

OleDbDataAdapter daSource = new OleDbDataAdapter(strSql, oCon);

DataSet ds = new DataSet();

daSource.AcceptChangesDuringFill =

false;

daSource.Fill(ds,

"TransferData");

//A table with same schema in the SQL database

String qSelect = "SELECT [SortOrder], [CommonName] FROM countrylist";

String connectionString = ConfigurationManager.ConnectionStrings["test_for_forumConnectionString"].ConnectionString;

SqlConnection connection = new SqlConnection(connectionString);

SqlDataAdapter daDestination = new SqlDataAdapter();

SqlCommand command = new SqlCommand(qSelect, connection);

daDestination.SelectCommand = command;

// Create the InsertCommand.

String qInsert = "INSERT INTO countrylist ([SortOrder], [CommonName]) VALUES (@SortOrder, @CommonName)";

command = new SqlCommand(qInsert, connection);

// Add the parameters for the InsertCommand.

command.Parameters.Add(

"@SortOrder", SqlDbType.NVarChar, 255, "SortOrder");

command.Parameters.Add(

"@CommonName", SqlDbType.NVarChar, 255, "CommonName");

daDestination.InsertCommand = command;

 

//daDestination.AcceptChangesDuringFill = true;

daDestination.Update(ds,

"TransferData");

 

GridView2.DataSource = ds;

GridView2.DataBind();

 

}

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s