How do I create a CSV so that a numeric appears as a string in the spreadsheet?

Question

I am trying to build an application that extracts some data from a database, and then uses some of the data to create a CSV file to be loaded up by Excel. The codez:

foreach (xOFDocInfo cm in docs)
{
    string s = bi.Agency
        + "," + cm.BatNbr.Trim()
        + "," + cm.RefNbr
        + "," + cm.DocType
        + "," + cm.OrigDocAmt.ToString()
        + "," + cm.CreateDate.ToShortDateString();

    writer.WriteLine(s);

}

The "cm.BatNbr" is a 6 character zero-filled numeric such as "001234". I want Excel to format that column as text so I don't lose the zeroes up front. I've tried some tricks, such as prefixing the number with a single-quote (apostrophe), but all I get is an apostrophe prefix. If I set the cells to be formatted as text then remove the apostrophes, I also lose the zeroes at the front.

I accidentally found that if I prefix the thing with a percent sign, Excel converts the value in the cell to a percentage, so perhaps there is some prefix I can use to cause Excel to take the value in the cell as text when I load it up?

Answer 1

You could format the data as ="001234". This will cause Excel to display it as 001234.

foreach (xOFDocInfo cm in docs)
{
    string s = bi.Agency
        + ",=\"" + cm.BatNbr.Trim()
        + "\"," + cm.RefNbr
        + "," + cm.DocType
        + "," + cm.OrigDocAmt.ToString()
        + "," + cm.CreateDate.ToShortDateString();

    writer.WriteLine(s);

}

You could also try using the SYLK format instead of CSV. SYLK gives you better control over formatting.