R Server working with flat file paths

Question

I am trying to load a flat file .csv using fread function in a stored procedure in R (in-database) Server.

Apparently, R Server (in the background) tries to copy this file to a working directory in C:\, but this fails.

First, there is the issue that R cannot read \, but Windows file paths require \. I seem to have overcome this issue (I think), but am not sure.

Has anyone worked with loading flat files from a file path into the R Server engine?

Here is the code:

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[usp_R_FileMap]    Script Date: 7/9/2018 11:25:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[usp_R_FileMap]
(
@FilePath nvarchar(255),
@FileName nvarchar(255),
@FileExtension nvarchar(10)
)
AS

declare @RCode nvarchar(max)

set @FilePath = replace(@FilePath,'\','\\')

set @RCode = N'


##PREPARE ENVIRONMENT##########################################################

usrInputFilePath <- "' + @FilePath + N'"
usrInputFileName <- "' + @FileName + N'"
usrInputFileType <- "' + @FileExtension + N'"

sysPackages <- c("data.table"
                 ,"foreach"
                 ,"reader"
                 ,"stringr"
)

##install / load packages as necessary
for (ii in 1:length(sysPackages)) {
  if (paste0("package:",sysPackages[ii]) %in% search()==F) {library(sysPackages[ii],character.only=T)}##if (!paste0("package:",sysPackages[ii]) %in% search())
}##for (ii in length(sysPackages))


##determine full file paths
vcInputFile <- paste0(usrInputFilePath,ifelse(substring(usrInputFilePath,nchar(usrInputFilePath),nchar(usrInputFilePath))!="\\","\\",""),usrInputFileName,usrInputFileType)
# vcInputFile <- file.path(usrInputFilePath,paste0(ifelse(substring(usrInputFilePath,nchar(usrInputFilePath),nchar(usrInputFilePath))!="\\","\\",""),usrInputFileName,usrInputFileType))




##DETERMINE DELIMITER##########################################################


  vcInputFileDelimiter <- gsub("(Detecting sep ... '')","",grep("Detecting sep",capture.output(fread(vcInputFile,nrows=1,stringsAsFactors=F,verbose=T)),value=T))



print(vcInputFileDelimiter)

'


EXEC sp_execute_external_script
  @language =N'R',
  @script=@RCode;


GO

score 2 · Accepted Answer · answered Jul 10 '18 at 19:03

Beyond the \\ vs \ problem, if you're trying to read a file that's on a network share you're going to be blocked by a Windows firewall rule that was installed with SQL Server R Services.

If you really want to pull files off the network share from R Services you can disable this rule (Start -> Windows Firewall with Advanced Security -> Outbound Rules -> Block network access for R local user accounts in SQL Server instance MSSQLSERVER -> Right click -> Disable).

Additionally, R processes run under a local computer account, something like MSSQLSERVER01, and these users would need permissions to access the file on the network share.

score 0 · Answer 2 · answered Jul 10 '18 at 02:16

Not seeing the error does make it harder to help. Anyhow, where does the file you want to load reside? It has to be where SQL Server can read it. As @Jiggles32 says, \\ should work, otherwise try with /.

I did a blog post related to loading script files here: http://www.nielsberglund.com/2018/03/07/microsoft-sql-server-r-services-sp_execute_external_script-I/

Let us know how it goes.

score -1 · Answer 3 · answered Jul 09 '18 at 19:09

-1

In R, you escape the ("backslash character") \ with \\.

This applies to strings within R, and is working as expected.

answered Jul 09 '18 at 19:09

ccarpenter32

1,058
2
9
17

fread("\\\\FILEPATH\\FILEPATH\\FILENAME.txt") Does NOT work. It says that the "1: running command 'C:\Windows\system32\cmd.exe /c (\\FILEPATH\FILEPATH\FILENAME.txt) > C:\PROGRA~1\MICROS~1\MSSQL1~1.MSS\MSSQL\EXTENS~1\MSSQLSERVER01\B8A3E23D-937B-4445-BA96-6941E2EED9AA\RtmpohMmsn\file2fb85fbd34e8' had status 1" Notice that the escape seems to work, but the file process breaks. – Pablo Boswell Jul 09 '18 at 19:13

R Server working with flat file paths

3 Answers3