cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
learningflow
Helper I
Helper I

Split a CSV into two tables using a keyword after parsing

Hi Everyone,

 

I am working on a use case where I have a CSV that contains two separate tables with different set of fields, below is a screenshot on the same. Its got Aircrafts table data and Flights table data. 

 

Capture.PNG

Below is a screenshot of Flow that I have setup and its able to parse the csv and generate an output of data in string format which is great. I am using Encodian's parse csv action to parse the csv. However, approach below parses the csv into one table with all the data. What I am looking to do is, may be by using String manipulation, if I can split the data into two tables: one for Aircrafts and other for Flights. Can someone assist with the best approach here, may be by splitting the string where it reads 'Flights table' in csv? thanks in advance. 

Flow.PNG

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @learningflow 

 

Obviously we picked this up seperately but here is the solution based on the file you shared with me.  we are splitting on The word table, some commas and an return line based on an if statement which caters for different file encodings as explained on my blog post.  I also have included a string for the first table as the header will include some extra commas and a return line (we simply remove using replace).  Ultimately, table 1 and 2 are called by integer indexes.

 

DamoBird365_0-1630611238248.png

and the flow returns two csv's

DamoBird365_1-1630611379214.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

View solution in original post

8 REPLIES 8
DamoBird365
Super User
Super User

Hi @learningflow 

 

add a compose and type 

Table

followed by hitting the return key

 

Then create another compose and use split() with the csv body as the first parameter and the compose with string table and return key as the second parameter. This will give you an array with 3 objects.

 

Objects start at 0, therefore you need to call object 1 and 2 by copying the expression for the compose containing the array and adding ?[1] to it.

 

This video gives you some examples on manipulating Json arrays if above doesn’t make sense. https://youtu.be/6nJSUNh579w

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Thanks @DamoBird365  for your response, I appreciate it. Would you please share screenshot of the compose actions you mentioned, it will be super helpful to understand, I did watch the video but could not figure out what to do in my use case, appreciate the help in advance. 

DamoBird365
Super User
Super User

Hi @learningflow 

 

here you go....

DamoBird365_0-1630527859189.png

 Comments are on each actions with the expressions and output of the last output is as follows:

DamoBird365_0-1630527948990.png

 

If you want to learn how to parse a CSV without a premium connector, take a look here too 🙂

https://www.damobird365.com/how-to-parse-a-csv-to-json-array-flow/

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

Thanks so much @DamoBird365 , I am not getting it to work somehow. However, I am getting something similar to below. Thoughts? Second screenshot shows the data coming from file content (after using your base64tostring conversion, thanks for that too!!)

Capture.PNGdatacompose.PNG

Hi @learningflow 

 

I guess your file is using a different encoding. My article covers that but what it means is that your return will be encoded as a hex value and the return will be being ignored. Try the string Table followed by the if statement expression in step 4 of the article.  It should detect the encoding and choose the right return. Make sure you remove the physical return line from the compose, but include the word Table. Also make sure there is no space between the dynamic block and the string Table.

 

Damien

learningflow
Helper I
Helper I

Excellent, thanks for the help. I have almost got it to working and been able to get into a format where I can parse. However, I am getting errors on parse json and need your help to see what I am doing wrong, I spent about 3 hours to figure out but could not, please see below. First screenshot shows the first table extracted through compose action:?[1] and then I am feeding that into parse csv which parses the data without an issue. 

 

Capture1.PNGCapture2.PNG

 

 

 

 

 

Below is what I tried. @DamoBird365 

Capture.PNG

 

Some outputs below:, if statement is not returning anything unfortunately. Thoughts?

4.PNG5.PNG2.PNG3.PNG

file.PNG

Hi @learningflow 

 

Obviously we picked this up seperately but here is the solution based on the file you shared with me.  we are splitting on The word table, some commas and an return line based on an if statement which caters for different file encodings as explained on my blog post.  I also have included a string for the first table as the header will include some extra commas and a return line (we simply remove using replace).  Ultimately, table 1 and 2 are called by integer indexes.

 

DamoBird365_0-1630611238248.png

and the flow returns two csv's

DamoBird365_1-1630611379214.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,299)