As question, I found that I can use .import
in sqlite shell, but seems it is not working in R environment, any suggestions?
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With