Geek Answers Handbook

How to create a CSV so that a numerical value is displayed as a string in a spreadsheet?

I am trying to create an application that extracts some data from a database and then uses some data to create a CSV file to load Excel. The code:

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

"cm.BatNbr" is a 6-character zero padding such as "001234". I want Excel to format this column as text so that I don't lose the zeros in front. I tried some tricks, for example, a single-quote number prefix (apostrophe), but all I get is an apostrophe prefix. If I set the cells to be formatted as text, then remove the apostrophes, I will also lose zeros in front.

I accidentally discovered that if I prefix things with a percent sign, Excel converts the value in the cell to percent, so maybe there is a prefix that I can use to force Excel to accept the value in the cell as text when I load it?

+7
source share
5 answers

You can 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 can also try using the SYLK format instead of CSV. SYLK gives you better control over formatting.

+10
source

Try to specify 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); }
0
source

Enclosing each value with quotation marks should solve your problem (EDIT: I see that this will not work now, but I will leave this answer here, as the rest of the information may be useful).

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

You are allowed to surround each value in quotation marks; in fact, I would recommend it, even if it is not required.

It is also important that when matching the value with quotation marks, make sure that there is no leading space between the comma and the initial quote. This will ruin Microsoft Excel and it will make the cell empty.

0
source

You can solve this problem while importing CSV into Excel. When importing and setting up parsing for CSV files, you have the option to assign a number format. I use this all the time for zip columns in marketing lists to prevent the loss of leading zeros in New Zealand zip archives.

In addition, as Richard noted, you can add a keyword before numbers, then parse a column using the Text to Columns function in Excel.

0
source

This worked for me:

Change the line:

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

for

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

More articles:


All Articles