Extract Data from a CSV file

[dc23]

I have a csv file. I was able to parse through the data and now the data is in the form as per snapshot below

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

Please help.

Thanks!

[efialttes]

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

[v-litu-msft]

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]

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.

[dc23]

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!

[dc23]

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.

[dc23]

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 form

[v-litu-msft]

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.

[dc23]

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

Thanks!

[dc23 · Accepted solution]

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

Thanks for the help

[asmusz]

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.

[QuestionGuy]

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]

Change CSV Delimiter Information
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Change-CSV-Data-Delimiter-From-a-Text-Fi...

[takolota]

New CSV to Dataset template here for anyone to try:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191