String not appearing as text in CSV while exporting from C#

Question

I have a set codes, 0075, 0062 etc saved as a string in my database and declared as string in my model. However, when I am exporting my details in CSV using CSV Helper, the codes are not saved as text but as number. That is, 0075 saved as 75. I have tried adding "=" in front of the string, but this does not work. Or tried it as below. But in vain. Below is my code:

streamWriter.WriteLine("Code;");
streamWriter.WriteLine(string.Join(";", "\""+result.Code+"\""));

Any idea how to saved result.Code which is declared as a string, as a text in my CSV?

Code as declared in the model:

public string Code { get; set; }

Answer 1

Looks like the method suggested by Formatting a comma-delimited CSV to force Excel to interpret value as a string works (on Excel 2010 at least), which is to format each cell as

"=""String Value"""

Here's a static helper class that does the necessary work. Since you used ; for a delimiter I reckon you are in a region where , is the decimal separator; to generalize my answer I'm using System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator as the list separator.

public static class CsvWriter
{
    public static void WriteToCsv(IEnumerable<string> cells, TextWriter writer, CultureInfo cultureInfo = null)
    {
        if (cells == null || writer == null)
            throw new ArgumentNullException();
        string listSeparator = (cultureInfo ?? System.Globalization.CultureInfo.CurrentCulture).TextInfo.ListSeparator;
        bool first = true;
        foreach (var cell in cells)
        {
            if (!first)
                writer.Write(listSeparator);
            writer.Write(ToCsvCell(cell));
            first = false;
        }
        writer.Write("\r\n");
    }

    public static void WriteToCsv<TEnumerable>(IEnumerable<TEnumerable> lines, TextWriter writer, CultureInfo cultureInfo = null) where TEnumerable : IEnumerable<string>
    {
        if (lines == null || writer == null)
            throw new ArgumentNullException();
        cultureInfo = cultureInfo ?? System.Globalization.CultureInfo.CurrentCulture;
        foreach (var cells in lines)
            WriteToCsv(cells, writer, cultureInfo);
    }

    public static string ToCsv<TEnumerable>(IEnumerable<TEnumerable> lines, CultureInfo cultureInfo = null) where TEnumerable : IEnumerable<string>
    {
        using (var writer = new StringWriter())
        {
            WriteToCsv(lines, writer, cultureInfo);
            return writer.ToString();
        }
    }

    static string ToCsvCell(string s)
    {
        if (s == null)
            return "";
        s = s.Replace("\"", "\"\"\"\"");
        return string.Format("\"=\"\"{0}\"\"\"", s);
    }
}

Then, to test:

        var lines = new[] 
        {
            new [] { "0075", "0062", "abc", DateTime.Today.ToShortDateString() },
            new [] { "I said \"this is a quote\"" },
            new [] { "Embedded new line: \r\nSecond Line",  string.Concat(Enumerable.Repeat(System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator, 5).ToArray()) },
        };
        var path = Path.Combine(Path.GetTempPath(), "TestQuestion34034950.csv");
        using (var writer = new StreamWriter(path))
        {
            CsvWriter.WriteToCsv(lines, writer);
        }
        Console.WriteLine("Wrote " + path);

Excel will interpret all the CSV cells created by the above as string literals.