R Language: Import multiline SQL query to single string

Question

In R, how can I import the contents of a multiline text file (containing SQL) to a single string?

The sql.txt file looks like this:

SELECT TOP 100 
 setpoint, 
 tph 
FROM rates

I need to import that text file into an R string such that it looks like this:

> sqlString
[1] "SELECT TOP 100 setpoint, tph FROM rates"

That's so that I can feed it to the RODBC like this

> library(RODBC)
> myconn<-odbcConnect("RPM")
> results<-sqlQuery(myconn,sqlString)

I've tried the readLines command as follows but it doesn't give the string format that RODBC needs.

> filecon<-file("sql.txt","r")
> sqlString<-readLines(filecon, warn=FALSE)
> sqlString
[1] "SELECT TOP 100 "                              "\t[Reclaim Setpoint Mean (tph)] as setpoint, "
[3] "\t[Reclaim Rate Mean (tph)] as tphmean "       "FROM [Dampier_RC1P].[dbo].[Rates]"           
> 

Answer 1

try paste(sqlString, collapse=" ")

Answer 2

The versatile paste() command can do that with argument collapse="":

R> lines <- readLines("/tmp/sql.txt")
R> lines
[1] "SELECT TOP 100 " " setpoint, "     " tph "           "FROM rates"     
R> sqlcmd <- paste(lines, collapse="")
R> sqlcmd
[1] "SELECT TOP 100  setpoint,  tph FROM rates"
R> 

Answer 3

Here's the final version of what I'm using. Thanks Dirk.

fileconn<-file("sql.txt","r")           
sqlString<-readLines(fileconn)          
sqlString<-paste(sqlString,collapse="")
gsub("\t","", sqlString)
library(RODBC)
sqlconn<-odbcConnect("RPM")
results<-sqlQuery(sqlconn,sqlString)
library(qcc)
tph <- qcc(results$tphmean[1:50], type="xbar.one", ylim=c(4000,12000), std.dev=600)
close(fileconn)
close(sqlconn)

Answer 4

I use sql <- gsub("\n","",sql) and sql <- gsub("\t","",sql) together.