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

Question

I have tried the readLines and the read.csv functions but then don't work.

Here is the contents of the 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 query 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 I am not getting anything back in this case. What can I try?

Answer 1

I've had trouble with reading sql files myself, and have found that often times the syntax gets broken if there are any single line comments in the sql. Since in R you store the sql statement as a single line string, if there are any double dashes in the sql it will essentially comment out any code after the double dash.

This is a function that I typically use whenever I am reading in a .sql file to 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've found for queries with multiple lines, the read_file() function from the readr package works well. The only thing you have to be mindful of is to avoid single quotes (double quotes are fine). You can even add comments this way.

Example query, saved as query.sql

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

I can then store the results in a data frame with

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

Answer 3

You can use the read_file() function from the readr package.

fileName = read_file("C:/Users/me/Desktop/my_script.sql")

You will get a string variable fileName with the desired text.

Note: Use / instead of \\\

Answer 4

The answer by Matt Jewett is quite useful, but I wanted to add that I sometimes encounter the following warning when trying to read .sql files generated by sql server using that answer:

Warning message: In readLines(con, n = 1) : line 1 appears to contain an embedded nul

The first line returned by readLines is often "ÿþ" in these cases (i.e. the UTF-16 byte order mark) and subsequent lines are not read properly. I solved this by opening the sql file in Microsoft SQL Server Management Studio and selecting

File -> Save As ...

then on the small downarrow next to the save button selecting

Save with Encoding ...

and choosing

Unicode (UTF-8 without signature) - Codepage 65001

from the Encoding dropdown menu.

If you do not have Microsoft SQL Server Management Studio and are using a Windows machine, you could also try opening the file with the default text editor and then selecting

File -> Save As ...

Encoding: UTF-8

to save with a .txt file extension.

Interestingly changing the file within Microsoft SQL Server Management Studio removes the BOM (byte order mark) altogether, whereas changing the file within the text editor converts the BOM to the UTF-8 BOM but nevertheless causes the query to be properly read using the referenced answer.

Answer 5

The combination of readr and textclean works well without having to create any new functions. read_file() reads the file into a character vector and replace_white() ensures all escape sequence characters are removed from your .sql file. Note: Does cause problems if you have comments in your SQL string !!

library(readr)
library(textclean)

SQL <- replace_white(read_file("file_path")))

Answer 6

Late to the game but I've been playing around this today and written a slightly different approach to gathering SQL code to run within DBI::dbGetQuery() function:

get_sql_code_from_file <- function(file_path){

   # Get contents of file
   file_lines <- readLines(file_path)

   # Remove single line comments
   file_lines <- file_lines[grepl(pattern = "^--", file_lines) == FALSE]

   # Note indices of lines with multi-line comments start/ends
   multi_line_start_indices <- grep(pattern = "^/\\*", file_lines)
   multi_line_end_indices <- grep(pattern = "\\*/", file_lines)

   # Check if any multi-line comments found
   if (length(multi_line_end_indices) != 0) {

      # Note lines defining and within multi-line comments
      multi_line_indices <- sapply(
         seq_along(multi_line_start_indices),
         FUN = function(index, multi_line_start_indices, multi_line_end_indices){
            return(multi_line_start_indices[index]:multi_line_end_indices[index])
         },
         multi_line_start_indices, multi_line_end_indices
      )

      # Remove multi-line_comments
      file_lines <- file_lines[-multi_line_indices]
   }

   # Combine into single string
   file_string <- paste(file_lines, collapse = "\n")

   return(file_string)
}

This approach differs from the accepted answer as it removes both single and multi-line comments from the file.