How to convert excel images in Binary format to update SQL table

[Anonymous]

Hi Mates,

How to convert images in to binary to update SQL table...?

We have a requirement to read excel(field values) and update in SQL table. team can able to read and write string data, but in excel we have JPEG images these images also need to update( may be convert in to binary)in to Table.

So how to convert excel images in to binary format to update.

your help will be highly appreciate.

[rsaikrishna]

@Anonymous 

Please check following videos created by @DavesTechTips :

Introduction - https://youtu.be/wJj0E1C4z0c

Using Variables in file upload - https://youtu.be/sgAx3aaRL8E

SQL Binary - https://youtu.be/hif0VfOhhMA

I hope these videos will help you to achieve what you are looking for.

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted Solution. Marking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

[DavesTechTips]

Thank you very much for the shout out @rsaikrishna ! 

Please let me know if you don't come right.

[Anonymous]

Hi @rsaikrishna , @DavesTechTips 

Really thanks for reply,

Still issue is opened, can you guys help on above requirement please ..?

read images from excel and convert in binary and update binary value in to SQL to refer the images.

Yes I can send Meating invitation, since it is blocking the entire module, please reply or send email with Emails, let me know availability.

Please help me

[rsaikrishna · Accepted solution]

@Anonymous 

After further investigation into the requirement, checked the flow to see if we read the images from Excel. None of the flow actions supports this directly or indirectly.

Then, started checking for workarounds.

When we save an excel file with images as html, then we can see all the images in excel file saves in a folder along with an xml file with the list of files and also every worksheet will have one Sheet1.html file. This file will contain the image content in binary/base64. Unfortunately, saving as html and parsing thru these files is custom dev work and cannot be done from flow. Write a C#, Python or some other way.

Another workaround: In the Excel, write a macro to read the images as Shapes, save them in local directory and convert them to either base64 or binary.

I am not VBA expert but i am able to manage to show the base64 code in a message box. You can update the code in the column next to the image and this code can be read from the flow.

Here is the Macro's VBA Code for your reference:

Public Function convertImageToBase64(filePath)             Dim inputStream             Set inputStream = CreateObject("ADODB.Stream")             inputStream.Open             inputStream.Type = 1 ' adTypeBinary             inputStream.LoadFromFile filePath             Dim bytes: bytes = inputStream.Read             Dim dom: Set dom = CreateObject("Microsoft.XMLDOM")             Dim elem: Set elem = dom.createElement("tmp")             elem.DataType = "bin.base64"             elem.nodeTypedValue = bytes             convertImageToBase64 = "data:image/png;base64," & Replace(elem.Text, vbLf, "") End Function Sub ConvertToBase64()             Dim ch As Chart             Set ch = Charts.Add             ch.Location xlLocationAsObject, "sheet1"             Set ch = ActiveChart             Dim sh As Shape             For Each sh In ActiveSheet.Shapes                    sh.CopyPicture                                  ActiveSheet.ChartObjects(1).Width = sh.Width                    ActiveSheet.ChartObjects(1).Height = sh.Height                    ch.Paste                     filePath = "C:\Users\xxxx\Documents\Demo\" & sh.Name & ".png"                     ch.Export filePath                    base64Code = convertImageToBase64(filePath)                    MsgBox base64Code                   Next sh End Sub

After speaking with a friend, I came to know that the For Each loop in the above function may not read the images in the same order. For that, in the VBA code, we need to read the co-ordinates of the cell and then get the image. I did not try this. Also, most of the companies may not allow Macros in the excel due to security concerns. We may need to try alternate work around such as creating an Azure Functions to read excel content. This requires custom coding. Once the Azure function is ready, we can call them from Power Automate actions. I hope Microsoft will provide an appropriate action to read images.  Keeping maintainability or support of technical solutions will be challenge if the solution is complex. I recommend to store the data in SharePoint list or similar way which Power Automate supports if business is convinced to use it. All the best. Regards Krishna Rachakonda If this reply helped you to solve the issue, please mark the post as Accepted Solution. Marking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

[Anonymous]

Hi @rsaikrishna @DavesTechTips 

IN Fact issue is still opened, since it is not possible using flow as of now closing this issue case.

Just accepting as Answer.

[Yutao]

Hey @Anonymous and friends,

Wondering if you'd like to try out the new "Run script" action of Excel Online (Business), where you can actually run Office Scripts to retrieve images from Excel as base64 encoded content then pass to the next action in Power Automate.

You'll need to first create the "Get image" script in Excel Online. Here is one sample script that can be used here (imaging there is an image named "Picture 5" on "Sheet1"):

function main(workbook: ExcelScript.Workbook): string {
  let shape = workbook.getWorksheet("Sheet1").getShape("Picture 5");
  return shape.getAsImage(ExcelScript.PictureFormat.png);
}

Here are a few links that might be helpful to learn more about the new Run script action and Office Scripts:

• Office Scripts in Excel on the web (overview )
• Announcing support for Office Scripts in the Excel Online (Business) connector
• Run Office Scripts with Power Automate
• Call scripts from a manual Power Automate flow
• Pass data to scripts in an automatically-run Power Automate flow

Hope this helps!

Yutao