Column value is converted to formula/exponent

Question

I am having a bit of trouble with a particular column of data when writing a CSV file. The majority of the time, the value is longer than 11 characters/digits. So it's being generated as 7.47313E+11 in the CSV, when it should really read as 747313016112. I was initially using this answered question as a reference in an attempt to resolve my issue, but it's not working.

One thing to note is that the column in the DB (titled Barcode) is a varchar. However, only number values are ever saved to this column. So I guess at some point during the CSV generation process (or after the generation), Excel is recognizing it as a number, not a string.

Here's my working code thus far:

public static void GenerateChartsCSVFile(IEnumerable<ChartsData> chartsData)
{
    string fullPath = "C:\\chart-data.csv";

    using (var writer = new StreamWriter(fullPath, true, Encoding.GetEncoding("iso-8859-1")))
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        csv.WriteRecords(chartsData);

        writer.Flush();
    }

    if (File.Exists(fullPath))
        Console.WriteLine("Charts CSV file " + csvFile + " successfully created!");
    else
        Console.WriteLine("Oops, something went wrong with generating the Charts CSV file.");
}

Data Model:

public class ChartsData
{
    public string CatalogueID { get; set; }
    public string Barcode { get; set; }
    public string Title { get; set; }
    public string ReleaseDate { get; set; }
    public int DiscNo { get; set; }
    public int TrackNo { get; set; }
    public string TrackTitle { get; set; }
    public string ISRC { get; set; }
    public string Duration { get; set; }
    public string LabelID { get; set; }
    public string Label { get; set; }
    public string TrackContributors { get; set; }
}

Thank you very much in advance!

Answer 1

Just add one set of double quotes. CsvHelper automatically escapes quotes in the string with another set of double quotes. The string =""747313016112"" will end up as =""""747313016112"""" because CsvHelper will escape each of the double quotes. That is why your value looks like "747313016112" inside Excel.

chartsData.Barcode = "=\"" + chartsData.Barcode + "\"";

CsvHelper is following RFC 4180

7. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.

For example:

"aaa","b""bb","ccc"