cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
molegris
Advocate II
Advocate II

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

Hi,

 

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.

 

Currently...

 

  • 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! 

2 REPLIES 2
RossB
Microsoft
Microsoft

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.

 

https://powerusers.microsoft.com/t5/Building-Flows/Extract-Data-from-a-CSV-file/td-p/529944

 

https://powerusers.microsoft.com/t5/General-Power-Automate/automate-import-csv-attachment-to-common-...

 

Thanks!

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 🙂

 

regards

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (20,609)