Dear folks--
I am not sure if this should be framed as a question about RSQLite, about
debugging, about SQLite, or about how to write a good question. I have a
little function (copied below beneath the error messages along with my
data), that is supposed to read a csv file and write it out to SQLite. I
can not even begin to figure out how to write a minimal reproducible
example I do not even know whether the RS-DBI driver is part of R, of
RSQLite, of SQLite, or is its own separate piece of software.
When I ran my functiont, like so:
> ImportRSQLite("C:\\R_PROJ\\INEQ_TRENDS\\TESTS\\minifile\\cps_00077.csv",
+ sep=",", DBname_c="TX1", yourTable_c="mini01", vars_L=vars_L,
+ indexName_c="IND01", indexVars_C=ndxs01)
I get this error message:
Error in sqliteExecStatement(con, statement, bind.data) :
RS-DBI driver: (error in statement: unknown database TX1)
Then I thought maybe it was not looking in my R working directory, so I ran
it again with a full path name, like this:
> ImportRSQLite("C:\\R_PROJ\\INEQ_TRENDS\\TESTS\\minifile\\cps_00077.csv",
+ sep=",", DBname_c="C:\\R_PROJ\\INEQ_TRENDS\\TESTS\\TX1",
yourTable_c="mini01", vars_L=vars_L, + indexName_c="IND01",
indexVars_C=ndxs01)
And got this error message, which seems to simultaneously contradict that
it is a wrong directory problem and also say that it can find the database
after all:
Error in sqliteExecStatement(con, statement, bind.data) :
RS-DBI driver: (error in statement: unrecognized token: ":")
In addition: Warning message:
In sqliteImportFile(conn, name, value, ...) :
table mini01 exists in database: aborting dbWriteTable
When I go to my R_PROJ\INEQ_TRENDS\TESTS directory, I see a file of the
name and size I expect if the database were written correctly.
Any help anyone could offer would be much appreciated.
Warmest regards, andrewH
###############################################
ImportRSQLite <- function(yourFileName_c, sep=",", DBname_c, yourTable_c,
vars_L, indexName_c=NULL, indexVars_C){
# ImportRSQLite takes: the file name (if in your working directory) or file
& path (if elsewhere) of your
# csv data file; a list containing the column names & optional datatypes; a
database name; a table name
# for the data; & creates an SQLite database with a table containing your
data.
# Make DB
require("RSQLite")
db <- dbConnect(SQLite(), dbname=DBname_c) ## Will make DB, if not
present
# Write file to table # Note: SQLite command is CREATE TABLE.
dbWriteTable(con=db, name=yourTable_c, value=yourFileName_c, sep=sep,
row.names=FALSE, header=TRUE, field.types=vars_L)
# Add indexing if desired
if (!is.null(indexName_c)){
command <-paste("CREATE INDEX IF NOT EXISTS ", DBname_c, ".",
indexName_c, " ON ", yourTable_c, " (", indexVars_C, ")",
sep='')
}
dbGetQuery(db, command)
dbDisconnect(db)
}
And here is my data:
###############################################
# TEST FOR ImportRSQLite
###############################################
vars_L <- list(YEAR="INTEGER",
SERIAL="INTEGER",
HWTSUPP="REAL",
STATEFIP="INTEGER",
MONTH="INTEGER",
PERNUM="INTEGER",
WTSUPP="REAL",
FAMSIZE="INTEGER",
AGE="INTEGER",
RACE="INTEGER",
FTOTVAL="REAL")
ndxs01 <- c("HWTSUPP", "RACE")
And the data in the file I am importing looks like this, but longer:
"YEAR","SERIAL","HWTSUPP","STATEFIP","MONTH","PERNUM","WTSUPP","FAMSIZE","AGE","RACE","FTOTVAL"
2001,6879,196.86,44,3,1,196.86,3,35,100,67010
2001,6931,413.27,44,3,2,413.27,1,35,100,10216
--
J. Andrew Hoerner
Director, Sustainable Economics Program
Redefining Progress
(510) 507-4820
[[alternative HTML version deleted]]
______________________________________________
R-help@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.
