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

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 val开发者_开发技巧ue in the cell as text when I load it up?

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.

This worked for me:

Change the line:

    + "," + cm.BatNbr.Trim()

for:

    + ",\t" + cm.BatNbr.Trim()

Try quoting the BatNbr

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

}

Surrounding every value with quotes should solve your problem (EDIT: I see that this won't work now, but I will leave this answer here since the rest of the info might still be useful).

Technically there is no way to specify type in CSV, and this applies to both CSV creators and CSV readers. Some programs try to infer data types from the CSV but this is not good practice and not supported in any way by CSV.

You are allowed to surround every value in quotes, in fact I would recommend it even though it is optional.

Also important, if surrounding a value with quotes, make sure there is no leading whitespace between the comma and beginning quote. This messes up Microsoft Excel and it will render the cell as blank.

You could address the issue while you're importing the CSV into Excel. As you import and set the parsing for CSV files, you have the option of assigning the number format. I use this all the time for ZIP code columns in marketing lists to prevent losing the leading zeros in New England ZIPs.

Also, as Richard noted, you could add a keyword before the numbers then parse the column using the Text to Columns feature in Excel.