CODEWITHSUNDEEP
c#.netcsvnewline
BearSkyview
BearSkyview

Reputation: 385

CSV Line Breaks

I'm building a csv file from database values. When I open the csv in excel everything is on one row. To counter this I've added either \n or \r after each row. Now when I open in excel starting at the second row there is an empty value throughout the rest of the first column.

IE:

value 1, value 2, value 3
       , value 1, value 2, value 3
       , value 1, value 2, value 3
       , value 1, value 2, value 3

I can add a newline or return before the first value, but then there is an empty row and column. How can I get this lined up?

My code:

try
{
    connection = new iDB2Connection(connectionString);
    connection.Open();
    command = new iDB2Command(commandString, connection);

    reader = command.ExecuteReader();
    var dt = new DataTable();
    dt.Load(reader);

    List<string> CsvList = new List<string>();

    foreach (DataRow row in dt.Rows)
    {
        foreach (var item in row.ItemArray)
        {
            CsvList.Add(item.ToString());
        }
        if (i == 1)
        {
            CsvList.Add(" ");
            CsvList.Add("Customer Service");
            CsvList.Add("Customer Service Representative" + "\r"); 
            // \r and \n not working
        }
    }

    System.IO.StreamWriter streamWriter;
    streamWriter = new System.IO.StreamWriter(fileName, true);

    string CsvString = string.Join(",", CsvList.ToArray());
    streamWriter.WriteLine(CsvString);
    streamWriter.Close();
    Console.WriteLine("File saved as " + fileName);

    i++;
}

Upvotes: 1

Views: 8291

Answers (2)

Servy
Servy

Reputation: 203802

It'll be a heck of a lot easier if you just build up a list of lines, rather than a flattened list of single values. Since you've flattened your list already when you use Join you're putting commas after newlines values, for example.

Instead join together the values for each line, and then just keep track of the entire lines:

foreach (DataRow row in dt.Rows)
{
    CsvList.Add(string.Join(",", row.ItemArray));
}

Now that you have a list of lines, we can just use File.AppendAllLines to simply and easily write out all of the lines to the end of the file:

File.AppendAllLines(fileName, CsvList);

Or, if you would like to stream the results to the file rather than copying all of the data over to a list and holding it all in memory at once, you can do this:

var lines = dt.AsEnumerable()
    .Select(row => string.Join(",", row.ItemArray));
File.AppendAllLines(fileName, lines);

Upvotes: 4

Chris Mantle
Chris Mantle

Reputation: 6683

Instead of creating an array and handling adding new-lines yourself, use a StringBuilder, and commit each row one at a time:

var sb = new StringBuilder();
foreach (DataRow row in dt.Rows)
{
    List<string> csvList = new List<string>();
    foreach (var item in row.ItemArray)
    {
        csvList.Add(item.ToString());
    }

    if (i == 1)
    {
        csvList.Add(" ");
        csvList.Add("Customer Service");
        csvList.Add("Customer Service Representative");
    }

    sb.WriteLine(string.Join(",", csvList));
}

System.IO.StreamWriter streamWriter;
streamWriter = new System.IO.StreamWriter(fileName, true);

streamWriter.WriteLine(sb.ToString());
streamWriter.Close();
Console.WriteLine("File saved as " + fileName);

Upvotes: 1

Related Questions