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.
Solved! Go to Solution.
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 :
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)
5. use a select on the result and skip element 1
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 :
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
Try these two videos I did:
https://youtu.be/sXdeg_6Lr3o
https://youtu.be/0RqSco9Til0
Blog post:
https://www.tachytelic.net/2021/02/power-automate-parse-csv/
all very easy.
@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!!!!
Yes, I can, but it will have to be tomorrow. Bedtime for me in the United Kingdom 🇬🇧
No worries! Good Night!! I'll wait for your solution! And Thank you Again.
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 :
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)
5. use a select on the result and skip element 1
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 :
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
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:
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.
Please let me know if I am doing wrong at any steps. Thank You again.
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
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 '])
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...