1

Dumb question, but how do I convert data that is stored in blob format to a human readable format? My data is stored in a .sqlite file. Many of the fields are stored in blob[##, B] format when viewing in R (I can also use Python if you know a solution that way btw)

When I query my data I get responses like the following if I use HEX function around the column...

789C55CF310EC2300C05D0AB449E3B244D1AC7CCB02171...

When I query my data without the HEX function I get

b"x\x9cU\xcf1\x0e\xc20\x0c\x05\xd0\xabX\x9e;\x...

I'm expecting something like '2018' or'Canada' in my columns but instead I get all this nonsense.

library(RSQLite)

conn = RSQLite::dbConnect(RSQLite::SQLite(), '/Users/me/countries/database.sqlite')

dbGetQuery(conn, "select * from countries limit 10")
runningbirds
  • 6,235
  • 13
  • 55
  • 94
  • Does this help: [How to convert a hex string to text in R?](https://stackoverflow.com/a/29252469/1422451) – Parfait Sep 22 '20 at 21:08
  • the `b"x\x0c...` looks like a python string, is that right? – r2evans Sep 26 '20 at 20:35
  • runningbirds, are you able to provide something reproducible? For instance, do you know what *that* hex string should render, specifically? It might be useful to have a known round-trip data-in/hex-out or blob-out. – r2evans Sep 26 '20 at 20:52

1 Answers1

0

I was also facing the same issue and found a solution in python for this. We are not able to decode these values because these values are in compressed format, so before performing de-code we should perform decompress operation like below.

import zlib
strs = b'x\x9c\x8bV\xf2\xcd\xcfO\xa9T\xd2QPrL\xceM,R\x8a\x05\x003\xf0\x05y'
zlib.decompress(strs).decode("utf-8")

'["Moody", "Acmar"]'
Kalaivanan Natarajan
  • 1