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?

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.

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,285)