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

Extract Data from a CSV file

I have a csv file. I was able to parse through the data and now the data is in the form as per snapshot below2020-04-17_11-19-25.png

 

I need to get the data separated based on the header and insert it into CDS.

 

Please help.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @v-litu-msft ,

 

I found a solution to the issue and an able to get the values extracted. I declared a variable and then got the string converted into array using 

split(triggerBody(),variables('initalData')) in compose
 

2020-04-21_13-25-08.png

Thanks for the help

View solution in original post

12 REPLIES 12
efialttes
Super User
Super User

Hi!

My suggestion is to follow this link to learn how to split your csv file into multiple lines (i.e. an array) https://powerusers.microsoft.com/t5/Building-Flows/Sliting-multiple-lines/td-p/90297

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



v-litu-msft
Community Support
Community Support

Hi @dc23,

 

You could use the "\r\n" as separator to separate the CSV into lines by using split() function:

split(outputs('Compose'),'\r\n')

Then for create records in CDS, remove the header:

skip(split(outputs('Compose'),'\r\n'),1)

 

Then use "," as separator to get each element:

split(item(),',')

Then use the index of the array to get corresponding data, then create a record in CDS, for example, "Date" is index 0, "Time" is index 1:

outputs('Compose_3')[0]

Annotation 2020-04-20 112441.png

 

Annotation 2020-04-20 112500.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-litu-msft  ,

 

Thanks for your input, however, when I use the step to skip the headers, in the output I only get []. Not sure why this is happening.

 

Thanks!

It seems that when I'm trying to get the data split as different items using the split() expression, that does not seem to be working.

To resolve this I have the file size reduced and not I'm getting the array like the snapshot below, however, when I try too remove the header, the results are the same, it given me []

 

 

Data received from the file

Getting data from the file. Data in string formGetting data from the file. Data in string form

 

v-litu-msft
Community Support
Community Support

Hi @dc23,

 

How about converting the CSV into the string by using string() function firstly?

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I guess that got converted using the split(). Here's the snapshot

 

2020-04-20_16-07-56.png

 

Thanks!

Hi @v-litu-msft ,

 

I found a solution to the issue and an able to get the values extracted. I declared a variable and then got the string converted into array using 

split(triggerBody(),variables('initalData')) in compose
 

2020-04-21_13-25-08.png

Thanks for the help

HEY MICROSOFT!!!

 

WHY DON'T WE HAVE A SIMPLE "PARSE CSV" ACTION YET IN POWER-AUTOMATE/FLOW!?!?!

 

This is a standard process in most scripting languages and is a very old and recognized "data exchange" format which is used for output by so many legacy systems!

 

It seems ludicrous to me that this is not an "out of box" standard function/action in Flow.

 

I have had this issue, though I have an additional problem. I have a .CSV with data qualifiers because one of the fields can contain multiple commas. Has anyone come up with a way to have flow work with the qualifiers. The default data qualifier for CSV is to place " around the field that contains the extra commas.

takolota
Memorable Member
Memorable Member

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,041)