Answered

Split String into multiple substrings

(0) Share

Report

Posted on by MirCola

Hey,

i am currently building a flow, which puts a csv table into a xlsx file. A part of that is splitting a single line of content into multiple

substrings to match them with the columns of the excel table. The difficult part is to find the correct expression for the compose action to split the string.

The data lines look like this: text,text2,text3,1,2,3,01.01.2001 or text,text2,2,4,6,02.02.2002 and I want to split it into 5 parts. Starting at the back it needs to be split at every ",". The challenge is here that I don't know how to stop after the 4th "," so that I get only 5 Parts.

I tried different expressions like:

reverse(take(reverse(split(item(), ',')), 5))

But this cut everything behind the 5th ",".

The result should look like this: ["text,text2,text", "1", "2", "3", "01.01.2001] or ["text,text2", "2", "4", "6", "02.02.2002"]

Thanks for every help 🙂

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Expiscornovus 33,857 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @MirCola,

You can also use a skip to get those other items. With a join and an array function you can turn it into one item. With a union you can put all the items back into one array.

Try something like the below

union(array(join(skip(reverse(split(trim(item()), ',')), 4), ',')), reverse(take(reverse(split(trim(item()), ',')), 4)))

Was this reply helpful? Yes No
MirCola 6 on at

Like (0)

Report
Copy link

Link copied!

Hi @Expiscornovus ,

I tried your expression and it works great, but there is one thing I don't know why it happens.
If the data is like this: text,text2,text3,1,2,3,01.01.2001 the result is perfect.
But if the data is like this: text,text2,text3,1,1,3,01.01.2001 the 2nd "1" is not in the result array. When they are different it works like a charm.

Thank you for your help 🙂

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,857 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @MirCola,

Apologies, that is because I was using a union. That function will only use a value once in the array.

In that case let's use a concat and some additional split and join functions.

Try this instead:

split(concat(join(array(join(skip(reverse(split(trim(item()), ',')), 4), ',')), ';'), ';', join(reverse(take(reverse(split(trim(item()), ',')), 4)), ';')), ';')

Was this reply helpful? Yes No