Retrive a Base64 text to picture (1 Viewer)

sajarac

Registered User.
Local time
Today, 06:45
Joined
Aug 18, 2015
Messages
126
Retrieve a Base64 text to picture

Hello there.

I am a Microsoft PowerAPPS user, and I have an app that save the pictures taken with my device camera into a SharePoilt list, the pictures saved are in base64 format, something like this:

data:image/jpeg;base64,/9j/4AAQSkZJRgABA..........

I would like to know if is possible to convert that base64 format in a jpeg or PNG picture to view in a nice report in my MS Access?

Thanks in advance for any response.

Regards,
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:45
Joined
Jul 9, 2003
Messages
16,244
I note your post has yet to receive a reply. I thought it was worth bumping it up the list just to make sure everyone's seen it.
 

jleach

Registered User.
Local time
Today, 06:45
Joined
Jan 4, 2012
Messages
308
Base64 is a text-based representation of binary data. You can use VBA to encode/decode it. Below is a link to a function that claims to do so (I haven't tested this one in particular, but there's many out there for you to try):

http://www.source-code.biz/snippets/vbasic/12.htm

The data is returned from the function as a bytearray, which is not immediately useful in displaying on Access form/reports. You'll have to save the bytearray as a file. Again, numerous examples exist on the web... below is another random search result:

https://stackoverflow.com/questions/14366033/how-to-save-a-byte-array-to-a-file-in-vba

Save the file with the extension indicated in the original data. Then you can set your picture control source to point to that file. This can all be done at runtime via code, but you may have a bit of lag due to the file IO required.

Also note that in the data sample you provide, you'll need to remove the initial format information:

data:image/jpeg;base64,/9j/4AAQSkZJRgABA

In this case, everything after "base64" should probably be used (or perhaps everything after the last "/" - unsure offhand, you'll have to play around and see what works, after which you can come up with a bit of logic to handle it automatically). Once saving the resultant bytearray to file, you can open the file normally via Windows Explorer to ensure you have the decoding/saving operation correct prior to doing the Access picture application.

hth
 

sajarac

Registered User.
Local time
Today, 06:45
Joined
Aug 18, 2015
Messages
126
Thank you very much for your prompt reply. I was reading those two choices and it seems like they are designed for expert people as you guys. And I am in baby steps. but thanks again. I will continue searching!
 
Last edited:

jleach

Registered User.
Local time
Today, 06:45
Joined
Jan 4, 2012
Messages
308
Well, first let's see if we can decode the Base64 string. Start out getting the actual Base64 portion of the original data string (I'm guessing everything after the last "/", but we'll have to verify).

Probably will look something like this:

Code:
Function GetBase64DataFromString(StringIn As String) As String
  Dim LastSlashPos As Integer
  Dim Result As String

  LastSlashPos = InStrRev(StringIn, "/")
  Result = Mid(StringIn, LastSlashPos)

  GetBase64DataFromString = Result
End Function

Make any necessary adjustments to the code so only the Base64 portion of your original string is being returned.

You may also need to get the type format of the data (e.g., the jpeg part so you can determine what the file extension should be). While we're chopping up the original string, try something like this:

Code:
Function GetTypeExtension(StringIn As String) As String

  'Make sure we have an image format
  If InStr(1, StringIn, ":image/") = 0 Then Exit Function

  'Let's split the string into an array on the "/" character
  Dim v As Variant, s As String
  v = Split(StringIn, "/")
  
  'Now v is an array containing all the substrings between the "/"
  'The second one is the one we want, but arrays are 0-based, so 
  'we get element index 1:
  s = CStr(v(1))

  's should be something like "jpeg;" now...

  Select Case s
    Case "jpeg;": GetTypeExtension = ".jpg"
    Case "png;": GetTypeExtension = ".png"
    'etc
  End Select
End Function

If you can get that working, you can use it to save the file in the correct format at a later step.

Have a work through those and see if you can get through those steps. After that we'll attack decoding the string and saving it to a file.

Cheers,
 

sajarac

Registered User.
Local time
Today, 06:45
Joined
Aug 18, 2015
Messages
126
Hello,

Sorry for my late reply. I have been working with this but I am completely stuck. Sorry, I don't know how to proceed with your code.

I have my table in access which is connected with my sharepoint list. in that table I have the fields that contain the base64 text. But the point that I don't get it is how to incorporate your code.

Again please apologize not an expert.

Regards.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:45
Joined
May 7, 2009
Messages
19,169
here is starting to start with, copy this to a module in Excel file (same principle in access). then run the Sub test()/
Code:
'https://stackoverflow.com/questions/39126617/inserting-an-image-into-a-sheet-using-base64-in-vba
Sub Test()

    Dim strTempPath As String
    Dim arrTest(1 To 3) As String
    Dim intCounter As Integer

    'base 64 image examples
    'red dot
    arrTest(1) = "iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg=="
    'little face logo
    arrTest(2) = "R0lGODlhDwAPAKECAAAAzMzM/////wAAACwAAAAADwAPAAACIISPeQHsrZ5ModrLlN48CXF8m2iQ3YmmKqVlRtW4MLwWACH+H09wdGltaXplZCBieSBVbGVhZCBTbWFydFNhdmVyIQAAOw=="
    'Stack Overflow logo
    arrTest(3) = GetSOLogoBase64

    'use workbook path as temp path
    strTempPath = Environ("temp") & "\temp.png"

    For intCounter = 1 To 3

        'save byte array to temp file
        Open strTempPath For Binary As #1
           Put #1, 1, DecodeBase64(arrTest(intCounter))
        Close #1

        'insert image from temp file
        Sheets("Sheet1").Cells(intCounter * 4, 1).Select
        Sheets("Sheet1").Pictures.Insert strTempPath

        'kill temp file
        Kill strTempPath

    Next intCounter

End Sub

Private Function DecodeBase64(ByVal strData As String) As Byte()

    Dim objXML As Object 'MSXML2.DOMDocument
    Dim objNode As Object 'MSXML2.IXMLDOMElement

    'get dom document
    Set objXML = CreateObject("MSXML2.DOMDocument")

    'create node with type of base 64 and decode
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.Text = strData
    DecodeBase64 = objNode.nodeTypedValue

    'clean up
    Set objNode = Nothing
    Set objXML = Nothing

End Function

Function GetSOLogoBase64() As String

    GetSOLogoBase64 = ""
    GetSOLogoBase64 = GetSOLogoBase64 & "iVBORw0KGgoAAAANSUhEUgAAANAAAAA4CAMAAAC7bYapAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvq"
    GetSOLogoBase64 = GetSOLogoBase64 & "GQAAADJUExURSIkJi8wMi8xMzw+QD0/QUpMTktNTlhaW1lbXGZnaWdoanR1dnV2d4KDhIOEhZCRkpGSk56en56foKusraytrrm6u7q7u7y7u8"
    GetSOLogoBase64 = GetSOLogoBase64 & "TDw8fIyMjHx8jJyczLy83MzNXV1tnY2N3d3ePj4+bl5e7u7vHx8fLy8vSAJPSHMPSIMfWPPvWQP/aXTPaYTfafWvegW/enZ/eoaPivdfiwdvm"
    GetSOLogoBase64 = GetSOLogoBase64 & "3g/m4hPm/kfrAkvrHnvrIn/vPrPvQrfzXuvzYu/zfyP3gyf3n1f7v4//38f///4l4PkAAAATsSURBVGje7Zh5e5tGEIeXS4CQDArGKlVEQ7pF"
    GetSOLogoBase64 = GetSOLogoBase64 & "cn0kjq8qqiqJ7/+hOjO7LAgdsRWnjXjYPyxgl2HeOX67j1nesMFaoBaoBWqBWqAW6GcGWs+bBbS8zuZNAlpfZtl01aQMzbIsu25UD90B0X1Dg"
    GetSOLogoBase64 = GetSOLogoBase64 & "Nb05wqI5o0AmmczkoXJSbXRfqDlNMse8OL5pNqIHZSD7DOW3Se4eGxAyWGtZdcrod3ZogGisEI9mC4BDX/XDZDtNRYbSgO20e2rbQfBdzrHz4"
    GetSOLogoBase64 = GetSOLogoBase64 & "MgzOPgNYb2AC3l7z0SPRzZRuw7T76JyRizc5+9xtCepZfTp3VVGqiNlvVVYZD+SKAOezMgxJg+rSrSsMwms61lNot/IFAKIJYfvgkQnndgfFm"
    GetSOLogoBase64 = GetSOLogoBase64 & "V0jBf5v8xUAwgaP9NSm5xI5EWUhqudq16NVA6Gu1p/1H6IqDRiB+rckuZpdsFSsOkmp8LkJ3zlIDejdDFNOg6Z6qfLs6c7jteAvEgSOjiHJtc"
    GetSOLogoBase64 = GetSOLogoBase64 & "73EqJo/aHi4GxUTAhfd2hLc9jkAdp18CJV1sKgcgDZihJqbX89yF5+k3ZXslka6/5rNFTXvwiwjEmBahWRpn5D61MtOjAggekPN5Ty7rcHRAx"
    GetSOLogoBase64 = GetSOLogoBase64 & "0d9cqQygd7r4i6K2aYoJHKChfI9AjFxCmasF+1DjxNC+rOSH65rIcQ4NEIE8nwIvqXBNQ81jBa4b0c89vED5IfiiVgxHAoBZs1Eh/HGsA2RNZ"
    GetSOLogoBase64 = GetSOLogoBase64 & "8pr2tAXFcG0oQW5JwJO3jbPwA0//LXPwoJ9TqrHLYjkeRqD/FEznTQIVfccAEE+QxLEY5y7pEPGnkg/DJF/XkUdfTeSvkg3Ooh5PY4xcXD9z3"
    GetSOLogoBase64 = GetSOLogoBase64 & "xCO0MZHz2AdFuevvwVeRldpndVSZ98GqPKOB3Oyyt3kOZhOqOWDm1jUe1gq5ypOJqwi+7oQbkyXUuZk28j62Dv1D62iFRuMqKcfv0NyRnttrQ"
    GetSOLogoBase64 = GetSOLogoBase64 & "HocfAFK1TPfAY1Y0y8cLA38TimkH44zhDXDQgoqk1YDAayMvbjFPCYRAs9BOoTH7gB5vJlk5pp+fa1oN2tML4gpQfObggA/FEL0KkM4sQVEBs"
    GetSOLogoBase64 = GetSOLogoBase64 & "ukXsPopFaHqmm8D2QoIGQZA5UI4+jHZ2QX0fjz+Qyrc/P5GIX3K84/j8W/qwEMNjImSQD2m2Ti2gVjINVndNSBwxBxQpRwHBNXmQt2FEBSzX9"
    GetSOLogoBase64 = GetSOLogoBase64 & "15N4CGw+Hv1a1odkfVB6fSD8Ph+42NMLKg4AVQzCxelBzfBArRcycvthxfSqwvzjW66uhqix4AMssVoAe6jk1lkR33RUDizPB8t9wGyikXtvx"
    GetSOLogoBase64 = GetSOLogoBase64 & "gXPaQxnjtpGBJWSS9FiSh8K/UXGGAHwQq9h6HMpXS666IB1PKsxNo8VQbq11ACZh1N4BS/K4ne6Y8KSRMT4vdPEguOtIvklxDqoQOFtKu3Fh3"
    GetSOLogoBase64 = GetSOLogoBase64 & "AcViH+tcJIHMtCU5BBk/BPSY1caiCtRzUrnvo8Z2ybVucUQA+xr1Z9rpqaNPXxRdojqF9kBoLnluCdUEEO0AimV7WcUyDd0fFBz42MqPBuJQK"
    GetSOLogoBase64 = GetSOLogoBase64 & "t0g6OloBLzXHawe0wkcXSv2P9MhPVZnOW3TcVeE01M7jld46u3MkNhu/JxLIi0p4kOdMygCc2SGQOTQqkU2EtcAA7wPVaN5qSwx/D4ejNRpO2"
    GetSOLogoBase64 = GetSOLogoBase64 & "I6MaQerLPC0t9CQCJy2Y139xD4LJQxDwHJ8OS+a8jOScqt+EWiIMZ2D53EP0laoBMA+nW8c/xyskD7Rwv0EwB9ODQ+NuBfwac4WqAWqAVqgVq"
    GetSOLogoBase64 = GetSOLogoBase64 & "gFuj/HP8CZQ0/RA2L6ggAAAAASUVORK5CYII="

End Function
 

sajarac

Registered User.
Local time
Today, 06:45
Joined
Aug 18, 2015
Messages
126
OK, I did that, then I run the macro after that I got the stackoverflow icon, a red dot icon and a blue icon
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:45
Joined
May 7, 2009
Messages
19,169
can u cooy the table with base64 to new db, zip and upload so i can give u sample in access.
 

sajarac

Registered User.
Local time
Today, 06:45
Joined
Aug 18, 2015
Messages
126
Hi there, here is the sample, I cannot convert into a db file but you will get the field which contain the base64 chain.

Thanks in advance for all your help.
 

Attachments

  • Exterior.xlsx
    351.5 KB · Views: 276

KappaAW

New member
Local time
Today, 11:45
Joined
Dec 23, 2022
Messages
3
Thank you!!!!
Finally an usefull and usable solution!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Feb 19, 2013
Messages
16,553
Finally? post is 7 years old so 'finally' happened a long time ago ;)
 

KappaAW

New member
Local time
Today, 11:45
Joined
Dec 23, 2022
Messages
3
:)! yes!!! but I've been looking for a solution to my problem for days! and finally.... (sorry for my English...)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Feb 19, 2013
Messages
16,553
my eyes aren't what they used to be - thought it said 2016:(
 

isladogs

MVP / VIP
Local time
Today, 10:45
Joined
Jan 14, 2017
Messages
18,186
Sorry to run salt into your wounds but your mathematical skills may also not be what they were as 2016 was 6 years ago 🤫 Happy Xmas
 

Users who are viewing this thread

Top Bottom