As I have mentioned on my previous post, I have discovered an interesting bug while exporting a fixed width file using DMF within Dynamics 365 Finance and Operations. The bug occurs when you specify the row delimiter as {CR}{LF} or even {CR} on your fixed width file settings. In either instance, the file gets exported without a carriage return ({CR}) thus ignoring the row delimiter setting for the fixed width file. The exported file therefore wouldn’t be formatted as you would want, potentially causing readability issues for some text editors or applications that depend on the {CR}{LF} combination. In this post, I’ll take a deeper look into this and how I solved it by integrating a fix within the Logic Apps process required for exporting the data.

Replicating the bug

This bug can be verified by opening the exported fixed width file within a text editor (older versions of Notepad depend on {CR}{LF} to render new lines; applications such as Notepad++ provide the option of displaying such characters/symbols for easier identification) and notice that the file fails to be exported as configured.

Workaround 1

An easy workaround/hack in this scenario would be to add a string unmapped column to your data entity, write your data entity view method that returns {CR} as a string. Don’t forget to update your staging table and regenerate mapping on your export project. You would need to run a DB sync and refresh data entities for the new field to appear in the mapping.

Pros

• Easy to implement

Cons

• If Microsoft fix this bug, then the solution has to be revisited to remove the {CR} field and regenerate the mapping.
• This will likely have repercussions with the associated Logic App integration and require a new Export Project to be created with a new mapping.
• If a new field is appended to the end of the file, this will not work.

Workaround 2

Another possible solution that can be integrated into the Logic Apps process is to make use of the coolest inline code functionality and write your own JavaScript code to read the new line character and replace it with a carriage return (\r) and a new line (\n) character.

This can be accomplished by using the Inline Code block. I used the following minified JavaScript code, primarily to keep to the 1024 character limit required by the block:

let t=workflowContext.actions.Read_file.outputs.body['$content'],p=/(?<!\r)\n/gm;var a,b,d,e,f,g,k="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",o="",i=0;for(n=t.replace(/[^A-Za-z0-9\+\/\=]/g,"");i<n.length;)a=(d=k.indexOf(n.charAt(i++)))<<2|(e=k.indexOf(n.charAt(i++)))>>4,b=(15&e)<<4|(f=k.indexOf(n.charAt(i++)))>>2,c=(3&f)<<6|(g=k.indexOf(n.charAt(i++))),o+=String.fromCharCode(a),64!=f&&(o+=String.fromCharCode(b)),64!=g&&(o+=String.fromCharCode(c));t="",i=0;for(var c=c1=c2=0;i<o.length;)(c=o.charCodeAt(i))<128?(t+=String.fromCharCode(c),i++):c>191&&c<224?(c2=o.charCodeAt(i+1),t+=String.fromCharCode((31&c)<<6|63&c2),i+=2):(c2=o.charCodeAt(i+1),c3=o.charCodeAt(i+2),t+=String.fromCharCode((15&c)<<12|(63&c2)<<6|63&c3),i+=3);t=t.replace(p,"\r\n"); return t;

Expanded in a human-readable form, this appears as follows:

let t = workflowContext.actions.Get_file_content.outputs.body['$content'];

let p = /(?<!\r)\n/gm;

// Begin Base64 decoding (as atob isn't available)
var k =  "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";

var o = "";
var a, b, c;
var d, e, f, g;
var i = 0;

n = t.replace(/[^A-Za-z0-9\+\/\=]/g,"");

while (i < n.length)
{
    d = k.indexOf(n.charAt(i++));
    e = k.indexOf(n.charAt(i++));
    f = k.indexOf(n.charAt(i++));
    g = k.indexOf(n.charAt(i++));

    a = (d << 2) | (e >> 4);
    b = ((e & 15) << 4) | (f >> 2);
    c = ((f & 3) << 6) | g;

    o += String.fromCharCode(a);

    if (f != 64)
    {
        o += String.fromCharCode(b);
    }
    if (g != 64)
    {
        o += String.fromCharCode(c);
    }
}

t = "";
i = 0;
var c = c1 = c2 = 0;

while ( i < o.length )
{
    c = o.charCodeAt(i);
    if (c < 128)
    {
        t += String.fromCharCode(c);
        i++;
    }
    else if((c > 191) && (c < 224))
    {
        c2 = o.charCodeAt(i+1);
        t += String.fromCharCode(((c & 31) << 6) | (c2 & 63));
        i += 2;
    }
    else
    {
        c2 = o.charCodeAt(i+1);
        c3 = o.charCodeAt(i+2);
        t += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));
        i += 3;
    }
}

// End of base64 encoding

t = t.replace(p, "\r\n");

return t;

Much like in my previous post, there are two things going on here:

1. A bulk of the code containing a conversion of the Base64 from the prior Get File Contents step to ASCII within Logic Apps (for reasons explained before.
2. The use of RegEx to search for instance of line feed ({LF}/\n) without the carriage return ({CR}/\r): /(?<!\r)\n/gm.

Should this bug be fixed in the future, the RegEx in question will not result in new lines containing two carriage returns (e.g. {CR}{CR}{LF}) which may occur in a simple replace/substitution of {LF} with {CR}{LF}. Why? The RegEx performs what is known as a negative look behind — essentially implying that the \n should not be preceded with the \n character. Every instance of a lonely {LF} is then picked up and replaced with the {CR}{LF} as required.

A good thing is I was able to reuse most code that I’ve written on my previous blog post about overcoming the limitations of DMF in using the fixed-width file format.