CODEWITHSUNDEEP
rstringdataframetruncaterodbc
Abbas Mousa
Abbas Mousa

Reputation: 21

using RODBC sqlquery in R to import long string, but R truncate the string, how to get around this?

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

Upvotes: 2

Views: 1555

Answers (2)

Sunnyvale_CF
Sunnyvale_CF

Reputation: 41

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.

Upvotes: 0

Michael Kassa
Michael Kassa

Reputation: 151

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

Upvotes: 1

Related Questions