You’re offline. This is a read only version of the page.
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.
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"):
Share
Report
All responses (
Answers (
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:
Hope this helps!
Yutao
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.
@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)
Sub ConvertToBase64()
Dim ch As Chart Set ch = ActiveChart
Dim sh As Shape
For Each sh In ActiveSheet.Shapes sh.CopyPicture ActiveSheet.ChartObjects(1).Width = sh.Width 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
|
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
Thank you very much for the shout out @rsaikrishna !
Please let me know if you don't come right.
@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. |
#1
WarrenBelz
146,524
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,906
Most Valuable Professional
