R中数值格式扫描行数设置及CSV小数截断问题求助

Got it, let's break down solutions for your two questions regarding how R infers numeric column formats and fixing that frustrating decimal truncation issue with large CSV files:

1. 检查/设置ODBC连接时R推断数值格式的扫描行数 #

The way R checks rows for type inference over ODBC depends on which package you're using (the modern odbc package vs. the older RODBC):

对于odbc包（tidyverse推荐的ODBC接口） #

• 查看默认扫描行数: Run this command to check the global default number of rows R scans to guess column types:

  getOption("odbc.guess_max")
  

  By default, this is set to 1000 rows.
• 自定义扫描行数: When reading tables or running queries, use the guess_max parameter to specify how many rows R should scan. For example:

  library(odbc)
  # Establish connection
  con <- dbConnect(odbc(), Driver = "YourDriverName", Server = "YourServer", Database = "YourDB", ...)
  # Scan first 5000 rows to infer column types
  data <- dbReadTable(con, "your_table_name", guess_max = 5000)
  

对于旧版RODBC包 #

• RODBC defaults to scanning the first 25 rows to infer column types. To work around this, you can first sample a larger subset of rows to get accurate column types, then read the full dataset with those types:

  library(RODBC)
  con <- odbcConnect("YourDSNName")
  # Sample first 1000 rows to detect correct column types
  sample_data <- sqlFetch(con, "your_table_name", max = 1000)
  col_types <- sapply(sample_data, class)
  # Read full data using the pre-detected types
  full_data <- sqlFetch(con, "your_table_name", colClasses = col_types)
  

2. 修复大型CSV读取时的小数截断问题 #

The issue happens because R's default type inference only scans a small number of rows (way less than your 2GB file's total rows) and assumes numeric columns are integers if the sampled rows have no decimals. The as.is parameter you tried only affects string-to-factor conversion, not numeric type inference—here's how to fix it:

如果你用基础R的read.csv/read.table #

Base R uses type.convert() under the hood, which only scans the first 5 rows by default to guess types. To increase this scan count, you can pass a custom function to colClasses that uses type.convert() with a higher n value:

# Scan first 100,000 rows to infer column types
data <- read.csv("your_large_file.csv", colClasses = function(x) type.convert(x, n = 100000))

Alternatively, if you know exactly which columns are numeric, you can explicitly set colClasses to "numeric" for those columns to bypass inference entirely.

如果你用readr包的read_csv（tidyverse工具） #

read_csv is optimized for large files and defaults to scanning 1000 rows. To increase this, use the guess_max parameter:

library(readr)
# Scan first 100,000 rows to ensure accurate type inference
data <- read_csv("your_large_file.csv", guess_max = 100000)

For extra safety (especially with extremely large files), you can directly specify column types using the col_types parameter (e.g., col_types = cols(your_decimal_col = col_double()))—this is faster than scanning thousands of rows too.

内容的提问来源于stack exchange，提问作者sumanta das