Q

How do I convert a SQL server database table into a text file and vice versa?

How do I convert a SQL server database table into a text file and vice versa? I know that BCP Utilities is there. Can you please give me some code?
It depends on the format of the text file ;)

To get an XML text file, simply load the dataset with the query (using a SqlDataAdapter probably) and use the following code:

DataSet ds;
//Load data from SQL
ds.WriteXml(@"c:\data.xml");

For a comma-separated text file, you will need to iterate the rows and dump them:

DataSet ds = new DataSet();
//Load data from SQL

using (StreamWriter sw = new StreamWriter(@"c:data.csv"))
{
 //First line contains column names
 foreach (DataColumn col in ds.Tables["titles"].Columns)
 {
  sw.Write(col.ColumnName);
  sw.Write(";");
 }
 sw.WriteLine();
 //Iterate rows
 foreach (DataRow row in ds.Tables["titles"].Rows)
 {
  //Dump each column value for the current row
  foreach (DataColumn col in ds.Tables["titles"].Columns)
  {
   sw.Write(row[col]);
   sw.Write(";");
  }
  sw.WriteLine();
 }
}

This was first published in October 2003

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close