Exporting numeric vector as character to CSV

Question

Let the following vectors:

x <- c(123456789123456789, 987654321987654321)
y <- as.character(x)

I'm trying to export y into a csv file for later conversion into XLSX (don't ask, client requested), like so:

 write.csv(y, file = 'y.csv', row.names = F)

If I open y.csv in a pure word processor, I can see it has correctly inserted quotes around the elements, but when I open it in Excel the program insists in converting the column into numbers and showing the contents in scientific format. This requires the extra step of reformatting the column, which can be a real time-waster when one works with lots of files.

How can I format a character vector of 20-digit numbers in R so that Excel doesn't display them in scientific notation?

Publicity

Answer 1

Instead of opening the csv file via File->Open, you can go to Data->From Text in Excel and on the last step specify the Column data format to be Text.

Not really sure though that you save any time by doing this, you can also consider using the WriteXLS (or some other direct to xls) package.

---

edit

Here's a much better way of forcing Excel to read as text:

write.csv(paste0('"=""', y, '"""'), 'y.csv', row.names = F, quote = F)

Droopy

Thanks for providing me with a pre-Excel solution, but my actual problem involves a data frame, say z <- data.frame(cbind(y, letter = c('a', 'b'))). I want to tell write.csv that z$y is not numeric, but it can leave z$letter (and all the other variables in the real data frame) alone. How can I do that? – Publicity

just apply the above paste to the columns you like, in this particular case: z$y = paste0('"=""', z$y, '"""') – Droopy

Smart solution. Now I've got it the way I wanted. Thanks again! – Publicity

write.csv(paste0("=\"", y, "\""), "y.csv", row.names=FALSE,quote=FALSE) – Paba

Answer 2

In Excel, select the column of numbers, and format them as text. (Format Cells -> Number tab -> Text in the list on the left)

Sandrocottus

Thank you for the input, but that's pretty much what I am doing now and would like to automate. – Publicity