DataReader to .CSV with column names

Question

I'm generating a csv file from an SqlDataReader, however it is not writing the column names, how can I make it write them? The code I'm using is as follows:

SqlConnection conn = new SqlConnection(myconn);
SqlCommand cmd = new SqlCommand("dbo.test", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();

StringBuilder sb = new StringBuilder();
StreamWriter sw = new StreamWriter(myfilePath + "testfile.csv"); 
while (reader.Read())
{
    for (int i = 0; i < reader.FieldCount; i++)
    {
        string value = reader[i].ToString();
        if (value.Contains(","))
            value = "\"" + value + "\"";

        sb.Append(value.Replace(Environment.NewLine, " ") + ",");
    }
    sb.Length--; // Remove the last comma
    sb.AppendLine();
}
conn.Close();
sw.Write(sb.ToString());
sw.Close();

Possible duplicate of http://stackoverflow.com/questions/681653/can-you-get-the-column-names-from-a-sqldatareader — Sybren, Apr 10 '15 at 12:31

Satpal · Accepted Answer · 2015-04-10T12:50:57.153

14

Read all the column names and append it to sb then iterate reader.

SqlDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();

//Get All column 
var columnNames = Enumerable.Range(0, reader.FieldCount)
                        .Select(reader.GetName) //OR .Select("\""+  reader.GetName"\"") 
                        .ToList();

//Create headers
sb.Append(string.Join(",", columnNames));

//Append Line
sb.AppendLine();

while (reader.Read())
....

edited Apr 10 '15 at 12:50

answered Apr 10 '15 at 12:31

Satpal

132,252
13
159
168

I think this might have some problems if you have a comma in the field name. – Paul Oct 28 '20 at 04:39

score 5 · Answer 2 · edited May 23 '17 at 12:02

Using this solution i created an extension.

/// <summary>
/// 
/// </summary>
/// <param name="reader"></param>
/// <param name="filename"></param>
/// <param name="path">if null/empty will use IO.Path.GetTempPath()</param>
/// <param name="extension">will use csv by default</param>
public static void ToCsv(this IDataReader reader, string filename, string path = null, string extension = "csv")
{
    int nextResult = 0;
    do
    {
        var filePath = Path.Combine(string.IsNullOrEmpty(path) ? Path.GetTempPath() : path, string.Format("{0}.{1}", filename, extension));
        using (StreamWriter writer = new StreamWriter(filePath))
        {
            writer.WriteLine(string.Join(",", Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList()));
            int count = 0;
            while (reader.Read())
            {
                writer.WriteLine(string.Join(",", Enumerable.Range(0, reader.FieldCount).Select(reader.GetValue).ToList()));
                if (++count % 100 == 0)
                {
                    writer.Flush();
                }
            }
        }

        filename = string.Format("{0}-{1}", filename, ++nextResult);
    }
    while (reader.NextResult());
}

Hi, I've read your function and it's very helpful to create the csv. However it doesn't account for lines which contain " or , . They will break validation on the CSV. — gabtzi, Sep 16 '19 at 11:22

score 4 · Answer 3 · answered Apr 10 '15 at 12:38

You can use SqlDataReader.GetName to get the column name

for (int i = 0; i < reader.FieldCount; i++)
{
    string columnName = reader.GetName(i);
}

Also you can create an extension method like below:

public static List<string> ToCSV(this IDataReader dataReader, bool includeHeaderAsFirstRow, string separator)
{
    List<string> csvRows = new List<string>();
    StringBuilder sb = null;

    if (includeHeaderAsFirstRow)
    {
        sb = new StringBuilder();
        for (int index = 0; index < dataReader.FieldCount; index++)
        {
            if (dataReader.GetName(index) != null)
                sb.Append(dataReader.GetName(index));

            if (index < dataReader.FieldCount - 1)
                sb.Append(separator);
        }
        csvRows.Add(sb.ToString());
    }

    while (dataReader.Read())
    {
        sb = new StringBuilder();
        for (int index = 0; index < dataReader.FieldCount - 1; index++)
        {
            if (!dataReader.IsDBNull(index))
            {
                string value = dataReader.GetValue(index).ToString();
                if (dataReader.GetFieldType(index) == typeof(String))
                {
                    //If double quotes are used in value, ensure each are replaced but 2.
                    if (value.IndexOf("\"") >= 0)
                        value = value.Replace("\"", "\"\"");

                    //If separtor are is in value, ensure it is put in double quotes.
                    if (value.IndexOf(separator) >= 0)
                        value = "\"" + value + "\"";
                }
                sb.Append(value);
            }

            if (index < dataReader.FieldCount - 1)
                sb.Append(separator);
        }

        if (!dataReader.IsDBNull(dataReader.FieldCount - 1))
            sb.Append(dataReader.GetValue(dataReader.FieldCount - 1).ToString().Replace(separator, " "));

        csvRows.Add(sb.ToString());
    }
    dataReader.Close();
    sb = null;
    return csvRows;
}

Example:

List<string> rows = null;
using (SqlDataReader dataReader = command.ExecuteReader())
    {
        rows = dataReader.ToCSV(includeHeadersAsFirstRow, separator);
        dataReader.Close();
    }

I think I like your implementation and will use with some modifications (say, return one string instead of list of strings and also implement similar extension for creating a file directly). How well this code is tested by you in production? — Naomi, Feb 06 '19 at 13:10

score 0 · Answer 4 · answered Apr 10 '15 at 12:31

0

You can use the SqlDataReader.GetName method to get the name of a column, like this:

for(int i = 0; i < reader.FieldCount; i++)
{
    string columnName = reader.GetName(i);
}

answered Apr 10 '15 at 12:31

mainvoid

347
1
4
17

score 0 · Answer 5 · answered May 17 '17 at 05:50

I developed following high performance extension

static void Main(string[] args)
        {
            SqlConnection sqlCon = new SqlConnection("Removed");
            sqlCon.Open();
            SqlCommand sqlCmd = new SqlCommand("Select * from Table", sqlCon);
            SqlDataReader reader = sqlCmd.ExecuteReader();
            string csv=reader.ToCSVHighPerformance(true);
            File.WriteAllText("Test.CSV", csv);
            reader.Close();
            sqlCon.Close();
        }

Extention:

public static string ToCSVHighPerformance(this IDataReader dataReader, bool includeHeaderAsFirstRow = true,
            string separator = ",")
        {
            DataTable dataTable = new DataTable();
            StringBuilder csvRows = new StringBuilder();
            string row = "";
            int columns ;
            try
            {
                dataTable.Load(dataReader);
                columns= dataTable.Columns.Count;
                //Create Header
                if (includeHeaderAsFirstRow)
                {
                    for (int index = 0; index < columns; index++)
                    {
                        row += (dataTable.Columns[index]);
                        if (index < columns - 1)
                            row += (separator);
                    }
                    row += (Environment.NewLine);
                }
                csvRows.Append(row);

                //Create Rows
                for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
                {
                    row = "";
                    //Row
                    for (int index = 0; index < columns - 1; index++)
                    {
                        string value = dataTable.Rows[rowIndex][index].ToString();

                        //If type of field is string
                        if (dataTable.Rows[rowIndex][index] is string)
                        {
                            //If double quotes are used in value, ensure each are replaced by double quotes.
                            if (value.IndexOf("\"") >= 0)
                                value = value.Replace("\"", "\"\"");

                            //If separtor are is in value, ensure it is put in double quotes.
                            if (value.IndexOf(separator) >= 0)
                                value = "\"" + value + "\"";

                            //If string contain new line character
                            while (value.Contains("\r"))
                            {
                                value = value.Replace("\r", "");
                            }
                            while (value.Contains("\n"))
                            {
                                value = value.Replace("\n", "");
                            }
                        }
                        row += value;
                        if (index < columns - 1)
                            row += separator;
                    }
                    dataTable.Rows[rowIndex][columns - 1].ToString().ToString().Replace(separator, " ");
                    row += Environment.NewLine;
                    csvRows.Append(row);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return csvRows.ToString();
        }

You are missing the last column when you iterate through them. Should be `for (int index = 0; index < columns; index++)` instead of `for (int index = 0; index < columns - 1; index++)` — Peter Holm, Feb 16 '18 at 07:15
Sorry, but you should not call this as high performance solution because of use datatable.Load(IDatareader) this removes main benefit of DataReader - _The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory._ [MS Docs](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader). This solution will be pretty slow on huge result sets — Baximilian, May 15 '18 at 19:06
When your database server and iis server is not same, network propagation time effect your performance. without datatable.Load, per row you get just one row from database server (for 1000 row, you have 1000* network propagation time), but by datatable.Load(IDatareader), for every read request (number of read request handle by .load function and depend on row size and network package size) you get many row and this issue reduce network propagation time. — Nigje, May 23 '18 at 05:54
@Nigje, I'm sorry but @Baximilian is right here. And what you're comparing doesn't make sense. There's also no guarantee that `datatable.Load` doesn't get data Row by Row either, if we were to trust your premise. I think you're not using DataTable for what it's meant to be used for: streaming large amounts of data and processing it in discreet packets as it comes in, instead of putting huge loads on the server, client and network and loading ALL data into memory, then processing it. That can have a number of bad side-effects, like resource contention, lock ups, low concurrency, OOM, etc. — Paul-Sebastian Manole, Oct 11 '20 at 18:02
DateTimes are a bit of a problem because depending on your locale, Excel (and other downstream apps) may get confused between dd MM yyyy and MM dd yyyy. It is best to include a datetime type check and explicitly format it to value = dt.Value.ToString("dd MMMM yyyy HH:mm:ss"); or similar. Also @PeterHolm made a great comment about the 'columns' for loop being short by 1. Otherwise we are using this code in production and it works great for us! thanks. — DJA, Sep 12 '22 at 23:35
This `high performance` may be faster but will consume the entire IDataReader records into memory by loading it into a DataTable. Memory consumption will also be doubled by the string builder. `out-of-memory-exception` might occur with this technique. You may consider outputting a IEnumerable to reduce this. — Jonathan Larouche, Nov 17 '22 at 15:37

DataReader to .CSV with column names

5 Answers5

Linked