write.csv in R: Something between

I have a question concerning write.csv or write.table - both did not bring what I wanted but produce the same:

I need to produce a .csv file in an externally given format. The requirements are:

Fields are always in plain text. Only if the field contains special characters (in terms of the CSV syntax) the following escaping is used: If the field value contains a "," or/and a double quote character, the complete field is encapsulated within double quotes. If the field originaly already contains double quotes, each of such quotes is repeated twice to make it distinguable of the additional double quotes, e.g. the value
This " is a , special value"
will become
"This "" is a , special value"""

The requirement is clear to me but I am struggling to write out the exact format. I already found out about the options "quote" and "qmethod" and here is what I did so far:


foo <- 
  data.table(
    'var1' = c('Test', 'Test\"3', "This \" is a , special value\""), 
    'var2' = c("This \" is a , special value\"", 'Bla\"', 'Test')
  )

write.csv(
  x = foo,
  file = 'test.csv', 
  quote = T,
  row.names = F
)

Which results in the following .csv file:

"var1","var2"
"Test","This "" is a , special value"""
"Test""3","Bla"""
"This "" is a , special value""","Test"

This does not fulfill the requirements because each value is surroundes by double quotes. However, note that it fulfills the criteria concerning repeating the double quotes inside the values.

Now, if I set quote = F, I receive:

var1,var2
Test,This " is a , special value"
Test"3,Bla"
This " is a , special value",Test

Now it does not fulfill both criteria.

I tried similar stuff with write.table but I had the same problem. I tried to google it but did not really find an answer as it seems - at least for me - to be a special problem.

Any help with this? Thanks very much in advance :)

Solution

You could just make the changes through regex and paste the results together into one big string:

output <- paste(names(foo), collapse = ",") |>
  paste0("\n") |>
  paste0(
    lapply(foo, function(x) {
      x <- gsub("\"", "\"\"", x)
      x[grepl("\"|,", x)] <- paste0("\"", x[grepl("\"|,", x)], "\"")
      x
    }) |>
    as.data.frame() |>
    apply(1, function(x) paste(x, collapse = ",")) |>
    paste(collapse = "\n")
  )

Now you can write the result with writeLines

writeLines(output, "myfile.csv")

Resulting in

myfile.csv

var1,var2
Test,"This "" is a , special value"""
"Test""3","Bla"""
"This "" is a , special value""",Test