CODEWITHSUNDEEP
c#excelcsv
Cyberherbalist
Cyberherbalist

Reputation: 12319

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 value in the cell as text when I load it up?

Upvotes: 11

Views: 22976

Answers (6)

Mike S
Mike S

Reputation: 1371

I opened up a blank workbook, then went to the Data menu, then on the left side selected "From Text/CSV". I choose my CSV file, then in one of the top dropdowns "Data Type Detection", selected "Do not detect data types".

I had a column of serial numbers, each 24 digits long. It converted "8.929595956+E23" to the raw 24-digit number in front of my eyes.

Then I hit Load at the bottom.

The 24-digit serial number column looked perfect.

This is in Excel from Office 365 (Version 2008).

Upvotes: 0

Alexander
Alexander

Reputation: 11

This worked for me:

Change the line: 

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

for:

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

Upvotes: 1

Katherine Chalmers
Katherine Chalmers

Reputation: 414

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.

Upvotes: 0

gnomed
gnomed

Reputation: 5565

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.

Upvotes: 0

Richard Schneider
Richard Schneider

Reputation: 35477

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

}

Upvotes: 0

Greg
Greg

Reputation: 33650

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.

Upvotes: 14

Related Questions