topic Cast from Blob to text in Viya in SAS Programming

Cast from Blob to text in Viya

heffo — Wed, 13 May 2020 02:01:25 GMT

Hi,

I have an SQL server that has documents (Word, Excel etc) that are saved as blobs. I now want to convert the data as text (to be used in Visual Text Analytics). I have tried a couple of version but nothing works. Any ideas on how to do this? At the moment I'm using SAS Studio to do this.

Cheers,

Henrik

Re: Cast from Blob to text in Viya

ChrisNZ — Wed, 13 May 2020 02:55:41 GMT

> I now want to convert the data as text

So you want to extract text from MS office files currently stored as blobs?

Re: Cast from Blob to text in Viya

heffo — Wed, 13 May 2020 03:00:07 GMT

That is correct. Get the text from the word documents (and possibly email messages) to be used as text for Text Analytics.

Re: Cast from Blob to text in Viya

ChrisNZ — Wed, 13 May 2020 03:23:50 GMT

There may be better ways, but now that DDE has been killed by MS, I would probably:

1. Extract the MS SQL data and save it as a binary file. If the files are larger then 32k, you'll need to use explicit SQL pass-through so the data is passed in 32k chunks.

2. Run a vbs script to dump the contents into a txt file.

3. Read that txt file into SAS

Re: Cast from Blob to text in Viya

SASKiwi — Wed, 13 May 2020 03:29:27 GMT

You'll need to do that in SQL Server as part of an SQL passthru query. Googling SQL Server blob to string found this example: https://dba.stackexchange.com/questions/240441/sql-server-convert-blob-data-to-string

amongst many others.