Excel to csv file conversion - conserve numbers af

Question

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.

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

Thanks

Answer 1

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

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).

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

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

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

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