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

Vivek Komarla Bhaskar 911 Reputation points
2023-05-11T10:09:33.0833333+00:00
  1. I created a logic app as shown below:

Screenshot 2023-05-11 at 10.44.25 am

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"
Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,952 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Sonny Gillissen 3,351 Reputation points
    2023-05-11T19:57:34.25+00:00

    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.


  2. Soumen Das 10 Reputation points
    2023-05-11T22:25:36.71+00:00

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