Convert database BLOB (excel/word/..) for saving to local file system

Help
1

Hi, I have a lot of files stored in a database, that I would like to select from UiPath, and save to the local file system. The files can be of any type like Excel, Word, PowerPoint, Pictures, etc.

When I select the blob using the Execute Query, I get the content in an Object of type Byte Array.

Can anyone give me a hint how I can get that Object converted into a datatype that can be written as a file to the local disk?

Thanks,
Regin

1 Like
2

Hi @Regin did you ever figure this out?

3

I want to know the solution about the issue.

5

I would like to get an answer, too!

When I select a blob value from a ms database table, it returns a System.Byte. I suspect i would use the “Download blob to file” activity (https://docs.uipath.com/activities/docs/azure-download-blob-to-file), but I am unsure how.

Please, can anyone give a hint?

Thank you!

6

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Activities;
using System.ComponentModel;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace BLB2FILE
{

public class Class1 : CodeActivity

{
    private byte[] bytData;

    [Category("Input")]

    public InArgument<String> DocID { get; set; }

    [Category("Input")]

    public InArgument<String> ConsString { get; set; }


    [Category("Input")]

    public InArgument<String> DBQuery { get; set; }



    [Category("Input")]

    public InArgument<String> ColName { get; set; }

    protected override void Execute(CodeActivityContext context)
    {


        string docID = DocID.Get(context);
        string constring = ConsString.Get(context);
        string dbQuery = DBQuery.Get(context);
        string colName = ColName.Get(context);

     
        SqlCommand command = new SqlCommand(dbQuery);

        command.CommandType = CommandType.Text;

        SqlConnection myconn = new SqlConnection(constring);

        command.Connection = myconn;

        myconn.Open();

        SqlDataReader dr = command.ExecuteReader();

        while (dr.Read())

        {

            bytData = (byte[])dr[colName];

        }

        if (bytData != null)

        {

            /*If it is a file other than imagetype change the extension in the filepath accordingly. */
            FileStream fs = new FileStream(docID + ".pdf", FileMode.OpenOrCreate, FileAccess.Write);
            BinaryWriter br = new BinaryWriter(fs);
            br.Write(bytData);
            fs.Dispose();

        }


    }
}

}

Create this as nupkg file and use this as custom activity

7

Thank you for your answer!

I am sorry if I sound demanding, but while I am sure your suggestion works, are you sure it isn’t possible to get this done using UiPath activities? I mean, most of the code you suggested I already have using UiPath activities (connection to database, query, etc.).

What lacks is this: How do you convert a blob value to a file? Is it really not possible to do the following using UiPath activities?

/*If it is a file other than imagetype change the extension in the filepath accordingly. */
FileStream fs = new FileStream(docID + “.pdf”, FileMode.OpenOrCreate, FileAccess.Write);
BinaryWriter br = new BinaryWriter(fs);
br.Write(bytData);
fs.Dispose();

Thank you very much!

8

Good Point.

I didn’t remember exactly the scenario, but I think I tried in UiPath and found out that the version it’s supports well for VB and I am comfortable in C# . So hence took the route that was easier to me and then integrated to UiPath. This story is about years ago. By this time there should be way to do this in the tool itself
Thanks
Krishna

9

I created a ticket in the UiPath techical support and got this solution, that works splendidly!

  • Create a memory stream variable (Variable type System.IO.MemoryStream), call it memoryStreamdoc.

  • Then, connect to DB and execute query that returns a datatable, where one column is of type BLOB.

  • Loop through each myRow (row) in the datatable

  • Within the loop: Create an assign: memoryStreamdoc=new memoryStream(CType(myRow.item(1),System.Byte())). You need to replace the myRow.item(1) with the number of the column (in my case the second column, therefore item(1)).

  • Add an Invoke Code activity and choose the language VBNet and paste the following code File.WriteAllBytes(“D:\document.docx”, memoryStreamdoc.ToArray()). Add the memoryStreamDoc as argument (direction In, Type Memory Stream).

  • ​​​​​​​Also instead of indicating the file name and path (where I have as “D:\doc.docx”) you can create a variable that will get the file name from database and will save the file with the original name.

Thank you to Vladimir from UiPath for the solution :slight_smile:

2 Likes
10

Hi,
I tried to implement the steps as you said, But it gave error : ‘Invoke code: Exception has been thrown by the target of an invocation’
Do I need to add arguments in the edit arguments tab in invoke code activity?

Thank you

11

Yes, you need to add he following argument to the Invoke Code:

image

I will add that to my original comment.

12

Thank you for your reply. It worked for me now.