Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to import CSV into sqlite using RSqlite?

Tags:

import

sqlite

r

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

like image 436
lokheart Avatar asked Dec 02 '10 08:12

lokheart


People also ask

How do I import a CSV file into SQLite?

First, from the menu choose tool menu item. Second, choose the database and table that you want to import data then click the Next button. Third, choose CSV as the data source type, choose the CSV file in the Input file field, and choose the ,(comma) option as the Field separator as shown in the picture below.

Which command is used to import CSV into sqlite3?

You can import a CSV file into SQLite table by using sqlite3 tool and . import command. This command accepts a file name, and a table name.


1 Answers

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

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

like image 97
G. Grothendieck Avatar answered Sep 20 '22 02:09

G. Grothendieck