characters converted to dates when using write.csv

Question

my data frame has a column A with strings in character form

> df$A
[1] "2-60", "2-61", "2-62", "2-63" etc

I saved the table using write.csv, but when I open it with Excel column A appears formatted as date:

Feb-60
Feb-61
Feb-62
Feb-63
etc

Anyone knows what can I do to avoid this?

I tweaked the arguments of write.csv but nothing worked, and I can't seem to find an example in Stack Overflow that helps solve this problem.

Aubarta

Please show the code you used to write the file – Synovia

It sounds like Excel is doing the conversion, not R. – Selfconfidence

write.csv(df, file="analysis.csv", quote=TRUE) I suspect it's Excel's fault, but I was hoping to do something in R so Excel does not do this. – Aubarta

What is the format if you read that file back into R? – Selfconfidence

@nrussell, great question. if I read "analysis.csv" back into R, I get the original format, not the dates. I combed through Excel options but couldn't find any way to deactivate this annoying automatic formatting. Thanks for your help. – Aubarta

Answer 1

Another solution - a bit tedious, Use Import Text File in Excel, click thru the dialog boxes and in Step 3 of 3 of the Text Import Wizard, you will have an option of setting the column data format, use "Text" for the column that has "2-60", "2-61", "2-62", "2-63". If you use General (the default), Excel tries to be smart and converts the answer for you.

Haber

Thanks, this is the solution that worked best for me, since I needed an output in Excel. – Aubarta

Answer 2

As said in the comments, this is an excel behaviour, not R's. And that can't be deactivated:

Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off. But there are ways to get around it.

Microsoft Office Article

The first suggested way around it according to the article is not helpful, because it relies on changing the cell formatting, but that's too late when you open the .csv file in excel (it's already converted to an integer representing the date).

There is, however, a useful tip:

If you only have a few numbers to enter, you can stop Excel from changing them into dates by entering:

• An apostrophe (‘) before you enter a number, such as ’11-53 or ‘1/47. The apostrophe isn’t displayed in the cell after you press Enter.

So you can make the data display as original by using

vec <- c("2-60", "2-61", "2-62", "2-63")
vec <- paste0("'", vec)

Just remember the values will still have the apostrophe if you read them again in R, so you might have to use

vec <- sub("'", "", vec)

This might not be ideal but at least it works.

One alternative is enclosing the text in =" ", as an excel formula, but that has the same end result and uses more characters.

Goalkeeper

Whoever was the genius on the Excel team who made this a non-optional design decision should be slapped up the side of the head. – Thymic

What amuses me is that instead of simpling creating a checkbox to disable it they write an article saying it can be very frustrating and give some ideas that are useful if you only have a few numbers to enter. – Goalkeeper

Answer 3

I solved the problem by saving the file using the .xlsx format by using the function

    write.xlsx()

from the package xlsx (https://www.rdocumentation.org/packages/xlsx/versions/0.6.5)

Bancroft

To improve your answer try to provide more information. For example package... – Colemancolemanite

Can this package save csv or is it just xlsx? I am having the same problem described above. – Fults