如何在R中批量读取.xlsx文件构建语料库以生成词-文档矩阵？

Hey there! I totally get the frustration of dealing with hundreds of XLSX files manually—especially when you already have a seamless workflow for PDFs. Let’s build a similar batch processing system to get those XLSX files into a corpus, just like your PDF setup.

Step 1: Get All XLSX File Paths #

First, we’ll grab the paths to all your XLSX files in the target directory (same aa directory you used for PDFs):

# Get full paths to all .xlsx files in directory 'aa'
xlsx_files <- list.files(path = aa, pattern = "\\.xlsx$", full.names = TRUE, ignore.case = TRUE)

• pattern = "\\.xlsx$" ensures we only pick up XLSX files
• full.names = TRUE gives us the complete file path, not just the filename
• ignore.case = TRUE catches files with .XLSX extensions too

Step 2: Create a Custom XLSX Reader Function #

We need a function that reads an XLSX file, extracts all its text, and returns a PlainTextDocument (the same format that readPDF outputs, so it works seamlessly with Corpus).

Basic Version (Read First Sheet) #

This reads the first sheet of each XLSX and combines all cell text into a single string:

library(tm) # Make sure you have the tm package loaded for PlainTextDocument

read_xlsx_text <- function(file_path) {
  # Read the first sheet of the XLSX file
  df <- xlsx::read.xlsx(file_path, sheetIndex = 1, stringsAsFactors = FALSE)
  
  # Flatten the data frame into a single string (adjust separator if needed)
  document_text <- paste(unlist(df), collapse = " ")
  
  # Return a PlainTextDocument with the filename as the ID
  return(PlainTextDocument(document_text, id = basename(file_path)))
}

Advanced Version (Read All Sheets) #

If your XLSX files have multiple sheets you need to include, use this tweaked function:

read_xlsx_all_sheets <- function(file_path) {
  # Get number of sheets in the file
  sheet_count <- xlsx::getNumberOfSheets(file_path)
  
  all_sheet_text <- c()
  for(sheet_idx in 1:sheet_count) {
    df <- xlsx::read.xlsx(file_path, sheetIndex = sheet_idx, stringsAsFactors = FALSE)
    sheet_text <- paste(unlist(df), collapse = " ")
    all_sheet_text <- c(all_sheet_text, sheet_text)
  }
  
  # Combine text from all sheets into one document
  full_document_text <- paste(all_sheet_text, collapse = " ")
  return(PlainTextDocument(full_document_text, id = basename(file_path)))
}

Safe Version (Handle Errors) #

To avoid a single corrupted file breaking the entire batch, add error handling with tryCatch:

read_xlsx_safe <- function(file_path) {
  tryCatch({
    df <- xlsx::read.xlsx(file_path, sheetIndex = 1, stringsAsFactors = FALSE)
    document_text <- paste(unlist(df), collapse = " ")
    PlainTextDocument(document_text, id = basename(file_path))
  }, error = function(e) {
    # Print a warning instead of stopping the whole process
    warning(paste("Skipping file", file_path, ":", e$message))
    return(NULL)
  })
}

Step 3: Build the XLSX Corpus #

Now apply your chosen reader function to all XLSX files and convert the result into a corpus:

# Batch process all XLSX files (use read_xlsx_all_sheets or read_xlsx_safe if needed)
xlsx_documents <- lapply(xlsx_files, read_xlsx_text)

# If using the safe version, filter out any NULL results from failed files
# xlsx_documents <- Filter(Negate(is.null), lapply(xlsx_files, read_xlsx_safe))

# Convert the list of PlainTextDocuments into a Corpus
xlsx_corpus <- Corpus(VectorSource(xlsx_documents))

Step 4: Combine with Your PDF Corpus #

Now you can merge your XLSX corpus with the PDF corpus you already built to create a single corpus for your term-document matrix:

# Combine PDF and XLSX corpora
combined_corpus <- c(bb, xlsx_corpus)

Pro Tips #

• Adjust Text Merging: If you need to preserve structure (e.g., keep rows separate), modify the collapse argument in paste() (e.g., collapse = "\n" for new lines between rows).
• Sheet Names: If you need to target specific sheets by name instead of index, use sheetName = "Sheet1" instead of sheetIndex = 1 in read.xlsx().
• Performance: For very large XLSX files, consider using the readxl package instead of xlsx (it’s faster and doesn’t require Java). Just replace xlsx::read.xlsx() with readxl::read_xlsx().

内容的提问来源于stack exchange，提问作者Byung Yun Son