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?

score 7 · Accepted Answer · edited May 23 '17 at 11:50

7

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)

edited May 23 '17 at 11:50

Community

1
1

answered Jun 28 '13 at 18:41

eddi

49,088
6
104
155

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? – Waldir Leoncio Jun 28 '13 at 19:27
2

just apply the above `paste` to the columns you like, in this particular case: `z$y = paste0('"=""', z$y, '"""')` – eddi Jun 28 '13 at 19:39
Smart solution. Now I've got it the way I wanted. Thanks again! – Waldir Leoncio Jun 28 '13 at 19:55
write.csv(paste0("=\"", y, "\""), "y.csv", row.names=FALSE,quote=FALSE) – ddunn801 Feb 18 '16 at 23:16

score 1 · Answer 2 · answered Jun 28 '13 at 18:41

1

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

answered Jun 28 '13 at 18:41

Hong Ooi

56,353
13
134
187

1

Thank you for the input, but that's pretty much what I am doing now and would like to automate. – Waldir Leoncio Jun 28 '13 at 19:21

Exporting numeric vector as character to CSV

2 Answers2