导入具有本地化的Excel中的CSV US格式的数字吗? [英] Importing CSV US formatted numbers in Excel with localisation?

问题描述

我有一个.csv文件，其值如下:

I have a .csv file with the following values:

1488201602.653, 8.304700E-04, 3.079498E-03
1488201603.107, 8.677357E-04, 2.856719E-03
1488201821.012, 7.071995E-04, 4.147542E-03

从摘要中可以看到，数字采用不同的格式:第一列具有完整的数字，并且句点.作为小数点分隔符.第二和第三列使用科学计数法表示数字，不同之处在于使用大写字母E，并且再次使用句点作为小数点分隔符.任何值中都没有千位分隔符.

As visible from the snippet, the numbers are in differing format: the first column has a full number, and a period . as a decimal point separator. The second and third columns have numbers in scientific notation, except a capital E is used, and again a period is used for the decimal separator; there are no thousands separator in any of the values.

当我尝试将其导入丹麦语本地化版本的Excel 2016中时，我得到的是这样的:

When I try to import this in a Danish localized version of Excel 2016, what I get is something like this:

因此，第一列中的句点.显然是成千上万个分隔符，但是，如果选择第一个数字，则公式字段将显示以下内容:

So, I'm apparently getting a ton of thousand separators as periods . in the first column, however, if I select the first number, the formula field shows this:

...的意思是，原来在.csv文件中的1488201602.653编号现在被解释为整数1488201602653，这是完全错误的.

... meaning, the number that was originally 1488201602.653 in the .csv file, now became interpreted as the integer 1488201602653, which is completely wrong.

对于第二列和第三列，如果我选择一个数字，则公式字段将显示:

For the sevcond and third columns, if I select a number, then the formula field shows:

...的意思是，原来在.csv文件中的8.304700E-04编号，然后在单元格中变为8,30E+02的编号，在.csv中显示为830,47，这又是-完全错误的

... meaning, the number that was originally 8.304700E-04 in the .csv file, then became 8,30E+02 in the cell, shown as 830,47 in the .csv, which is - again - completely wrong.

如何说服Excel以正确的数值将数据导入.csv文件(在美国或C语言环境中)，以便在丹麦语本地化下正确显示它们(即1488201602,653和)?

How can I persuade Excel to import the data in the .csv file, which in USA or C locale, in its proper numeric values, so they are shown properly under Danish localisation (that is, 1488201602,653 and 8,304700e-04)?

推荐答案

好吧，我找到了一种手动方法来解决此问题，但是仍然很高兴知道是否有自动方法.

Well, I found a manual way to handle this issue, but it would still be nice to know if there is an automatic one.

首先，获取并安装 Notepad ++ 已经拥有了.

First, get and install Notepad++ if you don't already have it.

然后，请注意:

• 在美国(或"C"语言)本地化下，没有千位分隔符(即，它是一个空字符串")-在丹麦语本地化中，千位分隔符是句点"."
• 在美国(或"C"语言)本地化下，小数点分隔符是句点"."-在丹麦语本地化下，小数点分隔符是逗号","
• 丹麦语本地化要求E符号指数使用小写字母e而不是大写字母E

• Under US (or "C" language) localization, there is no thousands separator (i.e. it is an empty string, "") - under Danish localization, the thousands separator is period "."
• Under US (or "C" language) localization, the decimal separator is a period "." - under Danish localization, the decimal separator is comma ","
• The Danish localization demands that the E-notation exponent is written as miniscule letter e, not as a capital letter E

然后，在Notepad ++中打开您的.csv文件，并可能将其另存为其他文件名下的副本.然后，按以下顺序进行以下替换:

Then, open your .csv file in Notepad++, and possibly save it as a copy under a different filename. Then, do the following replacements in this order:

• 搜索逗号,->用分号;替换(全部替换)
• 搜索句点.->用逗号,替换(全部替换)
• 搜索大写字母E->用小写的e替换(全部替换)

• Search for comma , -> replace with semicolon ; (replace all)
• Search for period . -> replace with comma , (replace all)
• Search for capital E -> replace with miniscule e (replace all)

然后保存文件，并将其导入Excel.在Excel中导入时，请记住将分号;指定为CSV字段分隔符-并且数字(至少按照OP示例)应被读入并正确解释.

Then save the file, and import it in Excel. When importing in Excel, remember to specify the semicolon ; as a CSV field separator - and the numbers (at least as per the OP example) should be read-in and interpreted correctly.

这篇关于导入具有本地化的Excel中的CSV US格式的数字吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！