how to import CSV into sqlite using RSqlite?

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

Thanks!

Answers


You can use read.csv.sql in the sqldf package. Its 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")

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).

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


Need Your Help

Mean along 4th dimension of 4-D matrix

arrays matlab matrix mean

I have 3-D spectrograms (5x3x129) per subject. I have 25 subjects.

Cloned node isn't equal to original node (with isEqualNode)

javascript html dom

I'm managing a list of names in Javascript. When you check a box, your name appears in the list. When you uncheck it, it gets crossed out. And when you set the box to the indeterminate state, your ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.