Alex Rivera | Logout

Import multiline SQL query to single string

Asked 2010-01-05T01:51:01.390
17

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]"           
> 
Edit
Report

1 Answer

0

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

answered 2010-01-05T03:24:57.970

Your Answer