How to preserve numbers in a character format for Excel with data.table's fwrite-CodePudding

In the example below, when I save the number in a character format (i.e., .10) as a CSV file using data.table's fwrite, Excel displays it as a number rather than a character string.

Is there a way to save the number in a character format that Excel can recognize as a string?

I'm not simply trying to remove the 0 before the decimal point or keep the trailing 0.

I'd like to keep the character strings intact, exactly as they would be displayed in R (e.g., .10 but without the quotation marks).

dt <- data.table(a = ".10")
fwrite(dt, "example.csv")

# The saved CSV file opened in Excel shows the number 0.1, rather than the string .10

CodePudding user response：

Excel is mostly brain-dead with regards to reading things in as you want. Here's one workaround:

dat <- data.frame(aschr = c("1", "2.0", "3.00"), asnum = 1:3, hascomma = "a,b", hasnewline = "a\nb", hasquote = 'a"b"')
notnumber <- sapply(dat, function(z) is.character(z) & all(is.na(z) | !grepl("[^-0-9.]", z)))
needsquotes <- sapply(dat, function(z) any(grepl('[,\n"]', z))) & !notnumber
dat[needsquotes] <- lapply(dat[needsquotes], function(z) dQuote(gsub('"', '""', z), FALSE))
dat[notnumber] <- lapply(dat[notnumber], function(z) paste0("=", dQuote(z, FALSE)))
fwrite(dat, "iris.csv", quote = FALSE)

Resulting in the following perspective in Excel.

Most of this is precautionary: if you know your data well and you know that none of the data contains commas, quotes, or newlines, then you can do away with the needsquotes portions. notnumber is the column(s) of interest.

Bottom line, we "trick" excel into keeping it a string by forcing it as an excel formula. This may not work with other spreadsheets (e.g., Calc), I haven't tested.