Using read.csv.sql to select multiple values ​​from a single column

Question

I use read.csv.sql from the read.csv.sql package to try and read in a subset of the rows where the subset selects from several values ​​- these values ​​are stored in another vector.

I have hacked a way to a form that works, but I would like to see the correct way to pass the sql statement.

Below is an example below.

 library(sqldf) # some data write.csv(mtcars, "mtcars.csv", quote = FALSE, row.names = FALSE) # values to select from variable 'carb' cc <- c(1, 2) # This only selects last value from 'cc' vector read.csv.sql("mtcars.csv", sql = paste("select * from file where carb = ", cc )) # So try using the 'in' operator - this works read.csv.sql("mtcars.csv", sql = "select * from file where carb in (1,2)" ) # but this doesn't read.csv.sql("mtcars.csv", sql = paste("select * from file where carb in ", cc )) # Finally this works read.csv.sql("mtcars.csv", sql = paste("select * from file where carb in ", paste("(", paste(cc, collapse=",") ,")"))) 

The last line above works, but is there an easier way to convey this statement, thanks.

Answer 1

1) fn $ Replacement can be done using fn$ gsubfn (which automatically pulls sqldf). See fn$ Examples on the sqldf homepage . In this case we have:

 fn$read.csv.sql("mtcars.csv", sql = "select * from file where carb in ( `toString(cc)` )") 

2) join . Another approach would be to create the required carb data format and connect to it:

 Carbs <- data.frame(carb = cc) read.csv.sql("mtcars.csv", sql = "select * from Carbs join file using (carb)") 

Answer 2

You can use deparse , but I'm not sure if it is much cleaner than you already have:

 read.csv.sql("mtcars.csv", sql = paste("select * from file where carb in ", gsub("c","",deparse(cc)) )) 

And note that this is not a general solution, because deparse will not always give you the correct character string. It just happens in this case.