Cast from Blob to text in Viya

[heffo]

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 

[ChrisNZ]

> I now want to convert the data as text

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

[heffo]

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

[ChrisNZ]

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

[SASKiwi]

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.