topic Re: Loading pictures/images in QlikView

Loading pictures/images

Mon, 10 Oct 2016 12:07:48 GMT

I have to load the following as a picture. I have not come across this before

example in attached file. Any ideas?

Re: Loading pictures/images

adamdavi3s — Mon, 10 Oct 2016 12:58:30 GMT

Is that a SQL BLOB or base 64?

Re: Loading pictures/images

adamdavi3s — Mon, 10 Oct 2016 13:01:32 GMT

For BLOBs I used this weird two step process

// As dumb as it is, this is the two step code to load binary images into QV.

logos:

SQL SELECT

ConfiguredValue as logo_name

,image

FROM [DW_XXXXXXXXXXXX].[dbo].[XXXXXXXXXXX_SSRS_CONFIG]

WHERE ConfiguredValueType LIKE 'image%'

;

INFO SELECT

ConfiguredValue as logo_name

,image

FROM [DW_XXXXXXXXXXXXXXXX].[dbo].[XXXXXXXXXXX_SSRS_CONFIG]

WHERE ConfiguredValueType LIKE 'image%'

;

Re: Loading pictures/images

Mon, 10 Oct 2016 13:29:38 GMT

No real idea, it's coming through in an Excel file

Re: Loading pictures/images

adamdavi3s — Mon, 10 Oct 2016 13:40:58 GMT

They look like blobs... but not sure.. its not BASE64 AFAIK

Let me have a play

Re: Loading pictures/images

adamdavi3s — Mon, 10 Oct 2016 13:57:24 GMT

It looks like a BLOB of some sort but I can't get them to import I am afraid.

I tried setting the file up as an ODBC connection and connecting that way but it won't do anything with the strings

Re: Loading pictures/images

MarcoWedel — Mon, 10 Oct 2016 22:54:02 GMT

Hi,

maybe one solution might be:

table1:
LOAD 'Pic'&RecNo() as PicNo,
    A
FROM [https://community.qlik.com/servlet/JiveServlet/download/1139317-249014/Book1.xlsx] (ooxml, no labels, table is Sheet1)
Where Len(A);

Left Join
LOAD A,
    Len(A) as LenPic
Resident table1;

tabTemp:
LOAD A,
    OctetStringToFile(A, 'D:\Temp\'&PicNo&'.yourfileextension') as ResultOctetStringToFile
Resident table1;

Bundle Info LOAD
  PicNo,
  'D:\Temp\'&PicNo&'.yourfileextension'
Resident table1;

Function OctetStringToFile(OctetString, FilePath)
Const adTypeText = 2
Const adSaveCreateOverWrite = 2

LenData = Len(OctetString)\2 + Len(OctetString) Mod 2
set fso = createobject("scripting.filesystemobject")
set stream = createobject("adodb.stream")
stream.type = adTypeText
stream.charset = "windows-1252"
stream.open
For i = 0 to LenData-3
  stream.writetext chr(CByte("&h" & Mid(OctetString, i*2+3, 2)))
Next
stream.savetofile FilePath, adSaveCreateOverWrite
stream.close
OctetStringToFile = "processed"
End Function

hope this helps

regards

Marco

Re: Loading pictures/images

Wed, 12 Oct 2016 07:58:21 GMT

I will have a look at this in the next few days. Thanks for your trouble