Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Blogs / Learn with Subs / Expression for parsing a ta...

Expression for parsing a tab separated text/flat file, using Logic Apps

Subhad365 Profile Picture Subhad365 7 User Group Leader





In an earlier post (https://www.blogger.com/blog/post/edit/2520883737187850604/6852187532033424242), we talked about parsing a CSV/Excel file to feed data into D365F&O, this post is a kinda corollary to that, here we'll be talking about parsing 'a tab separated' document, the necessary expression for the same.

Suppose we have a file content like this:

SalesPool IdCompanyName

Poo01122USPMPool 11222

Poo01123USPMPool 11223

Poo01124USPMPool 11224

Poo01125USPMPool 11225

Poo01126USPMPool 11226

As you've rightly guessed, its a tab separated file. In the control connecter where you are getting the content of the file >> you can use the following expression could help you to transform the incoming content into an array:

split(decodeUriComponent(replace(replace(replace(uriComponent(body('Get_file_content_using_path')), '%0D', ''), '%09', ';'),'%0A', '#NEWLINE#')), '#NEWLINE#')

Outcome:


[

  "SalesPool Id;Company;Name",

  "Poo01122;DAT;Pool 11222",

  "Poo01123;DAT;Pool 11223",

  "Poo01124;USPM;Pool 11224",

  "Poo01125;USPM;Pool 11225",

  "Poo01126;USPM;Pool 11226"

]

Use a variable called: FileArray for the array thus generated.

Explanation

a. The expression changes the incoming string of the file content as a URIComponent, by calling the method: uriComponent().

b. It transforms all the special characters/escape sequence/delimiters, from which we are replacing the 'Carriage return' , identified as '%0D' , with ''.

c. It replaces the new line, identified as '%0A' with #NEWLINE#.    

d. It also replaces the tabs, identified as '%09' with ''.

e. And then it splits all the rows, on basis of the identifier: #NEWLINE# as separate array elements.

To understand which special character is identified as what, you can paste your text/file content manually at:

https://www.onlinewebtoolkit.com/url-encode-decode

This can help you which symbol could be encoded as what.

The rest of the process is just like the previous blog: you can query the resultant outcome, by performing a 'select' operation:

skip(variables('FileArray'),1)

Where, optionally you can skip the first row, if its a heading. And then you can map by the following key-value pair mapping:





Here as the figure indicates, the columns are evaluated by using:
split(Item(),';')?[1]


Where, 0, 1,2 , etc. indicate the sequences of the columns.

Comments

*This post is locked for comments