Search code examples
rhexodbcblob

Convert column of Hex into Text in R

I am new to R, please have mercy. I imported a table from an Access database via odbc:

df <- select(dbReadTable(accdb_path, name ="accdb_table"),"Col_1","Col_2","Col_3")

For

> typeof(df$Col_3)

I get

[1] "list"

Using library(dplyr.teradata). I converted blob to string (maybe already on the wrong path here):

df$Hex <- blob_to_string(df$Col_3)

and now end up with a column (typeof = character) full of Hex:

df[1,4] 

[1] 49206765742061206c6f74206f662048657820616e642068617665207468652069737375652077697468207370656369616c2063687261637465727320696e204765726d616e206c616e6775616765206c696b65206e2b4150592d7

My question is, how to convert each value in Col_3 into proper Text (if possible, with respect to German special chracters like ü,ö, ä and ß).

I am aware of this solution How to convert a hex string to text in R?, but can't apply it properly:

df$Text <- rawToChar(as.raw(strtoi(df$Hex, 16L)))

Fehler in rawToChar(as.raw(strtoi(BinData$Hex, 16L))) : 
  Zeichenkette '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\

Thx!

Solution

  • If I understand this correctly, what you want to do it to apply a function to each element of a list so that it returns a character vector (that you can add to a data frame, if you so wish).

    This can be easily accomplished with the purrr family of functions. The following takes each element df$Col_3 and runs the function (with each element being the x in the given function)

    purrr::map_chr(.x = df$Col_3,
               .f = function(x) {rawToChar(as.raw(strtoi(x,16L)))})

    You should probably achieve the same with base R functions such as lapply() followed by unlist(), or sapply() but with purrr it's often easier to find inconsistent results.