如何在R中将xlsx转csv时保留时间戳？混合列读取异常求助

Hey there! Let's tackle these two R-related problems one by one, since they're both about handling date/timestamp formats correctly.

The key here is to make sure you correctly read the timestamps from Excel first, then write them to CSV in a human-readable format (instead of letting Excel or R auto-convert them to numeric values).

• Step 1: Use the readxl package to read your XLSX file—it automatically detects Excel's date/time formats and converts them to R's POSIXct or Date types:

  library(readxl)
  # Read the file (replace with your sheet name/file path)
  df <- read_excel("your_input_file.xlsx", sheet = "Sheet1")
  # Verify the timestamp column type should show POSIXct/POSIXlt or Date
  str(df$your_timestamp_column)
  

• Step 2: Format the timestamp column as a string before writing to CSV, so it stays in your desired format:

  # Format the timestamp to match your original Excel format (e.g., "MM/DD/YYYY HH:MM")
  df$your_timestamp_column <- format(df$your_timestamp_column, "%m/%d/%Y %H:%M")
  # Write to CSV, excluding row names
  write.csv(df, "your_output_file.csv", row.names = FALSE)
  

If you prefer a more Excel-aligned workflow, the openxlsx package works similarly—just read with read.xlsx() and follow the same formatting step before writing to CSV.

When a CSV column has both text (like "All is well") and date/timestamps that get converted to Excel-style serial numbers (e.g., 42009 for 1/5/2015), the issue is R's default column type inference. Here are two solid fixes:

Option 1: Read as Character First, Then Convert Date Rows #

First, read the entire column as character to preserve raw values, then target only the date rows for conversion:

# Read CSV with all columns as character (stringsAsFactors is FALSE by default in R ≥4.0)
df <- read.csv("your_mixed_column.csv", stringsAsFactors = FALSE)

# Define a helper function to convert Excel serial numbers to timestamps
convert_excel_serial <- function(x) {
  num_val <- suppressWarnings(as.numeric(x))
  # Check if it's a valid Excel date (25569 = 1970-01-01, a reasonable lower bound)
  if (!is.na(num_val) && num_val > 25569) {
    # Convert to POSIXct (handles date + time)
    as.POSIXct((num_val - 25569) * 86400, tz = "UTC")
  } else {
    # Keep the original text if it's not a date
    x
  }
}

# Apply the function to your mixed column
df$mixed_column <- sapply(df$mixed_column, convert_excel_serial)

The decimal part of the serial number (like 0.01042) represents the time fraction of a day, so this conversion preserves the full timestamp.

Option 2: Use readr for Targeted Parsing #

The readr package lets you define column types upfront, and we can use lubridate to parse only the date-time strings:

library(readr)
library(lubridate)

# Read the column as character to avoid auto-conversion
df <- read_csv(
  "your_mixed_column.csv",
  col_types = cols(mixed_column = col_character())
)

# Use regex to identify date-time rows, then parse them
df$mixed_column <- ifelse(
  grepl("^\\d+/\\d+/\\d+ \\d+:\\d+$", df$mixed_column),
  parse_date_time(df$mixed_column, "m/d/Y H:M"),
  df$mixed_column
)

This method is more precise—it uses a regex pattern to match your specific date-time format, so only valid date rows get converted.

A quick pro tip: If you're exporting the CSV from Excel directly, set the mixed column to Text format before exporting. This prevents Excel from converting dates to serial numbers in the first place, making your R workflow smoother.

内容的提问来源于stack exchange，提问作者Swapnil Shankar