Import multiline SQL query to single string

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开发者_StackOverflow社区 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]"           
> 

---

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

lines <- readLines("/tmp/sql.txt")
lines
[1] "SELECT TOP 100 " " setpoint, "     " tph "           "FROM rates"     

sqlcmd <- paste(lines, collapse="")
sqlcmd
[1] "SELECT TOP 100  setpoint,  tph FROM rates"

---

Below is an R function that reads in a multiline SQL query (from a text file) and converts it into a single-line string. The function removes formatting and whole-line comments.

To use it, run the code to define the functions, and your single-line string will be the result of running ONELINEQ("querytextfile.sql","~/path/to/thefile").

How it works: Inline comments detail this; it reads each line of the query and deletes (replaces with nothing) whatever isn't needed to write out a single-line version of the query (as asked for in the question). The result is a list of lines, some of which are blank and get filtered out; the last step is to paste this (unlisted) list together and return the single line.

#

# This set of functions allows us to read in formatted, commented SQL queries
# Comments must be entire-line comments, not on same line as SQL code, and begun with "--"
# The parsing function, to be applied to each line:
LINECLEAN <- function(x) {
  x = gsub("\t+", "", x, perl=TRUE); # remove all tabs
  x = gsub("^\\s+", "", x, perl=TRUE); # remove leading whitespace
  x = gsub("\\s+$", "", x, perl=TRUE); # remove trailing whitespace
  x = gsub("[ ]+", " ", x, perl=TRUE); # collapse multiple spaces to a single space
  x = gsub("^[--]+.*$", "", x, perl=TRUE); # destroy any comments
  return(x)
}
# PRETTYQUERY is the filename of your formatted query in quotes, eg "myquery.sql"
# DIRPATH is the path to that file, eg "~/Documents/queries"
ONELINEQ <- function(PRETTYQUERY,DIRPATH) { 
  A <- readLines(paste0(DIRPATH,"/",PRETTYQUERY)) # read in the query to a list of lines
  B <- lapply(A,LINECLEAN) # process each line
  C <- Filter(function(x) x != "",B) # remove blank and/or comment lines
  D <- paste(unlist(C),collapse=" ") # paste lines together into one-line string, spaces between.
  return(D)
}
# TODO: add eof newline automatically to remove warning
#############################################################################################

---

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)

---

This is what I use:

# Set Filename
fileName <- 'Input File.txt'

doSub <- function(src, dest_var_name, src_pattern, dest_pattern) {
    assign(
            x       = dest_var_name
        ,   value   = gsub(
                            pattern     = src_pattern
                        ,   replacement = dest_pattern
                        ,   x = src
                    )
        ,   envir   = .GlobalEnv
    )
}


# Read File Contents
original_text <- readChar(fileName, file.info(fileName)$size)

# Convert to UNIX line ending for ease of use
doSub(src = original_text, dest_var_name = 'unix_text', src_pattern = '\r\n', dest_pattern = '\n')

# Remove Block Comments
doSub(src = unix_text, dest_var_name = 'wo_bc_text', src_pattern = '/\\*.*?\\*/', dest_pattern = '')

# Remove Line Comments
doSub(src = wo_bc_text, dest_var_name = 'wo_bc_lc_text', src_pattern = '--.*?\n', dest_pattern = '')

# Remove Line Endings to get Flat Text
doSub(src = wo_bc_lc_text, dest_var_name = 'flat_text', src_pattern = '\n', dest_pattern = ' ')

# Remove Contiguous Spaces
doSub(src = flat_text, dest_var_name = 'clean_flat_text', src_pattern = ' +', dest_pattern = ' ')

---

try paste(sqlString, collapse=" ")

---

It's possible to use readChar() instead of readLines(). I had an ongoing issue with mixed commenting (-- or /* */) and this has always worked well for me.

sql <- readChar(path.to.file, file.size(path.to.file))
query <- sqlQuery(con, sql, stringsAsFactors = TRUE)

---

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