Create CSV table

[Krishna_R]

Hi,

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

I'm trying to create CSV file as shown below

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

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

[DamoBird365]

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]

@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 ?

[DamoBird365]

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]

@DamoBird365 

I've tried as below

and also i've tried with split

but no luck

[DamoBird365]

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

Damien

[Krishna_R]

Hi @DamoBird365 

thanks much. here you can download the data copy

[DamoBird365 · Accepted solution]

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:

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:

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]

@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 

[DamoBird365]

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]

@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