c# - Format number as text in CSV when open in both Excel and Notepad

Question

I received a requirement to save data in CSV file and send it to customers. Customers use both Excel and Notepad to view this file. Data look like:

975567EB, 973456CE, 971343C8

And my data have some number end by "E3" like:

98765E3

so when open in Excel, it will change to:

9.8765E+7

I write a program to change this format to text by adding ="98765E3" to this in C#

while(!sr.EndOfStream) {
     var line = sr.ReadLine();
     var values = line.Split(',');

     values[0] = "=" + """ + values[0] + """; //Change number format to string

     listA.Add(new string[] {values[0], values[1], values[2], values[3]});
}

But with customer, who use Notepad to open CSV file, it will show like:

="98765E3"

How could I save number as text in CSV to open in both Excel and Notepad with the same result? Greatly appreciate any suggestion!

Answer 1

Don't Shoot the messenger.

Your problem is not the way you are exporting (creating...?) data in C#. It is with the way that you are opening the CSV files in Excel.

Excel has numerous options for importing text files that allow for the use of a FieldInfo parameter that specifies the TextFileColumnDataTypes property for each field (aka column) of data being brought in.

If you chose to double-click a CSV file from an Explorer folder window then you will have to put up with what Excel 'best-guesses' are your intended field types for each column. It's not going to stop halfway through an import process to ask your opinion. Some common errors include:

• An alphanumeric value with an E will often be interpreted as scientific notation.
• Half of the DMY dates will be misinterpreted as the wrong MDY dates (or vise-versa). The other half will become text since Excel cannot process something like 14/08/2015 as MDY.
• Any value that starts with a + will produce a #NAME! error because Excel thinks you are attempting to bring in a formula with a named quality.

That's a short list of common errors. There are others. Here are some common solutions.

• Use Data ? Get External Data ? From Text. Explicitly specify any ambiguous column data type; e.g. 98765E3 as Text, dates as either DMY, MDY, YMD, etc as the case may be. There is even the option to discard a column of useless data.
• Use File ? Open ? Text Files which brings you through the same import wizard as the option above. These actions can be recorded for repeated use using either command.
• Use VBA's Workbooks.OpenText method and specify each column's FieldInfo position and data type (the latter with a XlColumnDataType constant).
• Read the import file into memory and process it in a memory array before dumping it into the target worksheet.

There are less precise solutions that are still subject to some interpretation from Excel.

• Use a Range.PrefixCharacter to force numbers with leading zeroes or alphnumeric values that could conceivably be misinterpreted as scientific notation into the worksheet as text.
• Use a text qualifier character; typically ASCII character 034 (e.g. ") to wrap values you want to be interpreted as text.
• Copy and paste the entire text file into the target worksheet's column A then use the Range.TextToColumns method (again with FieldInfo options available for each column).

These latter two methods are going to cause some odd values in Notepad but Notepad isn't Excel and cannot process a half-million calculations and other operations in several seconds. If you must mash-up the two programs there will be some compromises.

My suggestion is to leave the values as best as they can be in Notepad and use the facilities and processes readily available in Excel to import the data properly.