cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krishna_R
Resolver IV
Resolver IV

Create CSV table

Hi,

I'm having a txt file with a delimiter '~' something like this

Krishna_R_0-1627057963299.png

 

I'm trying to create CSV file as shown below

Krishna_R_1-1627058137323.png

 

I'm not sure what I'm doing wrong here. I'm getting my output as like this

Krishna_R_2-1627058487460.png

 

I've tried item()[0] and item()[studentID#] instead of item(). but no luck. any thoughts ?

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Krishna_R 

 

It looks like you can do it the orignal way I proposed.  The create HTML action is very much like the select.

 

Your data appears to use 0D to indicate a new line, split the file on this first and then split the values on ~, selecting the individual value using an ingeger:

 

DamoBird365_0-1627301628893.png

FROM: skip(split(outputs('Compose'),'0D'),1)

ID: split(item(),'~')?[0]

StudentName: split(item(),'~')?[1]

Class: split(item(),'~')?[2]

etc, 3,4,5,6,7,8

 

You appear to have a couple extra columns in your sample data but I am sure you can clean that up.  This is the output:

 

DamoBird365_1-1627301670201.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

10 REPLIES 10
DamoBird365
Community Champion
Community Champion

Hi @Krishna_R 

 

you want the from to be split by a return line to form an array of lines containing the ~ separated fields. Then you want the value to be split(item(),’~’)[0] ,1,2,3 etc.

 

This blog post will give you some ideas https://www.damobird365.com/how-to-parse-a-csv-to-json-array-flow/ note the file encodings for the line break at the end of the article.

 

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 😉

Krishna_R
Resolver IV
Resolver IV

@DamoBird365 

I'm passing split content of file content to the csv table here. if i need to pass split(item(),’~’)[0] instead of item() what would be the from value of my csv table ?

Krishna_R_0-1627277815869.png

 

Hi @Krishna_R 

 

I probably wasn’t very clear in my first post and was in autopilot, thinking you wanted an array.  You could convert your file into an array first and then pass that to the create csv table, albeit it might be possible to do what I’m thinking direct in the create csv table action I’m not at a pc just now to check.

 

using the Select action, pass your string split on the return line. Then use the method described previously to split each line by the ~.

 

your select will give you the array to then pass to the create csv table.

 

However, a much simpler method to convert from ~ to , separated would be to just use the expression replace. Abandon your create csv table action, insert a compose and construct an expression replace(outputs(‘get_file_content’)[‘body’],’~’,’,’) note that replace has three fields, the source, the string to replace ‘~’ and the string to replace with ‘,’

 

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 😉

 

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 😉

Krishna_R
Resolver IV
Resolver IV

@DamoBird365 

I've tried as below

Krishna_R_0-1627285655798.png

 

and also i've tried with split

Krishna_R_1-1627285705959.png

 

but no luck

If you supply me a copy of the data, I’ll build up a flow for you.

 

Damien

Krishna_R
Resolver IV
Resolver IV

Hi @DamoBird365 

thanks much. here you can download the data copy

Hi @Krishna_R 

 

It looks like you can do it the orignal way I proposed.  The create HTML action is very much like the select.

 

Your data appears to use 0D to indicate a new line, split the file on this first and then split the values on ~, selecting the individual value using an ingeger:

 

DamoBird365_0-1627301628893.png

FROM: skip(split(outputs('Compose'),'0D'),1)

ID: split(item(),'~')?[0]

StudentName: split(item(),'~')?[1]

Class: split(item(),'~')?[2]

etc, 3,4,5,6,7,8

 

You appear to have a couple extra columns in your sample data but I am sure you can clean that up.  This is the output:

 

DamoBird365_1-1627301670201.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

Krishna_R
Resolver IV
Resolver IV

@DamoBird365  thanks. '0D' in column name is a typo i believe. if we want to go for a new line do we need to use '\n' ? and also is there a way where we can perform the same functionality on the email attachment 

Hi @Krishna_R 

 

depends on where that data comes from and the encoding. If you look at that article I linked to, there is an explanation at the bottom for different return lines encodings. You can try it on an actual return line, the easiest method is to insert a compose action and literally hit return, before adding the compose as the 2nd parameter for the split. Give that a go first.


when it comes to the email, you can simply get the file attachment content as it will be plain text, no need to save a copy.

 

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 😉

 

Krishna_R
Resolver IV
Resolver IV

@DamoBird365 

I've been successfully getting the required values into csv table. is there a chance to add this values to an excel add row


Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,196)