Showing results for 
Search instead for 
Did you mean: 
Advocate IV
Advocate IV

Looking for a way to transform CSV files and upload to CDS



I work for a college.  Our students take exams in 3 different applications.  We get the students results by downloading csv files from the applications (web based apps).  Of course, the 3 csv files don't have the same format.  We want to upload all the results in a standard entity in our CDS environment.




  • I created a solution in PowerApps. (By the way, I don’t use any model nor canvas apps.  Only some dataflows created in a solution and uploading data in the local CDS database)
  • I created 3 dataflows that get a csv file as input and uses Power Query steps to transform it to fit our standard entity for test results.
  • It works fine but there’s a lot of manual interaction with very low value added SO I’m thinking it a good scenario for Power Automate


Eventually I want to use UI Flow to automate the download but I’m not there yet… to be continued.

What I’m really having trouble with is to automate the transformations currently done with Power Query.

I want to create a Flow that triggers when a csv file with the students results in drop into a folder in Teams (SharePoint)


I tried …


  • I thought I could call the dataflows I created in PowerApps from a Flow triggered when a file is created in SharePoint -> FAILED !
  • Since I can’t use my dataflows can I at least export and reuse the M code I did?  I found that is it ONLY possible with the SQL connector. ☹ FAILED !
  • I was very happy to learn that CDS has a new SQL endpoint… I tried for days but I never was able to connect to CDS with the SQL connector available in Power Automated (note that the endpoint works fine when I use SSMS to connect to it.) FAILED !


The ugly solutions …


I’m thinking about using Excel as a Power Query engine.  The first triggered Flow would open Excel, and with a VBA macro (I guess) it would run a Power Query to transform the file and save a new output file into another SharePoint … which would triggered a second Flow that would read this file line by line and use the CDS connector to insert one row at the time into the CDS entity.  I heard there’s some kind of Scripting for Excel online too… that might be an other option… but still ugly!


OR, I can use a bunch of variables, arrays and buildin sting fonctions to replicated the transformation I did with Power Query directly in Flow... can I ?


I already lost a week and a half on this attemps at Power Automate and I’m starting to hate that stuff! ☹  I think I need support from the community!  I need suggestions to enlight me!


So I’m asking this question:  

How would YOU automate the transformation of 3 different cvs files and upload them in an entity in your organisation’s CDS environement ?


Thank you! 


Hello molegris,


I can't say that I have ever tested this in my environment, but maybe one of these articles can help you out.  If not, it's possible someone else in the community may have a better idea.



Hi Ross,


Thanks for your reply.  I found some part on the answer in the first link you suggested.  However, I was forced to go with the "ugly" solution of using hudge fx_expressions to transform the data.  I'm looking forward to a better integration of Power Query 🙂



Super User
Super User

@molegris @RossB 

If anyone wants a CSV reader / parser using only the standard actions in Power Automate that can also handle in-data commas and in-data line-breaks, then try this template flow:

Helpful resources

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (4,293)