在Excel和记事本中打开时，格式化为CSV文本 [英] Format number as text in CSV when open in both Excel and Notepad

问题描述

我收到了将数据保存在CSV文件中并将其发送给客户的要求。
客户同时使用Excel和记事本来查看此文件。
数据看起来像：

975567EB，973456CE，971343C8

我的数据有一些数字以E3结尾，如：

98765E3

所以当在Excel中打开时，它将更改为：

9.8765E + 7

我写了一个程序，通过在C＃中添加=98765E3来将此格式更改为文本>

  while（！sr.EndOfStream）{
 var line = sr.ReadLine（）; 
 var values = line.Split（'，'）; 
 
值[0] ==+\+值[0] +\; //将数字格式更改为字符串
 
 listA.Add（new string [] {values [0]，values [1]，values [2]，values [3]}）; 
} 
  

但是，与客户一起使用记事本打开CSV文件，它会显示为：

=98765E3

如何将数字保存为CSV文本以在Excel和记事本中打开相同的结果？非常感谢任何建议！

解决方案

不要拍摄信使。 / p>

您的问题不是您在C＃中导出（创建...？）数据的方式。这是您打开Excel中的CSV文件的方式。

Excel具有多种导入文本文件的选项，允许使用FieldInfo 参数，该参数指定 TextFileColumnDataTypes属性每个字段（aka列）的数据被带入。

如果您选择从资源管理器文件夹窗口双击CSV文件，那么您将必须忍受Excel的最佳猜测是每列的预期字段类型。通过进口过程中途不要停止询问您的意见。一些常见错误包括：

• 带有E的字母数字值通常被解释为科学符号。


• DMY日期的一半将被误解为错误的MDY日期（或反之亦然）。另一半将成为文本，因为Excel无法像MDY那样处理像14/08/2015之类的东西。


• 以+开头的任何值都会产生一个#NAME！错误是因为Excel认为您尝试引入具有命名质量的公式。

这是一个常见错误的简短列表。还有其他的以下是一些常见的解决方案。

• 使用数据►获取外部数据►从文本。明确指定任何不明确的列数据类型;例如98765E3为文本，日期为DMY，MDY，YMD等。甚至可以放弃一列无用的数据。


• 使用文件►打开►文本文件，可以让您通过与上述选项相同的导入向导。这些操作可以使用任一命令重复使用。


• 使用VBA的 Workbooks.OpenText方法，并指定每列的 InfoInfo 位置和数据类型（后者具有 XlColumnDataType 常量）。


• 将导入文件读入内存并处理存储器阵列，然后将其转储到目标工作表中。

有更少的精确解决方案仍然受到Excel的一些解释。使用 Range.PrefixCharacter 强制具有前导零或数字值的数字，可能会被误解为工作表中的科学记数法作为文本。

使用文本限定符字符;通常是ASCII字符034（例如）来包装要解释为文本的值。

复制并粘贴整个文本文件进入目标工作表的列A然后使用 Range.TextToColumns方法 （再次使用FieldInfo选项可用于每列）。

这两种方法会在记事本中导致一些奇怪的值，但记事本isn Excel不能在几秒钟内处理50万次的计算和其他操作，如果你必须混合这两个程序会有一些妥协。

我的建议将值放在记事本中，并使用Excel中随时可用的设施和流程正确导入数据。

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!

解决方案

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.

这篇关于在Excel和记事本中打开时，格式化为CSV文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！