如何在R中正确读取含特殊分隔符与编码的CSV文件

问题背景 #

需读取一份调查类CSV数据，文件使用¤作为分隔符，编码特殊。目前通过read_delim指定分隔符为¤、编码为ISO-8859-1可实现读取，但效果不佳；该文件可正常导入Google Sheets，希望优化R中的读取结果。

当前代码与样本数据 #

文件下载与当前读取代码 #

download.file('https://www.dropbox.com/scl/fi/kl8a86fiyim15g4op4er4/test_file.csv?rlkey=bx8mozu395r3zkl4ddka9fhsy&dl=1',
              destfile="test_file.csv",
              method="auto")

best_so_far <- read_delim(here("test_file.csv"), delim = '¤', 
                          locale = locale(encoding = "ISO-8859-1"), 
                          quote = "")

Google Sheets显示的样本数据 #

7   150 3   2   2   99999998        99999998    99999998    99999998    99999998    99999998    99999998        3       99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998    99999998        99999998    17579   1 000 st 20 - 199   20-199 anställda    Bildelar & -tillbehör, partihandel  45310   100 64  STOCKHOLM
8   100 3   2   2   99999998        99999998    99999998    99999998    99999998    99999998    99999998        3       99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998    99999998        99999998    17579   1 000 st 20 - 199   20-199 anställda    Dagstidningsförlag  58131   100 64  STOCKHOLM
14  400 3   2   2   99999998        99999998    99999998    99999998    99999998    99999998    99999998        3       99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998    99999998        99999998    99999998    99999998    99999998    99999998    99999998        99999998    17579   1 000 st 20 - 199   20-199 anställda    Personal, uthyrning 78200   101 32  STOCKHOLM

原始样本行 #

7¤150¤3¤2¤2¤99999998¤¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤¤3¤¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤¤99999998¤99999998¤99999998¤99999998¤99999998¤¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤¤99999998¤17579¤1 000 st 20 - 199¤20-199 anställda¤Bildelar & -tillbehör, partihandel¤45310¤100 64¤STOCKHOLM
8¤100¤3¤2¤2¤99999998¤¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤¤3¤¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤¤99999998¤99999998¤99999998¤99999998¤99999998¤¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤¤99999998¤99999998¤99999998¤99999998¤99999998¤99999998¤¤99999998¤17579¤1 000 st 20 - 199¤20-199 anställda¤Dagstidningsförlag¤58131¤100 64¤STOCKHOLM

优化方案 #

1. 准确识别文件编码 #

先用readr::guess_encoding检测文件实际编码，避免经验猜测误差：

library(readr)
guess_encoding("test_file.csv")

若检测结果偏向Windows-1252，可替换原编码参数，该编码与ISO-8859-1高度兼容，能更好处理特殊字符。

2. 统一空值与缺失值处理 #

样本中99999998为缺失值编码，连续¤对应空字段，需统一转为R的NA：

optimized_read <- read_delim("test_file.csv", 
                             delim = '¤', 
                             locale = locale(encoding = "Windows-1252"),
                             quote = "",
                             na = c("", "99999998"),
                             trim_ws = TRUE)

• trim_ws = TRUE：自动去除字段两端冗余空格，保证数据整洁。

3. 控制列类型避免自动识别错误 #

部分字段（如100 64）含空格，自动识别易出错，可先统一读为字符类型，后续按需转换：

optimized_read <- read_delim("test_file.csv", 
                             delim = '¤', 
                             locale = locale(encoding = "Windows-1252"),
                             quote = "",
                             na = c("", "99999998"),
                             trim_ws = TRUE,
                             col_types = cols(.default = col_character()))

# 示例：将第一列转为整数
optimized_read[[1]] <- as.integer(optimized_read[[1]])

4. 尝试data.table的fread替代 #

data.table::fread对特殊分隔符和编码的兼容性更强，读取效率更高：

library(data.table)
dt_read <- fread("test_file.csv", 
                 sep = "¤",
                 encoding = "Latin-1", # 对应ISO-8859-1
                 na.strings = c("", "99999998"),
                 trim.ws = TRUE)

内容的提问来源于stack exchange，提问作者Tomas R