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]]

