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

[lonesurvivor]

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.

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.

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.

[brightlight]

@lonesurvivor 

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

[lonesurvivor]

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?

[MegaJerk]

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

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. 

[brightlight]

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.

[MegaJerk]

>> 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. 

[brightlight]

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.

[lonesurvivor]

@brightlight 

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

[brightlight]

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

[brightlight]

@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?