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

Transfer Azure data lake CSV file data to SharePoint List Via Microsoft Flow

I have tried so many things and expressions in Microsoft Flow to convert that CSV file data to the Sharepoint List but I am not getting any close to that part.

 

I have a situation like this, my CSV file is in Azure data lake and I want it to convert to SharePoint List. And that CSV file is updating every half an hour. So required to update that SharePoint List every time when CSV updates. I have tried so many expressions and so many different things but data is not coming to the SharePoint List. If anyone can help me I would really appreciate it.

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

Hi @krish11 

 

One possible option is to convert this string to pipe delimited |.

 

You can do this in one long expression or using many compose actions.  So this can be tidied up but the end result is :

 

DamoBird365_0-1616756248578.png

 

Assume Compose is my original CSV

1. replace(outputs('Compose'),',,',',"",')  //replace all instances of ,, with ,"",

2. replace(outputs('Compose_2'),'",','|')  //replace all instances of ",  with |

3. replace(outputs('Compose_3'),'"','')   //replace all instances of " (double quotes) with nothing i.e. '' (single quotes)

4. split(outputs('Compose_4'),outputs('ReturnLine'))  //split the string by a return line (which is a compose with a return line in it)

DamoBird365_2-1616756592070.png

 

5. use a select on the result and skip element 1

 

DamoBird365_1-1616756453893.png

Each value of the map is split(item(),'|')?[0]  //0 is element 1 etc, split each result and choose the element by number

 

A tidy solution is :

DamoBird365_3-1616756782670.png

 

And here is the combined expression for the TidiedCSV Compose.

 

split(replace(replace(replace(outputs('Compose'),',,',',"",'),'",','|'),'"',''),outputs('ReturnLine'))

 

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

View solution in original post

12 REPLIES 12
Paulie78
Super User
Super User

krish11
Helper I
Helper I

@Paulie78 Thank You so much for your quick reply.

 

I watched all videos as you suggested. But I have something different formate in the CSV file. Here you can see the data format for CSV:

 

"id","File_name","File_description","page_id ","page_name","page_owner","page_group_Name","file_id"
"EA0A-9A77-44E2-A287-2E981AB","Infrastructure",,"3","A","","",
"96ACDA3A-410ABBEBC463D5A5","Spend","Page Owner: abc Kyle,page Group: Autoservice,","FBE6","EA_Report"," abc Kyle","Autoservice",
"EA0A-9A77-44E2-A287-2E981AB","Infrastructure",,"3","A","","",
"96ACDA3AB7CB-BBEBC463D5A5","Spend","Page Owner: abc Kyle,page Group: Autoservice,","FBE6-DAC8","EA_Report"," abc Kyle","Autoservice", 

 

Every data field has ""(double quotes) in it. So I am not able to split the column as you did in your video. Can you please suggest to me any solution for this? How can I get data out from CSV?

 

I really appreciate your help here. Please Please Please HELP ME!!!!

Paulie78
Super User
Super User

Yes, I can, but it will have to be tomorrow. Bedtime for me in the United Kingdom 🇬🇧 

krish11
Helper I
Helper I

No worries! Good Night!! I'll wait for your solution! And Thank you Again.

DamoBird365
Super User
Super User

Hi @krish11 

 

One possible option is to convert this string to pipe delimited |.

 

You can do this in one long expression or using many compose actions.  So this can be tidied up but the end result is :

 

DamoBird365_0-1616756248578.png

 

Assume Compose is my original CSV

1. replace(outputs('Compose'),',,',',"",')  //replace all instances of ,, with ,"",

2. replace(outputs('Compose_2'),'",','|')  //replace all instances of ",  with |

3. replace(outputs('Compose_3'),'"','')   //replace all instances of " (double quotes) with nothing i.e. '' (single quotes)

4. split(outputs('Compose_4'),outputs('ReturnLine'))  //split the string by a return line (which is a compose with a return line in it)

DamoBird365_2-1616756592070.png

 

5. use a select on the result and skip element 1

 

DamoBird365_1-1616756453893.png

Each value of the map is split(item(),'|')?[0]  //0 is element 1 etc, split each result and choose the element by number

 

A tidy solution is :

DamoBird365_3-1616756782670.png

 

And here is the combined expression for the TidiedCSV Compose.

 

split(replace(replace(replace(outputs('Compose'),',,',',"",'),'",','|'),'"',''),outputs('ReturnLine'))

 

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

@krish11 I've done a video for you as I think others could benefit from this scenario.

 

Please find it here

 

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

krish11_0-1616775384696.png

@DamoBird365  

I really appreciate all your help. I don't believe you created a video for my problem. Really impressive.
I did exactly all steps as you mentioned here and in the video.
But still, in the end, I'm not getting JSON format. What I'm getting is this:

 

krish11_0-1616779434228.png

 

krish11_1-1616779510356.png

This is not what you have mentioned in the previous screenshot. And I have used the same data that you used.

 

And still, I wrote JSON format by Myself and tried to add in the SharePoint List but nothing is coming in the List.

 

You helped me a lot. But I don't know what I am Missing. And flow ran successfully every time. But no content like yours.

 

And one more question I have for you what you wrote in ReturnLine? I wrote the same ReturnLine in Input.

 

krish11_2-1616779867746.png

Please let me know if I am doing wrong at any steps. Thank You again.

Paulie78
Super User
Super User

In the ReturnLine, you need just press the enter key, so it is a newline in there and nothing else.

Hi @krish11 

 

Hopefully it's just the typo that @Paulie78 has pointed out, otherwise, download this and import it as a solution - the prebuilt Cloud 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

 

 

krish11
Helper I
Helper I

Thank You for your support. @DamoBird365 @Paulie78 

 

For the first 5 raws, I'm able to convert and write it to SharePoint List.

 

@Paulie78 , @DamoBird365   Hello,

 

I am transferring This CSV file to the SharePoint List. But this is happening every half an hour and this creates items is creating NEW records every time. I don't want to repeat if that record already exists in the list. 

 

For that, I have used Get Items from SharePoint List and then compare those 2 lists. 

for Expresion in Filter query I have compare both lists unique ID column, 

equals(item()?['file_x0020_id_x0020_'], items('Apply_to_each')['file_id '])

krish11_1-1617117433515.png

 

 

But this expression is throwing an error. And the condition part I am comparing the Length of those Apply to each section.

 

But It's not working. Can anyone please suggest a better way to compare both lists?

takolota
Power Participant
Power Participant

Your query filter needs to be file_x0020_id_x0020_ eq items('Apply_to_each')['file_id '] not an expression.

Then you can use the Update item referencing that Get items within that loop.

 

 

And if anyone needs to routinely do this with larger datasets check these..,

 

CSV to Dataset Template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/m-p/1521305#M603

 

SharePoint Batch Update Template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-SharePoint-List-With-Extern...

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.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (3,773)