将多行SQL查询导入单个字符串

问题描述

在 R 中，如何导入多行文本文件(包含SQL)的内容到一个字符串?

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

sql.txt文件如下所示:

The sql.txt file looks like this:

SELECT TOP 100
 setpoint,
 tph
FROM rates

我需要将该文本文件导入R字符串，使其看起来像这样:

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"

就是这样，这样我就可以将其馈送到RODBC

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

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

我已经尝试了以下readLines命令，但是它没有提供RODBC所需的字符串格式.

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

推荐答案

通用的paste()命令可以使用参数collapse=""来做到这一点:

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"

这篇关于将多行SQL查询导入单个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！