Clean Up Code And Keep Null Values From Crashing Read.csv.sql

I am using read.csv.sql to conditionally read in data (my data set is extremely large so this was the solution I chose to filter it and reduce it in size prior to reading the data

Solution 1:

There are several problems:

• some of the simplifications provided in the link in the question were not followed.
• spec is a scalar so one can just use '$spec'
• petwd is a numeric scalar and SQL does not require quotes around numbers so just use $petwd
• the question states you want to handle empty fields but not how so we have used csvfix to map them to -1 and also strip off quotes. (Alternately let them enter and do it in R. Empty numerics will come through as 0 and empty character fields will come through as zero length character fields.)
• you can use [...] in place of "..." in SQL

The code below worked for me in both Windows and Ubuntu Linux with the bash shell.

library(sqldf)

spec <- 'setosa'
petwd <- 0.2

d <- fn$read.csv.sql(
  "iris.csv", 
  sql = "select * from file where [Species] = '$spec' and [Petal.Width] = $petwd", 
  verbose = TRUE, 
  filter = 'csvfix map -smq -fv "" -tv -1'
)

Update

Regarding the update at the end of the question it was clarified that the NA could be in spec as opposed to being in the data being read in and that if spec is NA then the condition involving spec should be regarded as TRUE. In that case just expand the SQL where condition to handle that as follows.

spec <- NA
petwd <- 0.2

d <- fn$read.csv.sql(
  "iris.csv", 
  sql = "select * from file 
         where ('$spec' == 'NA' or [Species] = '$spec') and [Petal.Width] = $petwd", 
  verbose = TRUE, 
  filter = 'csvfix echo -smq'
)

The above will return all rows for which Petal.Width is 0.2 .