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

11 REPLIES 11
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

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,833)