如何使用 RSqlite 将 CSV 导入 sqlite? [英] How to import CSV into sqlite using RSqlite?

问题描述

作为问题，我发现我可以在 sqlite shell 中使用 .import，但似乎它在 R 环境中不起作用，有什么建议吗?

As question, I found that I can use .import in sqlite shell, but seems it is not working in R environment, any suggestions?

推荐答案

您可以使用 sqldf 包中的 read.csv.sql.只需一行代码即可完成读取.假设您要创建一个新数据库 testingdb，然后将文件读入其中，请尝试以下操作:

You can use read.csv.sql in the sqldf package. It is only one line of code to do the read. Assuming you want to create a new database, testingdb, and then read a file into it try this:

# create a test file
write.table(iris, "iris.csv", sep = ",", quote = FALSE, row.names = FALSE)

# create an empty database.
# can skip this step if database already exists.
sqldf("attach testingdb as new")
# or: cat(file = "testingdb")

# read into table called iris in the testingdb sqlite database
library(sqldf)
read.csv.sql("iris.csv", sql = "create table main.iris as select * from file", 
  dbname = "testingdb")

# look at first three lines
sqldf("select * from main.iris limit 3", dbname = "testingdb")

上面使用了使用 RSQLite 的 sqldf.您也可以直接使用 RSQLite.参见 RSQLite 中的 ?dbWriteTable.请注意，如果您直接使用 dbWriteTable 执行此操作，sqldf 将自动处理(通常)，则行尾可能会出现问题.

The above uses sqldf which uses RSQLite. You can also use RSQLite directly. See ?dbWriteTable in RSQLite. Note that there can be problems with line endings if you do it directly with dbWriteTable that sqldf will automatically handle (usually).

如果您打算在将文件读入数据库后立即将其读入 R 并且之后您真的不需要数据库，那么请参阅:

If your intention was to read the file into R immediately after reading it into the database and you don't really need the database after that then see:

http://code.google.com/p/sqldf/#Example_13._read.csv.sql_and_read.csv2.sql

这篇关于如何使用 RSqlite 将 CSV 导入 sqlite?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！