CODEWITHSUNDEEP
c#streamwritersqldatareader
user2823617
user2823617

Reputation: 13

SqlDataReader and StreamWriter formatting issue

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();
                }
            }

Upvotes: 1

Views: 906

Answers (2)

C.Evenhuis
C.Evenhuis

Reputation: 26446

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.

Upvotes: 1

Steve
Steve

Reputation: 216313

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());

Upvotes: 0

Related Questions