How to read the contents of a .sql file in an R script to run a query?

Question

I tried the readLines and read.csv , but then it didn’t work.

Here is the contents of my_script.sql file:

 SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees WHERE HireDate >= '1-july-1993' 

and it is saved on my desktop.

Now I want to run this request from my R script. Here is what I have:

 conn = connectDb() fileName <- "C:\\Users\\me\\Desktop\\my_script.sql" query <- readChar(fileName, file.info(fileName)$size) query <- gsub("\r", " ", query) query <- gsub("\n", " ", query) query <- gsub("", " ", query) recordSet <- dbSendQuery(conn, query) rate <- fetch(recordSet, n = -1) print(rate) disconnectDb(conn) 

And in this case, I get nothing. What can i try?

Answer 1

I had trouble reading the sql files myself, and I found that often when the syntax breaks, if sql has any comments on one line. Since in R you save the sql operator as a single-line string, if sql has double dashes, it will essentially comment on any code after the double dash.

This is the function that I usually use whenever I read the .sql file that will be used in R.

 getSQL <- function(filepath) { con = file(filepath, "r") sql.string <- "" while ( TRUE ) { line <- readLines(con, n = 1) if ( length(line) == 0 ) { break } line <- gsub("\\t", " ", line) if(grepl("--",line) == TRUE) { line <- paste(sub("--","/*",line),"*/") } sql.string <- paste(sql.string, line) } close(con) return(sql.string) } 

Answer 2

I found for queries with multiple lines, the read_file() function from the readr package works well. The only thing you should remember is to avoid single quotes (double quotes are ok). You can even add comments this way.

Sample query saved as query.sql

 SELECT COUNT(1) as "my_count" -- comment goes here FROM -- tabs work too my_table 

Then I can save the results in a data frame using

 df <- dbGetQuery(con, statement = read_file('query.sql'))