r - 使用具有引用值的 RSQLite 加载数据

Question

我正在尝试使用 RSQLite 包(我也尝试过 sqldf 包)将一个大的 csv 文件加载到 SQL lite 数据库中。该文件包含所有英国邮政编码和它们的各种查找值。

我想避免将其加载到 R 中，而直接将其加载到数据库中。虽然这对于这项任务并不是绝对必要的，但我想这样做是为了让该技术为将来必须处理的较大文件做好准备，这些文件将不适合内存。

不幸的是，csv 提供了双引号中的值，而 dbWriteTable 函数似乎无法删除它们或以任何形式忽略它们。这是文件的下载位置:http://ons.maps.arcgis.com/home/item.html?id=3548d835cff740de83b527429fe23ee0

这是我的代码:

# Load library
library("RSQLite")

# Create a temporary directory
tmpdir <- tempdir()

# Set the file name
file <- "data\\ONSPD_MAY_2017_UK.zip"

# Unzip the ONS Postcode Data file
unzip(file, exdir = tmpdir )

# Create a path pointing at the unzipped csv file
ONSPD_path <- paste0(tmpdir,"\\ONSPD_MAY_2017_UK.csv")

# Create a SQL Lite database connection
db_connection <- dbConnect(SQLite(), dbname="ons_lkp_db")

# Now load the data into our SQL lite database
dbWriteTable(conn = db_connection,
              name = "ONS_PD",
              value = ONSPD_path,
              row.names = FALSE,
              header = TRUE,
              overwrite = TRUE
              )

# Check the data upload
dbListTables(db_connection)
dbGetQuery(db_connection,"SELECT pcd, pcd2, pcds from ONS_PD  LIMIT 20")

遇到这个问题后，我找到了推荐使用 sqldf 包的引用教程 ( https://www.r-bloggers.com/r-and-sqlite-part-1/)，但不幸的是，当我尝试在 sqldf (read.csv.sql) 中使用相关函数时，我遇到了同样的双引号问题。

将 csv 文件导入 sql 系统时，这感觉是一个相当普遍的问题，大多数导入工具都能够处理双引号，所以我很惊讶遇到这个问题(除非我错过了关于这个问题的明显帮助文件沿途某处)。

编辑 1

以下是我的 csv 文件中的一些示例数据，格式为 SQL 表的 dput 输出:

structure(list(pcd = c("\"AB1 0AA\"", "\"AB1 0AB\"", "\"AB1 0AD\"", 
"\"AB1 0AE\"", "\"AB1 0AF\""), pcd2 = c("\"AB1  0AA\"", "\"AB1  0AB\"", 
"\"AB1  0AD\"", "\"AB1  0AE\"", "\"AB1  0AF\""), pcds = c("\"AB1 0AA\"", 
"\"AB1 0AB\"", "\"AB1 0AD\"", "\"AB1 0AE\"", "\"AB1 0AF\"")), .Names = c("pcd", 
"pcd2", "pcds"), class = "data.frame", row.names = c(NA, -5L))

编辑 2

这是我在 sqldf 的 read.csv.sql 函数中使用 filter 参数的尝试(注意，Windows 用户需要为此安装 rtools)。不幸的是，这似乎仍然没有从我的数据中删除引号，尽管它确实神秘地删除了所有空格。

library("sqldf")
sqldf("attach 'ons_lkp_db' as new")
db_connection <- dbConnect(SQLite(), dbname="ons_lkp_db")
read.csv.sql(ONSPD_path,
              sql = "CREATE TABLE ONS_PD AS SELECT * FROM file",
              dbname = "ons_lkp_db",
              filter = 'tr.exe -d ^"'
              )

dbGetQuery(db_connection,"SELECT pcd, pcd2, pcds from ONS_PD  LIMIT 5")

另外，感谢任何认为这不是 Stack Overflow 范围内的编程问题的人的密切投票(？!)。

Answer 1

RSQLite 中的 CSV 导入器包派生自 sqlite3 shell，它在导入 CSV 文件( How to import load a .sql or .csv file into SQLite? ， doc )时似乎不支持引用值。您可以使用 readr::read_delim_chunked() :

callback <- function(data) {
  name <- "ONS_PD"
  exists <- dbExistsTable(con, name)
  dbWriteTable(con, name, data, append = exists)
}

readr::read_delim_chunked(ONSPD_path, callback, ...)

替补 ...包含 CSV 文件所需的任何额外参数。