CODEWITHSUNDEEP
rrodbcdremio
hemant A
hemant A

Reputation: 185

How to convert raw vector from sqlQuery to a single string in dataframe?

In my workplace, R is integrated with Dremio and I can access the tables in Dremio using RODBC::sqlQuery() function. Tables are having around hundred thousand entries. I am creating data-frame of SQL tables using this function but some rows are storing data in different format. I have read that sqlQuery function converts the binary SQL types to list of raw vectors, hence I am getting cell value like this:

as.raw(c(0x56, 0xfd, 0x36, 0xaf, 0x63, 0x18, 0x77, 0x2a, 0x09, 0x35, 0x9f, 0xe3))

dput(df)
structure(list(`_Id` = structure(list(as.raw(c(0x56, 0xfd, 0x36, 
0xaf, 0x63, 0x18, 0x77, 0x2a, 0x09, 0x35, 0x9f, 0xe3)), as.raw(c(0x56, 
0xfd, 0x37, 0x07, 0x63, 0x18, 0x77, 0x2a, 0x09, 0x35, 0x9f, 0xeb
)), as.raw(c(0x56, 0xfd, 0x38, 0x7a, 0x63, 0x18, 0x77, 0x2a, 
0x09, 0x35, 0xa0, 0x07)), as.raw(c(0x56, 0xfd, 0x38, 0x8b, 0x63, 
0x18, 0x77, 0x2a, 0x09, 0x35, 0xa0, 0x0e)), as.raw(c(0x56, 0xfd, 
0x38, 0x9d, 0x63, 0x18, 0x77, 0x2a, 0x09, 0x35, 0xa0, 0x12))), class = "ODBC_binary")), class = "data.frame", row.names = c(NA, 
5L))

I want cell value to be a single string id as "56fd36af6318772a09359fe3" so that I can use it as matching criteria with other tables. Is there any way to do it?

Upvotes: 0

Views: 437

Answers (1)

stevec
stevec

Reputation: 52308

You can use dplyr 


library(dplyr)

df$`_Id` %>% 
  sapply(., function(x) { paste0(x, collapse = "") }) %>% 
  as.data.frame(string_id=as.character(.), stringsAsFactors=FALSE) 

# 1 56fd36af6318772a09359fe3
# 2 56fd37076318772a09359feb
# 3 56fd387a6318772a0935a007
# 4 56fd388b6318772a0935a00e
# 5 56fd389d6318772a0935a012

Upvotes: 0

Related Questions