R help - Nov 2012 - read.csv.sql() to select from a large csv file

Dear list,
 
Dear list,
I am using read.csv.sql() from the sqldf package to read
individual-based data from a csv file that is too large for R. 
My original file contains a column called “ID” that
identifies the individual. I would like to read in data for only 
one individual at a
time, for example "Bobby". 
read.csv.sql("filename",sql = 'select * from file where ID =
“Bobby”') works fine, but
Name <- "Bobby"
read.csv.sql("filename",sql = 'select * from file
where ID = Name') gives me an error message 
 “Error in sqliteExecStatement(con, statement,
bind.data) : 
RS-DBI driver: (error in statement: no such column: Name)”
 
I would like to use a variable in the select statement because I have many
individuals in the original csv file, is this possible ? I have no experience
with sql, perhaps someone can kindly point out how to do this correctly or offer
an alternative solution ?
Thank you very much and happy Friday,
Juliane 
Dr. Juliane Struve
Fisheries and Aquatic Sciences
7922 NW 71st Street, Gainesville, FL 32653
352-273-3632 (tel)
352-392-3672 (fax)
email:mailto:jstruve@ufl.edu
	[[alternative HTML version deleted]]

On Fri, Nov 23, 2012 at 11:12 AM, Juliane Struve
<juliane_struve at yahoo.co.uk> wrote:
> Dear list,
>
> Dear list,
> I am using read.csv.sql() from the sqldf package to read
> individual-based data from a csv file that is too large for R.
> My original file contains a column called ?ID? that
> identifies the individual. I would like to read in data for only
> one individual at a
> time, for example "Bobby".
> read.csv.sql("filename",sql = 'select * from file where ID =
?Bobby?') works fine, but
> Name <- "Bobby"
> read.csv.sql("filename",sql = 'select * from file
> where ID = Name') gives me an error message
>  ?Error in sqliteExecStatement(con, statement,
> bind.data) :
> RS-DBI driver: (error in statement: no such column: Name)?
>
> I would like to use a variable in the select statement because I have many
individuals in the original csv file, is this possible ? I have no experience
with sql, perhaps someone can kindly point out how to do this correctly or offer
an alternative solution ?
See Example 5 on the sqldf home page:

https://code.google.com/p/sqldf/#Example_5._Insert_Variables


--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

you need to construct the character string.  here's what you want to give
to the sql argument --

paste( 'select * from file where ID = ' , Name )

so try

Name <- "Bobby"
read.csv.sql("filename",sql = paste( 'select * from file where ID
= ' ,
Name ) )


On Fri, Nov 23, 2012 at 11:12 AM, Juliane Struve
<juliane_struve@yahoo.co.uk
> wrote:
> Dear list,
>
> Dear list,
> I am using read.csv.sql() from the sqldf package to read
> individual-based data from a csv file that is too large for R.
> My original file contains a column called “ID” that
> identifies the individual. I would like to read in data for only
> one individual at a
> time, for example "Bobby".
> read.csv.sql("filename",sql = 'select * from file where ID =
“Bobby”')
> works fine, but
> Name <- "Bobby"
> read.csv.sql("filename",sql = 'select * from file
> where ID = Name') gives me an error message
>  “Error in sqliteExecStatement(con, statement,
> bind.data) :
> RS-DBI driver: (error in statement: no such column: Name)”
>
> I would like to use a variable in the select statement because I have many
> individuals in the original csv file, is this possible ? I have no
> experience with sql, perhaps someone can kindly point out how to do this
> correctly or offer an alternative solution ?
> Thank you very much and happy Friday,
> Juliane
> Dr. Juliane Struve
> Fisheries and Aquatic Sciences
> 7922 NW 71st Street, Gainesville, FL 32653
> 352-273-3632 (tel)
> 352-392-3672 (fax)
> email:mailto:jstruve@ufl.edu
>         [[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.
>
>
	[[alternative HTML version deleted]]

Hello,

Try forming the sql statement with paste().

Name <- "Bobby"
sqlstatement <- paste("select * from file where ID = '", Name,
"'", sep
= "")
read.csv.sql("filename", sql = sqlstatement)

Note the opening and closing quotes around the name.

Hope this helps,

Rui Barradas
Em 23-11-2012 16:12, Juliane Struve escreveu:
> Dear list,
> Â
> Dear list,
> I am using read.csv.sql() from the sqldf package to read
> individual-based data from a csv file that is too large for R.
> My original file contains a column called âEURoeIDâEUR? that
> identifies the individual. I would like to read in data for only
> one individual at a
> time, for example "Bobby".
> read.csv.sql("filename",sql = 'select * from file where ID =
âEURoeBobbyâEUR?') works fine, but
> Name <- "Bobby"
> read.csv.sql("filename",sql = 'select * from file
> where ID = Name') gives me an error message
> Â âEURoeError in sqliteExecStatement(con, statement,
> bind.data) :
> RS-DBI driver: (error in statement: no such column: Name)âEUR?
> Â
> I would like to use a variable in the select statement because I have many
individuals in the original csv file, is this possible ? I have no experience
with sql, perhaps someone can kindly point out how to do this correctly or offer
an alternative solution ?
> Thank you very much and happy Friday,
> Juliane
> Dr. Juliane Struve
> Fisheries and Aquatic Sciences
> 7922 NW 71st Street, Gainesville, FL 32653
> 352-273-3632 (tel)
> 352-392-3672 (fax)
> email:mailto:jstruve@ufl.edu
> 	[[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.

	[[alternative HTML version deleted]]