Providing a function for Base64-decoding text without writing a Custom Inline function


Badge +5

Hello everybody,

 

I would like to share my solution which took quite some time to find. Maybe it can help somebody.

 

Recently I needed a workflow that extracts the contents of .eml-email files in a library. As the mailcontent was base64-encoded and no out-of-the-box solution was provided by the workflow designer, I had to come up with something. Writing a custom inline function would be obvious but because then a .wsp has to be deployed which causes an IIS-restart which needed to be announced and scheduled due to company-processes. So this was no short term option for me. Also using an external webservice was a no-go as of data protection reasons.

 

So finally I found a solution which includes the SQL Server SharePoint uses. In this way I don't leave the SharePoint-environment and don't have to deploy anything.

The only thing necessary is that I had to ask my infrastructure collegues to create a new native sql account which gets the server role 'public' only to prevent any abuse.

 

 

1034i9AF0A68FC1E05D8D.png

 

 

Then I created an UDA which contains a single "Execute SQL"-Action that executes a SQL query that does the base64-decoding. The UDA needs two parameter, one input for the base64 encoded text, one output for the result.

 

1033iE212C27FC6D563B6.png

 

After a lot of googleing I found a SQL query that fits the requirement but also cares for encoding for other language than just english (non-ascii characters) like used in french, german, swedish, etc. With a little adjustment and the use of the input-parameter, the query looks like this:

 

SELECT 
        CONVERT(NVARCHAR(MAX),
                CONVERT(XML,
                        '<?xml version="1.0" encoding="UTF-8"?><![CDATA[' +
                        CONVERT(VARCHAR(MAX),
                                CONVERT(XML, N'{WorkflowVariable:mtxtBase64EncodedText}')
                                  .value('.','varbinary(max)')
                               ) + ']]>'
                       )
               );

With having this capsuled in an user defined action, every workflow designer can use this functionality without the need to care for sql-connections or knowledge about SQL language.


12 replies

Badge +9

@lonesurvivor 

Is there any way you can convert documents (pdf, word, excel, images) to base64?

Badge +5

For the default OnPrem-Workflow actions, I am not sure if there is an ootb-way to convert the binary data from the file for further processing into base64. Maybe the approach mentioned here can help you?

Userlevel 5
Badge +14

Use the custom Action that Vadim made: http://www.vadimtabakman.com/nintex-workflow-get-list-item-attachment-in-base64-custom-action-happy-thanksgiving.aspx

 

It's a lifesaver, and I am unsure why it has not just been built into the darn Product by now... 

Using the built in functions, you can grab a list of URLs to documents, encode them to base64, and then upload them to a different part of SharePoint using WebServices (which require a Base64 string). As far as I know it's the only way to do this via a workflow. 

 

 

Badge +9

Thank you @MegaJerk 

I have the solution installed, i can't recall but i thought i had it working in the past with the solution provided by vadim.

But now, i cannot seem to get the base64. Does it have to be a task list only that i can get the base64 from or can it be any list attachment?

I am however able to copy/move attachments and documents to different places using sharepoint web services through nintex workflow.

5114iCB1E4DD9F60B7D1A.png

Userlevel 5
Badge +14

>> Does it have to be a task list only? 

I don't believe so. It should work with any URL as far as I remember, but I'm sure you can use some of the other threads that have all of this information there. 

Badge +9

I cannot seem to find any community threads just for getting base64. There are a lot of threads on how to copy/move attachments and documents but not specifically on getting the base64.

I keep getting below error while using Vadims solution, and i am able to get the URL's properly. I must be missing something:

Error Getting List Item Attachment. Object reference not set to an instance of an object.

Badge +5

@brightlight 

I assume, the size of the files you want to process do exceed 8000 bytes?

Badge +9
Yes , they do. Most of the files will be larger than 50,000 bytes i believe.
Badge +9

@lonesurvivor @MegaJerk 

I finally got it to working with Vadims Solution. The issue was that the list name in the URL MUST match the list title, i figured that out after MULTIPL tries. Thank you guys for trying to help me out.

 

I also like this solution. Can we also store the decoded base64 as file into multilinetext SharePoint column?

Userlevel 5
Badge +14

>> I also like this solution. Can we also store the decoded base64 as file into multilinetext SharePoint column?


Don't see why you couldn't, but I'm not entirely sure why you'd need to when you could just use the WebService to produce the file proper (from the Base64) elsewhere. 

Just try saving the B64 to a Multiline Text Column and see what happens. 

Badge +5

>> I also like this solution. Can we also store the decoded base64 as file into multilinetext SharePoint column?

 

The data is stored in the multiline text column for sure but not in the sense that a user can click on it and a download-windows opens if that would be your intention. The user rather would see a long bunch of nonsense characters in his listview.

 

By the way, I found an (ugly) way to get SQL using a webservice to get the binary data of a file and convert it to base64-data but only for files maximal sized 8000 bytes which is really useless in my opinion because the most files, as yours, are larger than that. Maybe I'll post it here later on but, as I said, without the ability to overcome this limit which seems to be on the side of the sp_OACreate object, it's useless.

Badge +9

@lonesurvivor @MegaJerk 

Got it! I was looking to capture the signature that is stored as base64 into list column back to an image:5388i94D25A4642EEE325.png

 

 

Reply