[R] How to building my own datafile

[Michael Bedward]

Hello Stephen,

Please reply to the list, not to me directly so you can get feedback
from others as well.

> Whether you suggested;
>
> RSQLite 0.9-2 on CRAN
> http://cran.r-project.org/web/packages/RSQLite/NEWS
>
> SQLite FTS3 Extension
> http://www.sqlite.org/fts3.html
>
> Package ‘RSQLite’
> July 24, 2010
> http://cran.r-project.org/web/packages/RSQLite/RSQLite.pdf

1 and 3 are the same package and refer to my suggestion. They will
allow you to work with an SQLite database from within R.

> I suppose it is possible creating a single file (simple database table) on R.

Here is an example session, writing a data.frame to a database,
retrieving it as a whole and also as a query...

library(RSQLite)
foo <- data.frame(id=1:10, name=paste("label", 1:10), value=runif(10))

drv <- dbDriver("SQLite")
con <- dbConnect(drv, "mydatabase.db")
dbWriteTable(con, "foo", foo)

dbListTables(con)
[1] "foo"

dbListFields(con, "foo")
[1] "row_names" "id"        "name"      "value"

dbReadTable(con, "foo")

   id     name      value
1   1  label 1 0.40306616
2   2  label 2 0.07662259
3   3  label 3 0.91400476
4   4  label 4 0.99851675
5   5  label 5 0.03186925
6   6  label 6 0.84866629
7   7  label 7 0.19167795
8   8  label 8 0.02639135
9   9  label 9 0.45611208
10 10 label 10 0.61527828

dbGetQuery(con, "select * from foo where value < 0.5")

  row_names id    name      value
1         1  1 label 1 0.40306616
2         2  2 label 2 0.07662259
3         5  5 label 5 0.03186925
4         7  7 label 7 0.19167795
5         8  8 label 8 0.02639135
6         9  9 label 9 0.45611208

> How to create multiple files with lot of input?  Any software to help?  Any GUI
> software in this respect?  TIA

As you can see from above it is not that hard. You can have many
tables in a single database file or work with separate files. Please
read the RSQLite docs for more info.

Michael