关于data.table::fread处理嵌入式引号的技术求助

问题场景 #

使用data.table::fread导入CSV文件时，出现双引号转义不符合预期的情况：

• CSV中的原始字段内容：

  ...,"TIRE WAS FOUND ""DEFLATED"", SUGGESTING FAILURE",...

• 导入后显示结果：

  TIRE WAS FOUND ""DEFLATED"", SUGGESTING FAILURE

• 预期结果（数据库原始值）：

  TIRE WAS FOUND "DEFLATED", SUGGESTING FAILURE

使用的R代码：

cur01 <- data.table::fread(base::paste0(newdata, "incidents.csv"), 
                       encoding = "unknown",
                       sep = ",",
                       dec = ".",
                       quote = "\"",
                       nrows = Inf,
                       skip = 0,
                       header = TRUE,
                       stringsAsFactors = FALSE,
                       data.table = FALSE,
                       showProgress = TRUE,
                       nThread = parallel::detectCores())

环境说明：R版本4.3.1，data.table版本1.14.8

解决建议 #

• 关闭fread引号解析后手动替换
  先禁用fread的引号解析功能，再通过字符串替换把连续的两个双引号转为单个：

  cur01 <- data.table::fread(base::paste0(newdata, "incidents.csv"), 
                         encoding = "unknown",
                         sep = ",",
                         dec = ".",
                         quote = "",  # 关闭引号解析
                         nrows = Inf,
                         skip = 0,
                         header = TRUE,
                         stringsAsFactors = FALSE,
                         data.table = FALSE,
                         showProgress = TRUE,
                         nThread = parallel::detectCores())
  # 替换所有连续双引号为单个
  cur01$target_column <- gsub('""', '"', cur01$target_column)
  

• 改用read.csv并设置转义参数
  如果fread的处理逻辑不适用，换用基础包的read.csv，指定双引号作为自身的转义字符：

  cur01 <- read.csv(base::paste0(newdata, "incidents.csv"),
                    encoding = "unknown",
                    sep = ",",
                    dec = ".",
                    quote = "\"",
                    escape = "\"",  # 用双引号转义自身
                    stringsAsFactors = FALSE)
  

• 检查数据库导出规则
  数据库导出CSV时，部分系统会把单个双引号转义为两个（符合CSV标准），如果想从源头解决，可以调整数据库导出CSV的转义设置，让输出的双引号格式直接匹配预期。

内容的提问来源于stack exchange，提问作者Bill Knowlton