Excel到csv文件转换 - 保存小数点后的数字

时间:2021-05-17 17:12:04

When converting a xls file to a csv file using 'Save as...' in Excel (which will then be imported into R), the csv output file systematically drops all numbers after the decimal point e.g. 1.0524 becomes 1, 5.213 becomes 5 and so on.

当使用Excel中的“另存为...”将xls文件转换为csv文件(然后将其导入R)时,csv输出文件会系统地删除小数点后的所有数字,例如: 1.0524变为1,5.213变为5,依此类推。

How can I prevent this which obviously leads to problems of accuracy over a large number of data entries?

如何防止这显然会导致大量数据条目的准确性问题?

Thanks

1 个解决方案

#1


5  

Excel doesn't do that. However, it's pretty common for non-US users to stumble over Excel's CSV output format.

Excel不会这样做。但是,非美国用户偶然发现Excel的CSV输出格式是很常见的。

In the US, Excel uses a . for a decimal point and , as a CSV separator (obviously, it's called comma-separated values for a reason).

在美国,Excel使用a。对于小数点,作为CSV分隔符(显然,出于某种原因,它被称为逗号分隔值)。

In many European countries, Excel uses a , for a decimal point and ; as a CSV separator (according to the countries' conventions for decimal notation).

在许多欧洲国家,Excel使用a,小数点和;作为CSV分隔符(根据各国的十进制表示法惯例)。

If you try to read an EU CSV file with a US CSV library, you'll get mixed-up field values for obvious reasons.

如果您尝试使用US CSV库读取EU CSV文件,则出于显而易见的原因,您将获得混合字段值。

So, for example in R you need to tell your CSV reader this:

因此,例如在R中,您需要告诉您的CSV阅读器:

table = read.csv2(file, sep = ";", quote = "\"", dec = ",")

#1


5  

Excel doesn't do that. However, it's pretty common for non-US users to stumble over Excel's CSV output format.

Excel不会这样做。但是,非美国用户偶然发现Excel的CSV输出格式是很常见的。

In the US, Excel uses a . for a decimal point and , as a CSV separator (obviously, it's called comma-separated values for a reason).

在美国,Excel使用a。对于小数点,作为CSV分隔符(显然,出于某种原因,它被称为逗号分隔值)。

In many European countries, Excel uses a , for a decimal point and ; as a CSV separator (according to the countries' conventions for decimal notation).

在许多欧洲国家,Excel使用a,小数点和;作为CSV分隔符(根据各国的十进制表示法惯例)。

If you try to read an EU CSV file with a US CSV library, you'll get mixed-up field values for obvious reasons.

如果您尝试使用US CSV库读取EU CSV文件,则出于显而易见的原因,您将获得混合字段值。

So, for example in R you need to tell your CSV reader this:

因此,例如在R中,您需要告诉您的CSV阅读器:

table = read.csv2(file, sep = ";", quote = "\"", dec = ",")