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:
https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-alarming-number-of-scientific-papers-contain-excel-errors/
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
06-99
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.