Best way to save DataSet to a CSV file

Question

I have some SQL queries that I need to write out the results to a CSV file.

I'm currently storing the results in a DataSet.

What is the best way to to save each table (using a user-defined filename) as its own CSV file?

I'm using C#.

Solution 1

You can convert a dataset to a CSV file as follows:

Code snippet:

StringBuilder str = new StringBuilder();
foreach (DataRow dr in this.NorthwindDataSet.Customers) 
{
 foreach (object field in dr.ItemArray) 
 {
   str.Append(field.ToString + ",");
 }
 str.Replace(",", vbNewLine, str.Length - 1, 1);
}

try 
{
 My.Computer.FileSystem.WriteAllText("C:\\temp\\testcsv.csv", str.ToString, false);
} 
catch (Exception ex) 
{
 MessageBox.Show("Write Error");
}

Hope this helps! Cheers!

Solution 2

You can loop through the DataSet (Tables, Rows, Fields)

C# Example:

        StringBuilder str = new StringBuilder();
        foreach (DataTable dt in tempDataDS.Tables)
        {
            foreach (DataRow item in dt.Rows)
            {
                foreach (object field in item.ItemArray)
                {
                    str.Append(field.ToString() + ",");
                }
                str.Replace(",", Environment.NewLine, str.Length - 1, 1);
            }
        }
        try
        {
            File.AppendAllText("C:\\temp\\tempData.csv", str.ToString(), Encoding.UTF8);
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error while writing content to csv file. \nException details: {0}", ex.ToString());
        }

Solution 3

I use http://www.filehelpers.com anytime I need to export to CSV.

Comments

[MethodMan]

will that help him out as well if he's using sql server 2008 R2 as for example..?

[Joachim Isaksson]

Worked on 2008 (non-R2) but it's dodgy with characters that need escaping in CSVs. Updated the answer to reflect that.