Answered

Split a list of comma delimeted strings into a multiple columns array

(0) Share

Report

Posted on by Yann78

Hi!

I have a list of coma separated strings that i wan to split into an array of multiple columns. I have tried with a combination of get and split but i could not figure out the right code. any idea? if possible without for each.

from this (which is actually a 1 column array)

Name,start,finish

A,2024/3/3,2024/3/5

B,2024/4/3,2024/4/5

to this

Name	start	finish
A	2024/3/3	2024/3/5
B	2024/4/3	2024/4/5

thanks for your help!

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Manish Solanki 15,169 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Yann78

Could you please share the input array in the form of object? You have shared it as a table.

Was this reply helpful? Yes No
Yann78 18 on at

Like (0)

Report
Copy link

Link copied!

Thanks for you reply Manish. did you mean sharing the table as bellow?
[
{ "Name,Start,Finish": "A ;2024/05/23;2024/05/23" },
{ "Name,Start,Finish": "B;2024/06/23;2024/08/23" }
]

Was this reply helpful? Yes No
Verified answer

Manish Solanki 15,169 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @Yann78

Here is the sample flow.

I have stored the input array in the "Compose" action:

[
{ "Name,Start,Finish": "A ;2024/05/23;2024/05/23" },
{ "Name,Start,Finish": "B;2024/06/23;2024/08/23" }
]

Next, add "Select" action to transform the input array into the required format. We will be using expression to perform the transformation. Expression needs to be added in the expression box as shown below:

xpath(xml(json(concat('{"root":{"items":',outputs('Compose'),'}}'))),'//Name_x002C_Start_x002C_Finish/text()')

In the Map property, enter the column name as plain text and expression in the value part:

Map Value

Name

trim(first(split(item(),';')))

Start

trim(split(item(),';')[1])

Finish

trim(last(split(item(),';')))

The output of select action will give the required array.

Output:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Was this reply helpful? Yes No
Yann78 18 on at

Like (1)

Report
Copy link

Link copied!

Thanks Manish.
The input to the SELECT is a FILTER ARRAY not a COMPOSE, so I modified the Xpath function as following

{
"type": "Select",
"inputs": {
"from": "@xpath(xml(json(concat('{\"root\":{\"items\":',body('Filter_excel_array'),'}}'))),'//Name_x002C_Start_x002C_Finish/text()')",
"select": {
"Name": "@first(split(item(),';'))",
"Start": "@split(item(),';')[1]",
"Finish": "@last(split(item(),';'))"
}
},
"runAfter": {
"Filter_excel_array": [
"Succeeded"
]
}
}
And it worked fine. thanks for your solution.

By the way, there no simpler formula than the Xpath? I have no clue how to debugg it...

Was this reply helpful? Yes No
Manish Solanki 15,169 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Yann78

I am glad that it solves your problem 😊

Yes, you are right. Its a bit complex but I could not think of other solution. You may break the formulas into simple expressions and then use compose action to understand the output of each expression. For e.g.:

Expression 1: xml(json(concat('{"root":{"items":',outputs('Compose'),'}}'))

Expression 2: xpath(<dynamic output of previous expression 1>),'//Name_x002C_Start_x002C_Finish/text()')

Was this reply helpful? Yes No