Need quoting with write_csv for Excel countermeasure

Excel does all sorts of unwanted automatic conversions when opening CSV files. Microsoft doesn't care to fix these problems since they've been around for years:

I'd like a write_csv option to write fields that will block automatic conversion later by Excel when reading the file. Control of quoting in write_csv would let me fix my own problems now.

write_csv help says "Values are only quoted if needed: if they contain a comma, quote or newline." Why not give me the power to control quoting for a specific column if I want it?

I have a problem today with an R tibble with a character column of sizes, which are coded as a range in values, like "06-99". write_csv will write this into a file as


And by default Excel turns this range into June 1999. Argh!

I need a way to write this with a space prefix and quoted:

" 06-99"

This can be easily done with something like

mutate(SIZE_RANGE = paste0(" ", SIZE_RANGE)) 

The space is enough when quoted to thwart Excel. So why must I use the old write.csv to be able to do write this to a file instead of using write_csv?

Or perhaps RStudio could help scientists everywhere with something like this?
Escape Excel: A tool for preventing gene symbol and accession conversion errors

We need Excel countermeasures since it's not going away.

1 Like

It's a huge issue in genetics!