duckdb_read_csv读取含双引号转义的GBIF CSV报错解决方案

问题背景 #

有一份GBIF生成的大型CSV文件，内部使用双引号转义格式（例如"Henry ""Nick"" Robertson"），无法直接修改原始文件。在R环境中使用DuckDB 1.3.3版本的duckdb_read_csv函数读取时，触发「未终止引号」错误，读取失败。

测试用例 #

CSV内容 #

"gbifID","datasetKey","occurrenceID","kingdom","phylum","class","order","family","genus","species","infraspecificEpithet","taxonRank","scientificName","verbatimScientificName","verbatimScientificNameAuthorship","countryCode","locality","stateProvince","occurrenceStatus","individualCount","publishingOrgKey","decimalLatitude","decimalLongitude","coordinateUncertaintyInMeters","coordinatePrecision","elevation","elevationAccuracy","depth","depthAccuracy","eventDate","day","month","year","taxonKey","speciesKey","basisOfRecord","institutionCode","collectionCode","catalogNumber","recordNumber","identifiedBy","dateIdentified","license","rightsHolder","recordedBy","typeStatus","establishmentMeans","lastInterpreted","mediaType","issue"
4028745676,"50c9509d-22c7-4a22-a47d-8c48425ef4a7","https://www.inaturalist.org/observations/120076657","Plantae","Tracheophyta","Magnoliopsida","Apiales","Araliaceae","Aralia","Aralia nudicaulis",NA,"SPECIES","Aralia nudicaulis L.","Aralia nudicaulis",NA,"CA",NA,"Québec","PRESENT",NA,"28eb1a3f-1c15-4a95-931a-4af90ecb574d",45.44365,-74.255022,49,NA,NA,NA,NA,NA,"2022-06-03T13:07:53",3,6,2022,3037021,3037021,"HUMAN_OBSERVATION","iNaturalist","Observations",120076657,NA,"Henry ""Nick"" Robertson","2022-11-22 13:59:38","CC_BY_NC_4_0","katenormand","katenormand",NA,NA,"2025-09-08 07:19:47.44","StillImage","COORDINATE_ROUNDED;CONTINENT_DERIVED_FROM_COORDINATES;TAXON_ID_NOT_FOUND"

生成测试CSV的R代码 #

csv = structure(list(gbifID = 4028745676, datasetKey = "50c9509d-22c7-4a22-a47d-8c48425ef4a7", 
    occurrenceID = "https://www.inaturalist.org/observations/120076657", 
    kingdom = "Plantae", phylum = "Tracheophyta", class = "Magnoliopsida", 
    order = "Apiales", family = "Araliaceae", genus = "Aralia", 
    species = "Aralia nudicaulis", infraspecificEpithet = NA_character_, 
    taxonRank = "SPECIES", scientificName = "Aralia nudicaulis L.", 
    verbatimScientificName = "Aralia nudicaulis", verbatimScientificNameAuthorship = NA, 
    countryCode = "CA", locality = NA, stateProvince = "Québec", 
    occurrenceStatus = "PRESENT", individualCount = NA, publishingOrgKey = "28eb1a3f-1c15-4a95-931a-4af90ecb574d", 
    decimalLatitude = 45.44365, decimalLongitude = -74.255022, 
    coordinateUncertaintyInMeters = 49, coordinatePrecision = NA, 
    elevation = NA, elevationAccuracy = NA, depth = NA, depthAccuracy = NA, 
    eventDate = "2022-06-03T13:07:53", day = 3L, month = 6L, 
    year = 2022L, taxonKey = 3037021L, speciesKey = 3037021L, 
    basisOfRecord = "HUMAN_OBSERVATION", institutionCode = "iNaturalist", 
    collectionCode = "Observations", catalogNumber = 120076657L, 
    recordNumber = NA, identifiedBy = "Henry \"Nick\" Robertson", 
    dateIdentified = "2022-11-22 13:59:38", license = "CC_BY_NC_4_0", 
    rightsHolder = "katenormand", recordedBy = "katenormand", 
    typeStatus = NA, establishmentMeans = NA, lastInterpreted = "2025-09-08 07:19:47.44", 
    mediaType = "StillImage", issue = "COORDINATE_ROUNDED;CONTINENT_DERIVED_FROM_COORDINATES;TAXON_ID_NOT_FOUND"), class = "data.frame", row.names = c(NA, 
-1L))
# 导出CSV
write.csv(csv, 
          file = 'inat_test.csv', 
          row.names = FALSE)

原始读取代码（触发错误） #

con <- dbConnect(duckdb())
gbif_csv = duckdb_read_csv(conn = con,
                           name = "test",
                           files ='inat_test.csv',
                           delim = ",",
                           header = TRUE)

错误信息 #

Error in `duckdb_result()`:
  ! rapi_execute: Failed to run query
Error: Invalid Input Error: CSV Error on Line: 3467
Original Line: 4028745676,"50c9509d-22c7-4a22-a47d-8c48425ef4a7","https://www.inaturalist.org/observations/120076657","Plantae","Tracheophyta","Magnoliopsida","Apiales","Araliaceae","Aralia","Aralia nudicaulis",NA,"SPECIES","Aralia nudicaulis L.","Aralia nudicaulis",NA,"CA",NA,"Québec","PRESENT",NA,"28eb1a3f-1c15-4a95-931a-4af90ecb574d",45.44365,-74.255022,49,NA,NA,NA,NA,NA,"2022-06-03T13:07:53",3,6,2022,3037021,3037021,"HUMAN_OBSERVATION","iNaturalist","Observations","120076657",NA,"Henry ""Nick"" Robertson",2022-11-22 13:59:38,"CC_BY_NC_4_0","katenormand","katenormand",NA,NA,2025-09-08 07:19:47.44,"StillImage","COORDINATE_ROUNDED;CONTINENT_DERIVED_FROM_COORDINATES;TAXON_ID_NOT_FOUND"
Value with unterminated quote found.

Possible fixes:
  * Disable the parser's strict mode (strict_mode=false) to allow reading rows that do not comply with the CSV standard.
* Enable ignore errors (ignore_errors=true) to skip this row
* Set quote to empty or to a different value (e.g., quote='')

  file = a_big_CSV.csv
  delimiter = , (Set By User)
  quote = " (Set By User)
  escape = \0 (Auto-Detected)
  new_line = \n (Auto-Detected)
  header = true (Set By User)
  skip_rows = 0 (Auto-Detected)
  comment = \0 (Auto-Detected)
  strict_mode = true (Auto-Detected)
  date_format =  (Auto-Detected)
  timestamp_format =  (Auto-Detected)
  null_padding = 0
  sample_size = 20480
  ignore_errors = false
  all_varchar = 0
The Column types set by the user do not match the ones found by the sniffer. 
Column at position: 0 Set type: DOUBLE Sniffed type: BIGINT
Column at position: 30 Set type: INTEGER Sniffed type: BIGINT
Column at position: 31 Set type: INTEGER Sniffed type: BIGINT
Column at position: 32 Set type: INTEGER Sniffed type: BIGINT
Column at position: 33 Set type: INTEGER Sniffed type: BIGINT
Column at position: 34 Set type: INTEGER Sniffed type: BIGINT
Column at position: 38 Set type: INTEGER Sniffed type: BIGINT
Column at position: 47 Set type: VARCHAR Sniffed type: TIMESTAMP

Run `rlang::last_trace()` to see where the error occurred.

解决方案 #

问题核心是DuckDB自动检测的转义符不符合GBIF CSV的规则：GBIF使用双引号本身作为转义符（即""表示单个"），但DuckDB默认自动检测时未识别到这一点，导致解析时误判为未终止的引号。

只需在duckdb_read_csv中显式指定escape = '"'，即可让DuckDB正确解析双引号转义格式：

修正后的读取代码 #

con <- dbConnect(duckdb())
gbif_csv <- duckdb_read_csv(
  conn = con,
  name = "test",
  files = 'inat_test.csv',
  delim = ",",
  header = TRUE,
  escape = '"'  # 关键设置：指定双引号为转义符
)

# 验证转义解析是否正确
dbGetQuery(con, "SELECT identifiedBy FROM test")

执行后，identifiedBy列会正确显示为Henry "Nick" Robertson，说明转义解析正常。

如果遇到列类型不匹配的警告，可以额外添加auto_detect = TRUE让DuckDB自动适配列类型，或者手动指定columns参数定义列类型。

内容的提问来源于stack exchange，提问作者M. Beausoleil