How to convert Pipe delimited file to Json using logic app?

Question

1. I created a logic app as shown below:

List blobs (V2): The purpose of this step is to list all the files in the given location.

For each: In this step, I will execute the next steps for each file that I have from the previous step [List blobs (V2)].

SplitLines: Using compose action to split the contents of the pipe delimited file on every new line into an array. Here is the expression used in SplitLines compose action:

split(body('Get_blob_content_(V2)'),decodeUriComponent('%0D%0A'))

RemoveLastLine: Removing the last(empty) line from the previous output using another compose action as shown below:

take(outputs('SplitLines'),add(length(outputs('SplitLines')),-1))

SplitFieldName: Separating header/field names using compose action:

split(first(outputs('SplitLines')), '|')

Select: Forming json as shown below using Select action:

**From**: **`skip(outputs('RemoveLastLine'), 1)`**
**Map:**
**`outputs('SplitFieldName')[0]`** **`split(item(), '|')?[0]`**
**`outputs('SplitFieldName')[1]`** **`split(item(), '|')?[1]`**
**`outputs('SplitFieldName')[2]`** **`split(item(), '|')?[2]`**
**`outputs('SplitFieldName')[3]`** **`split(item(), '|')?[3]`**
**`outputs('SplitFieldName')[4]`** **`split(item(), '|')?[4]`**

The problem I face is when I have a pipe character in my columns. As a result, I am not getting the expected values for my output columns in this case. My dataset delimiter is pipe '|' and the character used to quote column values if it contains a column delimiter is a double quote ("). Please see below for the data sample in my source -

distribution|path|last_ping_timestamp|user_agent|page_title
SITE|youtube.com/Music/|1680580438|Mozilla/5.0 (Linux; Android 13; SM-A127F) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Mobile Safari/537.36|Music : Englist
SITE|google.com/politics/|1680580438|Mozilla/5.0 (Linux; Android 13; SM-A127F) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Mobile Safari/537.36|"Politics : Latest & breaking News | Google"
SITE|"bing.com/news/home|country"|1680580438|Mozilla/5.0 (Linux; Android 13; SM-A127F) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Mobile Safari/537.36|"News : Latest & breaking News | Bing | Website"

Answer 1

Hi Vivek Komarla Bhaskar

Thanks for reaching out on Microsoft Q&A!

I think it's almost undoable to verify whether or not a pipe is within the text or the actual delimiter. Think it is possible, in a way, with RegEx, but that's not my expertise.

However, your remark on the double quotes for the values containing a pipe made me think: is it possible for you to give every column double quotes? Then you can split on the value "|" which is always the delimiter, and the pipes within your text aren't seen as split values. Or perhaps a double pipe || would do the trick as well.

Let me know if my answer was helpful by clicking "Accept answer". Feel free to drop additional queries in the comments.

Answer 2

Hi,

You can write your own custom code in Logic App by creating an integration account, linking it to the Logic App Workflow Setting and then use "Execute JavaScript Code".