2

I'm using R library(RODBC) to import the results of a sql store procedure and save it in a data frame then export that data frame using write.table to write it to xml file (the results from sql is an xml output) anyhow, R is truncating the string (imported xml results from sql). I've tried to find a function or an option to expand the size/length of the R dataframe cell but didn't find any I also tried to use the sqlquery in the write.table statement to ignore using a dataframe but also it didn't work, the imported data from sql is always truncated. Anyone have any suggestions or an answer that could help me. here is my code

#library & starting the sql connection
library(RODBC)
my_conn<-odbcDriverConnect('Driver={SQL Server};server=sql2014;database=my_conn;trusted_connection=TRUE') 

#Create a folder and a path to save my output
x <- "C:/Users/ATM1/Documents/R/CSV/test"
dir.create(x, showWarnings=FALSE)
setwd(x)
Mpath <- getwd()

#importing the data from sql store procedure output
xmlcode1 <- sqlquery(my_conn, "exec dbo.p_webDefCreate 'SA25'", stringsAsFactors=F, as.is=TRUE)


#writing to a file
write.table(xmlcode1, file=paste0(Mpath,"/SA5b_def.xml"), quote = FALSE, col.names = FALSE, row.names = FALSE)

what I get is plain text that is not the full output. and the code below is how I find the current length of my string

stri_length(xmlcode1) [1] 65534

Abbas Mousa
  • 21
  • 3
  • Possible duplicate of [RODBC and Microsoft SQL Server: Truncating Long Character Strings](https://stackoverflow.com/questions/44249788/rodbc-and-microsoft-sql-server-truncating-long-character-strings) – Benjamin May 03 '18 at 18:08
  • Hi Benjamin, that question does not answer mine, my store procedure results is xml not a table that I can try and play with it's column sizes or whatever. that xml output is being read as a string and then saved to an xml file where it's supposed to be viewed as xml but it won't happen because its truncated. when I run the store procedure with a different tablename that has short result, everything runs smooth but not with the long result – Abbas Mousa May 03 '18 at 18:25
  • You're right. I hadn't realized that you weren't pulling the data out of a table. I'm afraid I don't have a good solution for you. Maybe try the `DBI` style interface? (I'm not sure how much good it will do, based on ODBC driver limitations) – Benjamin May 03 '18 at 18:50
  • unfortunately for me I can't use BDI, I'm only limited to what my agency has in term of R packages, we are not allowed to download any and they have some kind of procedure they go through before downloading any package. Do you know of a way where I can split the imported string in a half and save it in two dataframes because I can combine those two when I write to file – Abbas Mousa May 03 '18 at 18:59

2 Answers2

1

I had similar issue with our project, the data that was coming from the db was getting truncated to 257 characters, and I could not really get around it. Eventually I converted the column def on the db table from varchar(max) to varchar(8000) and I got all the characters back. I did not mind changing the table defintion.

In your case you can perhaps convert the column type in your proc output to varchar with some defined value if possible.

M

Michael Kassa
  • 141
  • 4
0

I am using PostgeSQL but experienced the same issue of truncation upon importing into R with RODBC package. I used Michael Kassa's solution with a slight change to set the data type to text which can store a string with unlimited length per postgresqltutorial. This worked for me.

The TEXT data type can store a string with unlimited length.

varchar() also worked for me

If you do not specify the n integer for the VARCHAR data type, it behaves like the TEXT datatype. The performance of the VARCHAR (without the size n) and TEXT are the same.

Sunnyvale_CF
  • 41
  • 2
  • 5