10导入 Excel 与分隔文本

按文件类型选择 readxl 或 data.table，稳定读取 Excel、CSV、TSV、TXT 与压缩表格。

2026-03-08
RExcelreadxldata.tableData Import
本章目录 · 14

表格数据并不只有一种形态。Excel 工作簿可能包含多个工作表、说明行和混合类型；CSV、TSV、TXT 以及它们的压缩文件则更适合交给面向分隔文本的读取器。

因此，稳定的导入流程不是寻找一个包办所有格式的函数，而是先按文件类型选择工具：

  • .xlsx、.xls：使用 readxl::read_excel()；
  • .csv、.tsv、.txt、.gz：使用 data.table::fread()；
  • 多文件任务：先列出文件，再用 lapply()、purrr::map() 等迭代读取。

读取前先确认数据契约

无论使用哪个函数，开始前都应确认：

  • 表头在哪一行，是否只有一行；
  • 空值使用 ""、NA、N/A 还是其他标记；
  • ID、编码、手机号、邮编和 SKU 等列是否必须保留为文本；
  • 日期、时间和时区采用什么格式；
  • 文件中是否包含说明行、合计行或多个数据区域；
  • 多个文件是否具有相同的列名和列类型。

这些约定比自动猜测更可靠。尤其是带前导零的标识符，一旦按数值读入，原始格式便已经丢失。

使用 readxl 读取 Excel

readxl 不依赖本机安装 Excel，基本调用如下：

library(readxl)

df <- read_excel(
  path = "data/raw/survey.xlsx",
  sheet = 1,
  range = NULL,
  skip = 0,
  col_names = TRUE,
  col_types = NULL,
  na = "",
  trim_ws = TRUE
)

sheet 可以是工作表编号或名称；range 可将读取范围限制为 "B2:H999" 这样的区域。对于带有页眉说明、图例或尾部合计的工作表，明确指定 sheet、skip 和 range，通常比读入后再清理更稳妥。

明确保护标识符

对 ID、邮编等字段，应通过 col_types 明确指定文本类型：

df <- read_excel(
  "data/customers.xlsx",
  sheet = "master",
  col_types = c("text", "text", "numeric", "text")
)

这样可以保留 00123 的前导零，并避免长编号显示为科学计数法。

一个可复用的读取封装

下面的 read_excel_flex() 在 read_excel() 之外增加文件检查、可选的列名清理、二次类型转换和 CLI 提示。它只负责读取和轻量整理，不承担长宽变换、分组或衍生变量等分析步骤。

read_excel_flex <- function(
  file_path,
  sheet = 1,
  range = NULL,
  skip = 0,
  col_names = TRUE,
  col_types = NULL,
  na = "",
  trim_ws = TRUE,
  clean_names = TRUE,
  post_type_convert = FALSE,
  verbose = TRUE
) {
  if (!requireNamespace("readxl", quietly = TRUE)) {
    stop("Please install 'readxl'.")
  }
  if (verbose && !requireNamespace("cli", quietly = TRUE)) {
    stop("Please install 'cli'.")
  }
  if (clean_names && !requireNamespace("janitor", quietly = TRUE)) {
    stop("Please install 'janitor'.")
  }
  if (post_type_convert && !requireNamespace("readr", quietly = TRUE)) {
    stop("Please install 'readr'.")
  }

  if (!file.exists(file_path)) {
    if (verbose) {
      cli::cli_alert_danger("File not found: {.path {file_path}}")
    }
    stop("File not found.")
  }

  sheets <- readxl::excel_sheets(file_path)
  if (verbose) {
    cli::cli_h1("Reading Excel")
    cli::cli_alert_info("Sheets: {paste(sheets, collapse = ', ')}")
  }

  df <- readxl::read_excel(
    path = file_path,
    sheet = sheet,
    range = range,
    skip = skip,
    col_names = col_names,
    col_types = col_types,
    na = na,
    trim_ws = trim_ws
  )

  if (clean_names) {
    df <- janitor::clean_names(df)
    if (verbose) cli::cli_alert_success("Column names cleaned")
  }

  if (post_type_convert && is.null(col_types)) {
    df <- readr::type_convert(df, guess_integer = TRUE)
    if (verbose) cli::cli_alert_success("Post type conversion completed")
  }

  if (verbose) {
    sheet_name <- if (is.numeric(sheet)) sheets[sheet] else sheet
    cli::cli_alert_success(
      "Read sheet {.val {sheet_name}} from {.path {file_path}}"
    )
    cli::cli_alert_info("Rows: {nrow(df)} | Cols: {ncol(df)}")
  }

  df
}

例如，只读取有效区域并清理列名：

df <- read_excel_flex(
  "data/report.xlsx",
  sheet = 2,
  range = "B3:K500"
)

批量任务中可以设置 verbose = FALSE，避免每次读取都输出提示。

合并多个工作表

工作表结构一致时，可以在合并的同时记录来源：

library(dplyr)
library(purrr)

file <- "data/survey_all.xlsx"
sheets <- readxl::excel_sheets(file)

df <- map_dfr(sheets, function(sheet_name) {
  read_excel_flex(file, sheet = sheet_name) |>
    mutate(source_sheet = sheet_name)
})

处理 Excel 日期

readxl 通常会识别 Excel 日期。如果某列仍被读取为序列数，可根据工作簿采用的日期系统显式转换：

# 常见的 Windows 日期系统
df$date <- as.Date(df$date_num, origin = "1899-12-30")

# 少数工作簿使用 1904 日期系统
# df$date <- as.Date(df$date_num, origin = "1904-01-01")

如果一列同时混入了文本、日期和数字，应先检查原工作表，而不是盲目转换整列。

使用 fread 读取分隔文本

对于 CSV、TSV、TXT 和压缩文本，data.table::fread() 适合快速读取大表：

library(data.table)

exposure_gwas <- fread(
  file = "data/exposure.txt",
  sep = "\t",
  data.table = FALSE
)

sep 可以明确指定逗号、制表符或其他分隔符。设置 data.table = FALSE 后返回普通 data.frame，便于接入依赖这一类型的既有代码；保留默认值则返回 data.table。

压缩文件可以直接读取：

variants <- fread("data/variants.tsv.gz")

当扩展名或数据来源不够统一时，也可以使用自定义的 evanverse::read_table_flex()，让函数按扩展名选择分隔符，并统一 CLI 反馈：

library(evanverse)

dt <- read_table_flex(
  "data/demo_data.csv.gz",
  verbose = TRUE
)

这个封装主要接受以下参数：

  • file_path：文件路径；
  • sep：手动指定分隔符，未提供时按扩展名判断；
  • encoding：文件编码，默认使用 UTF-8；
  • header：是否包含表头；
  • verbose：是否显示读取过程。

它适合文件命名规范、格式范围明确的数据管道；遇到来源不明的文件时，仍应先检查实际分隔符、编码和表头，而不是只信任扩展名。

将字符解析为数值

表格读入后，带单位、货币符号或千分位分隔符的字段通常仍是字符列。readr 提供两种不同严格程度的数值解析函数：

函数 解析方式 适用数据
parse_double() 要求整个字符串都是合法数值 结构明确、应当是纯数字的字段
parse_number() 忽略数值前后的非数字字符 带单位、货币符号或说明文字的字段

这两个函数都返回 double，但它们表达的数据契约不同：前者用于验证“这一格就是一个数”，后者用于提取“这一格中的第一个数”。

使用 parse_double() 严格解析

library(readr)

parse_double("3.14")
# [1] 3.14

parse_double("1e5")
# [1] 1e+05

如果输入混有单位，parse_double() 会报告解析失败，而不是默默删除字符：

parse_double("3.14 kg")
# 产生解析警告，并返回 NA

因此，它适合本应完全由数值构成的列。解析失败本身就是数据质量信号，可以通过 problems() 继续检查读取或解析问题。

使用 parse_number() 提取数值

parse_number() 会跳过数值前的字符，并在第一个完整数值结束时停止：

parse_number("Weight: 65kg")
# [1] 65

parse_number("$123.45")
# [1] 123.45

parse_number("10,000 people")
# [1] 10000

parse_number("约为50.2%")
# [1] 50.2

带单位的向量可以直接解析：

parse_number(c("180 cm", "2.3 mg/L", "50μg"))
# [1] 180.0 2.3 50.0

parse_number() 只移除文本包装，不理解单位含义。"50%" 会得到 50，如果分析中需要比例，还要显式除以 100：

rates <- c("12%", "5.5%", "约 100%")
parse_number(rates) / 100
# [1] 0.120 0.055 1.000

同理，"2.3 mg/L" 与 "50μg" 解析后的数值不能直接比较；单位标准化仍是独立的数据清洗步骤。

明确小数点和分组符号

locale() 用来描述数值采用的地区格式。例如欧式数值常使用逗号作为小数点、句点作为分组符号：

parse_number(
  "€1.234,56",
  locale = locale(decimal_mark = ",")
)
# [1] 1234.56

一列中如果同时混用 1,234.56 和 1.234,56，不能用一个 locale 安全解释全部值。应先识别来源或格式，再分别解析并统一。

批量清理多列

多个列使用相同格式时，可以结合 dplyr::across()：

library(dplyr)

df <- tibble(
  weight = c("65kg", "72kg", "88kg"),
  height = c("180cm", "175cm", "170cm"),
  price = c("$10.00", "$20.50", "$30.75")
)

df <- df |>
  mutate(
    across(
      c(weight, height, price),
      ~ parse_number(.x, locale = locale(decimal_mark = "."))
    )
  )

批量转换前，应确认这些列确实共享同一小数格式，并记录转换前的单位。解析字符和统一量纲是两个不同步骤。

批量导入文件

先用 list.files() 找到目标文件，再逐个读取：

files <- list.files(
  "data/raw",
  pattern = "\\.(csv|tsv|txt)(\\.gz)?$",
  full.names = TRUE,
  ignore.case = TRUE
)

tables <- lapply(files, fread)
names(tables) <- basename(files)

只有在各文件列结构兼容时才直接纵向合并：

all_data <- rbindlist(tables, idcol = "source_file", fill = TRUE)

fill = TRUE 会为缺失列补空值，但不会替你判断同名列是否具有相同含义。合并前仍需检查列名、类型和单位。

常见问题

  • 合并单元格：读取后只有左上角单元格有值，需要按业务含义决定是否使用 tidyr::fill()；
  • 多行表头和尾部备注：Excel 使用 skip、range，文本文件则在读取参数或读后过滤中处理；
  • 带货币符号或单位的数值：先按文本读取，再用 readr::parse_number()；
  • 编码异常：明确文件编码，不要只在显示端替换乱码；
  • 自动类型不稳定：关键字段显式指定类型，多文件导入时尤其如此；
  • 文件结构不一致：保留文件名或工作表名作为来源列，便于定位问题。

把文件发现、格式选择、类型约束和来源记录分开处理，导入步骤就能从一次性的手工操作变成可复用的数据入口。