Dear r helpers,
At first, I apologize for raising a query which seems to be a stupid
interpretation on my part. I am trying to learn SQLite.
Following is an example given in the RSQLite.zip file (Page # 4)
drv <- dbDriver("SQLite")
tfile <- tempfile()
con <- dbConnect(drv, dbname = tfile)
data(USArrests)
dbWriteTable(con, "arrests", USArrests)
On the similar line I am trying to read my data.
Suppose I have a dataframe as given below.
DF = data.frame(X = c("US", "UK", "Canada",
"Australia", "Newzealand"), Y = c(52, 36, 74, 10, 98))
drv <- dbDriver("SQLite")
tfile <- tempfile()
con <- dbConnect(drv, dbname = tfile)
data(DF)
dbWriteTable(con, ......., .......) # Didn't know what to write here.
I understand I have raised a query in a stupid manner. I need to understand is
there any way I can use SQLite to read
dataframe or for that matter any csv file say e.g. 'DF.csv'.
Please enlighten me.
Amy
[[alternative HTML version deleted]]
Hi Amy,
I'm not sure if I understand your question correctly so let me know if
the following is off track.
Starting with your example, here is how to create a data.frame and
write it to a new table in a new database file...
my.data = data.frame(X = c("US", "UK", "Canada",
"Australia",
"Newzealand"), Y = c(52, 36, 74, 10, 98))
drv <- dbDriver("SQLite")
con <- dbConnect(drv, "myfilename.db")
dbWriteTable(con, "sometablename", my.data)
To verify that the table is now in the file...
dbListTables(con)
To check the fields in the table (should match the colnames in your
data.frame)...
dbListFields(con, "sometablename")
To read the whole table into the workspace as a new data.frame
my.data.copy <- dbReadTable(con, "sometablename")
If you have data in a CSV file, and the contents are small enough to
read in one go, you would use the read.csv function to read the
contents of the file into a data.frame and then use dbWriteTable to
transfer this to your database.
Hope this helps,
Michael
On 4 January 2011 21:43, Amy Milano <milano_amy at yahoo.com>
wrote:> Dear r helpers,
>
> At first, I apologize for raising a query which seems to be a stupid
interpretation on my part. I am trying to learn SQLite.
>
>
>
> Following is an example given in the RSQLite.zip file (Page # 4)
>
> drv <- dbDriver("SQLite")
> tfile <- tempfile()
> con <- dbConnect(drv, dbname = tfile)
> data(USArrests)
> dbWriteTable(con, "arrests", USArrests)
>
>
> On the similar line I am trying to read my data.
>
> Suppose I have a dataframe as given below.
>
> DF = data.frame(X = c("US", "UK", "Canada",
"Australia", "Newzealand"), Y = c(52, 36, 74, 10, 98))
>
> drv <- dbDriver("SQLite")
> tfile <- tempfile()
> con <- dbConnect(drv, dbname = tfile)
> data(DF)
> dbWriteTable(con, ......., .......) # Didn't know what to write here.
>
> I understand I have raised a query in a stupid manner. I need to understand
is there any way I can use SQLite to read
> ?dataframe or for that matter any csv file say e.g. 'DF.csv'.
>
> Please enlighten me.
>
> Amy
>
>
>
> ? ? ? ?[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
Hi Amy,> Suppose I have a dataframe as given below. > > DF = data.frame(X = c("US", "UK", "Canada", "Australia", "Newzealand"), Y = c(52, 36, 74, 10, 98)) > > drv <- dbDriver("SQLite") > tfile <- tempfile() > con <- dbConnect(drv, dbname = tfile) > data(DF) > dbWriteTable(con, ......., .......) # Didn't know what to write here. >data() loads data sets which ship with R or an extension package. Your data frame DF is already in the working environment, hence it is neither possible nor necessary to "load" it, the expression data(DF) is an error. The command to write DF to a database would be something like dbWriteTable(con, "DF", DF) where the second argument is the name of the database table to write to, the third argument the data frame you want to write (please have a look at the documentation).> I understand I have raised a query in a stupid manner. I need to understand is there any way I can use SQLite to read > dataframe or for that matter any csv file say e.g. 'DF.csv'.To copy a CSV file to SQLite, read it via read.table() or read.csv() first, then copy the result with dbWriteTable. There might be a way to read files directly from SQLite, but I don't know about that. Best regards, Andreas> >-- Andreas Borg Medizinische Informatik UNIVERSIT?TSMEDIZIN der Johannes Gutenberg-Universit?t Institut f?r Medizinische Biometrie, Epidemiologie und Informatik Obere Zahlbacher Stra?e 69, 55131 Mainz www.imbei.uni-mainz.de Telefon +49 (0) 6131 175062 E-Mail: borg at imbei.uni-mainz.de Diese E-Mail enth?lt vertrauliche und/oder rechtlich gesch?tzte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrt?mlich erhalten haben, informieren Sie bitte sofort den Absender und l?schen Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail und der darin enthaltenen Informationen ist nicht gestattet.