SqlDataReader and StreamWriter formatting issue

Question

My code is not dealing with certain fields that contain two sentences. It writes the first sentence to the correct cell in the .csv file. However, the second sentence of the SQL field is written to a the first column two rows down...

private void SQLToCSV(string query, string Filename)
        {

            SqlConnection conn = new SqlConnection("redacted");
            conn.Open();
            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader dr = cmd.ExecuteReader();

            using (System.IO.StreamWriter fs = new System.IO.StreamWriter(Filename))
            {
                // Loop through the fields and add headers
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    string name = dr.GetName(i);
                    if (name.Contains(","))
                        name = "\"" + name + "\"";

                    fs.Write(name + ",");
                }
                fs.WriteLine();

                // Loop through the rows and output the data
                while (dr.Read())
                {
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        string value = dr[i].ToString();
                        if (value.Contains(","))
                            value = "\"" + value + "\"";

                        fs.Write(value + ",");
                    }
                    fs.WriteLine();
                }
            }

C.Evenhuis · Accepted Answer · 2013-09-27 13:55:02Z

1

CSV is a nasty format if you ask me. Comma's traditionally terminate columns (except in cultures where comma's are the decimal character, they sometimes use semicolons), newlines terminate rows. To ensure that comma or newline data is treated as data, you wrap them in a double-quote pair.

However that adds another special character to the list: what if your data contains double-quote values? You need to convert them to double double-quotes:

1,10,"This is my
multiline csv value, it ""may"" cause your parser to fail",8

Wikipedia mentions some programs do not follow the rules:

Fields with embedded line breaks must be quoted (however, many CSV implementations simply do not support this).

You may want to look at your data in notepad or something to verify that the problem is your code, not the program that uses it.

answered Sep 27, 2013 at 13:55

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve · Accepted Answer · 2013-09-27 13:55:00Z

0

Probably your sentences contain carriage returns and line feeds that are breaking your csv.
You could remove that NewLine chars while reading the input fields.
Also I suggest to use a string builder and write everything at the end of the loop

// Loop through the rows and output the data
StringBuilder sb = new StringBuilder();
while (dr.Read())
{
    for (int i = 0; i < dr.FieldCount; i++)
    {
        string value = dr[i].ToString();
        if (value.Contains(","))
            value = "\"" + value + "\"";

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

edited Sep 27, 2013 at 13:55

answered Sep 27, 2013 at 13:46

Steve

217k22 gold badges242 silver badges296 bronze badges

Collectives™ on Stack Overflow

SqlDataReader and StreamWriter formatting issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related