How to parse a CSV file with Power Automate
HTML-код
- Опубликовано: 7 сен 2024
- Demonstration of how to use Power Automate to parse a CSV file and convert it to a JSON array. Full details and export of the flow on my blog at www.tachytelic...
Check out my latest CSV parsing video where I show you the easiest, fastest and more secure way to Parse CSV • The ULIMATE Solution t...
This is amazing, i have been hitting my head against a wall to convert a csv into a complex json, i tried several methods that ppl used, it would work at first, but after couple of times, it would break, this fixes everything.
Really glad it helped you, but have you watched my updated version?
@@PaulieM Where is the updated version? (I can’t find it and really appreciate your contributions.)
Hi Paulie! This is gold! Thank you for taking the time to make the video and explain each step so clearly and thoroughly!
You're very welcome!
Paul, you're the best! Thanks a lot. Your tricks and experience bringing it to another level. Now it's so much easier and fun to work on daily tasks in PA. I learn a ton from your videos. It is always detailed and comprehensive, easy to follow the logic and adopt for everyday use. Thank you for doing it and sharing the knowledge with the community!
Thank you! Such a lovely comment. Pleased to hear it’s been helpful to you.
Excelente explicación. Muchas Gracias !!!
Awesome Paulie! Exactly what I was after. :)
Jesus Christ on a bicycle... You sir are an absolute god! Thanks for this one!
That was a good example and really well explained. Thanks.
You sir are an absolute legend!
Great Thanks!!!
Great video Paul. You're a Flow master!
As always this was so helpful, thanks a lot for sharing
absolute goat thankyou so much
Absolute hero.
Thanks, man, it is a great explanation. But, could you please continue to show how to use the data you had got in JSON one array.
thanks bro, you saved my life (and my brain)
My workflow is almost working as expected.. The only issue is my dataset has a comma in the body text. So it's splitting where it shouldn't.
Great video, covered exactly what I needed, many thanks sir!
Excellent - glad it helped - now check out my other CSV video which is even easier and faster!
Thank you so much!! It would be great to get a step to import csv data to dataverse table!!!
Ty, Great Video!!!
Very helpful vide. Can you please make a video on how to fetch tabular items with all its details (item code, description, quantity, price etc) from an already trained pdf invoices model and put them into json?
Hello Paulie!
Thank you for creating this excellent tutorial!
If you are still checking the comments of old videos, I would love some help please:
In your example, you composed your own CSV data in step 2.
In my scenario, I am using the “Get file content” action to bring the .csv file to the Flow.
Here is the problem, after the SKIPLINE action, each line gets the
in the end.
So, in the end of the flow, I add the values to an Excel Table. All items in the array will work except the last one, which contains the
.
It will even show as as INPUT, but does not show in the OUTPUT.
Any ideas how I can get rid of the
?
Thank you very much!!
Yes, check the blog post, there is a section which explains why this happens and how to fix.
It was really helpful, thanks a lot
well done
Nice, thank you
Wow that’s awesome! Wondering how I might be able to adapt this to a csv that is delimited by tabs, any idea? (Thinking maybe convert to uri and then use a replace to replace the uri code for tab to the Uri code for semi colon (my data has commas so can’t split on that) then convert back to regular text)
At 4:33 simply split by a tab instead of a comma:
split(item(), decodeUriComponent('%09'))
@@PaulieM excellent thank you!!!
Hey, i had a question,
at 04:30 in the splitByComma step, we use the "," to split the row
but if i have a csv with tab as delimiter (.tsv), what should i do ?
Instead of the comma in single quotes, use the expression: *decodeUriComponent('%09')* . This will produce a tab character and enable you to split on a tab instead.
@@PaulieM Thanks!
hi paul. awesomeness! thank you. one issue i'm having that's driving me a bit nuts... after the parseJson step, I'm then going to Excel and first doing a List Rows Present and then conditionally either updating (if exists) or adding (if doesnt). the issue i'm having is that in your earlier splitByLines step, it also adds a new line after the last record which is then an empty row in the xls and causes then a failure of that update/add step. is there some way to stop that happening - maybe in splitByLines do while or do until; or later splitting to remove the last line?
I’m sure I corrected this in the updated video. But I would just filter the output of splitbylines to remove any array entries with a length of 0
Hi, Paulie! Thanks for this very helpful video. I am new to using power automate and was able to parse csv by following your tutorial. However, I need compare the data in the csv file to the SPO list then update the list with that csv data (most of the data are person/people in SPO wherein the list item ID will be utilized). Is this possible and would you be able to create a tutorial on this as well?
Thanks for the video can you tell me how to download the output again so it will be more helpful else we have to again and run the the test to see the output .... please please help me
Great Video! you got me 90% of the way there. However when I add the last step in I get an error "Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Compose' at line '1 and column '2268' cannot reference action 'JSON'. The action 'JSON' is nested in a foreach scope of multiple levels. Referencing repetition actions from outside the scope is supported only when there are no multiple levels of nesting.'.'." I am not sure what I am doing wrong. It gets angry when I try to compose something outside of the loop. Any help would be appreciated. Thanks again for the video.
You’re almost there, it’s just that you have ended up with two apply to each loops. If you are working with a normal CSV that shouldn’t have happened. Happy to help you out with it if you get in touch via my blog.
you can use a variable and use append to array inside the loop.
Hi Paulie! i tried to replicate this flow....but mine is extracting the data what is already set in the CSV stage of the flow. so each time when i trigger new email, its only extracting the data what is already set at CSV stage....any idea on how to overcome this...
Thanks for this Detailed example. Using Power Automate I am doing following operations as per requirement.
Trigger condition : When email trigger with csv attachment
- In .CSV file there is no empty row in between content
- Using select operations mapping key and value pairs.
But after execution of select operation , in output I am seeing empty value.
Is there any value to restrict empty row value check in select operation
"From" option dynamically?
Also Parse JSON is failing due to empty , so I added "null" value in Parse JSON scheme.
After adding this value , I could not see return values(Name,Date,Email) from csv
Kindly help.
This is awesome. Thank you for this great video. I am wondering if there is a way to benefit from the attachment control in PowerApps to pass a CSV file from the attachment control to Power Automate and parse the file as you explained in the video! I think it would be great.
Hi, yes - it should be straightforward. You could even pass it back to PowerApps as a JSON and view the results.
@@PaulieM Can you please guide me through this or provide me with a link? I am new to this and would love to learn about it :)
Any guidance on how to het CSV from file?
Hi Paul, my csv file contains the comma on one of the name column(e.g Last Name, First name) so when using splitByComma expression it would affect that too and I don't want it. Do you have any suggestion about this? Also the csv is a large file which has all of the employees information including blank space in the column.. Thank you.
My csv file also having 25 line row header.
Hiee great video! that’s really helpful.but facing an issue showing null values in the json as the csv contains null and unable to proceed further,do we have solution for this.Please help me with this I would be great!
Maybe a silly question, but I have a value in my csv that has a comma inside it , ex. "Big Company Inc., Ltd." - how would you recommend handling this?
Remove those commas before converting the CSV file to JSON?
@@johnfromireland7551 lol ended up being simple enough. Found a solution via standard actions posted on the community forums that would split the CSV into an array variable against the double quotes. From there iterating through every other "odd" row in that array using a condition, mod(), and incrementing integer variable, replacing the comma in the "even" values only - Do Until the incrementing variable matches array count. From there appending to a string, effectively "rebuilding" the CSV dynamically without commas in those values before sending through Paulie's flow
@@alterdom I suppose it is bad practise for there to be commas within a text string? Would there be any complaint, from your customer, if you simply just removed all the commas from their data set? There would, then , not be any need to handle the commas. Keep up the good work! Remember if all else fails (with Power Automate) Excel Office Scripts can come to the rescue! Have a look at my RUclips Channel to learn more about Office Scripts : ruclips.net/p/PL2nG2TcT_3Z6pQniv9tSOdPOTbRCD8Eg4
@@alterdom Hi there, would you be able to link me to that forum? im trying to find a solution to this myself. Thanks
@@curtistheturtis 👋 Hello! Here is the link: powerusers.microsoft.com/t5/Building-Flows/How-to-split-amp-parse-CSV-file-with-comma-inside-a-value/m-p/908676/highlight/true#M127948
It piggy backs off a solution (also linked within the post) that splits the CSV into an array, my addition will use that split CSV array and some other actions to remove commas on every "even" condition, creating a "clean CSV" that will flow nicely into Paulie's flow. Feel free to message me on the forums if you need more detail!
gold
How to convert csv in excel to excel xlsx file .don't have premium license.Thank you in advance.hope there is possible solution.
need help I have followed the exact steps but getting an error : The schema validation failed. The output which I used to generate sample schema also has null values
Can you put a few lines of your CSV on pastebin? Have you checked blog post? It has some more detailed information
Hi thanks for the video!
Is it possible to split the json by each element, so i can write the lines in an AzureSQL?
Yes, but you don't really need to. You can just use the SQL connector in Power Automate and use the output of the JSON directly in that connector. Can you clarify what you mean a little more?
@@PaulieM Yes i saw that. But in the SQL there existing columns that i need to fill with the json-data. And in Power Automate i can only select the hole value and not the dynamic records of the json.
I hope it is written understandably :)
@@dalebrunner3284 use the get in touch form on my blog and we can figure it out together.
Add a parse JSON step and you will be able to select the values that you can put into your SQL fields.
For some reason a '/' is added after new line and split new line
I am pretty sure if you had used the PARSE JSON connector it would have saved you perhaps 3 or even 4 steps??
I’d be interested to see where. There’s no JSON in the flow to parse. What are you thinking?
@@PaulieM Hi Paulie, Let me put my example Flow on Github to show you. I will post the link to it here. I used the "Select" connector also.
@@johnfromireland7551 I guess you mean like in this other video I did? ruclips.net/video/sXdeg_6Lr3o/видео.html
What if your data has commas in it? How do you ignore the commas in quotes?
It’s tricky for sure. It’s better to use a data flow or a 3rd party tool in my opinion. But it is possible to deal with it using this flow, but it makes it much more complicated.
@@PaulieM I found a way. Basically you have to replace all the delimiting commas with another character. I used a pipeline and it was smooth sailing after that
@@M9xerwell done. That method is good. But sometimes you have quotes within quotes which is the problem. But in your case it all sounds fine.
Well done!
no way to actually get the csv file into the system? seriously?
How do you mean? Which system are you trying to get them into?
@@PaulieM Well, from the front end on a canvas app. I see an import control, but no documentation on it I think. then I need to parse the csv file and save it in a temporary data structure like a table or something. thanks.
@@user-nl3ui4nn4d yes, you’re right. You can do it directly in powerapps using the connector that I made:
ruclips.net/video/QbqwQuvcMnA/видео.htmlsi=ia_ZZMlUXSEPxDPE
@@PaulieM lol, ok , it's paid.
You can do it for free with the flow I created here, if you want to send the data back to powerapps you can do that.