cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Elowy
Resolver I
Resolver I

How to Create Excel from CDS records

I feel like I’m running around in circles… I hope somebody can help.

What I’m trying to do is collecting data from CDS and mail this to the user. How difficult can it be…

First I tried creating a CSV file with the “Create CSV” action. Unfortunately this only creates American style CSV’s (semicolons instead of commas as data separator and commas as decimal separator) and these cannot be opened (or converted) in my clients environment.

So now I try to create an Excel file using the Excel for Business actions. I use an existing Excel file with the right table in OneDrive. That file is altered for each flow run (delete all rows and add new rows):

 

  1. Gather previous data rows from Excel file
  2. Delete previous data rows
  3. Add new data rows
  4. Mail the Excel file

Steps 1 and 2:

Excel1.png

Unfortunately this does not work because there is a strange caching issue in step 1. The results of step 1 are not correct in most cases. They seem to be the output of the previous run or so.
Another problem with this solution is that only one instance of the flow can run, because I use one Excel template. And because the flow takes a long time to run, this might be an issue in the future.

So I tried to copy the empty template Excel first. So I don’t have to delete the rows first, and I can run multiple flows at the same time:

 

  1. Copy template file to filename+guid
  2. Add new data rows to new file
  3. Mail the Excel file

Steps 1, 2 and 3:

E2E2

Unfortunately this doesn’t work as well. I cannot use a dynamic generated filename in the “Add a row into a table” action.
I also tried to use the file ID that comes from the OneDrive copy action, that doesn’t work as well..

 

So I’m lost…

Should it be so difficult gathering data and sent it to the user per mail?

Hope somebody can help.

Thanks a lot,
Elowy.

 

 

 

5 REPLIES 5
abm
Super User
Super User

Hi @Elowy 

 

Could you please explain what you meant by American style csv?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

I guess the default CSV standard is always "American". But in Europe we use semicolons instead of commas as data separator, commas as decimal separator and dots for the thousand separators.

Hi @Elowy 

 

Thanks for your quick reply.

 

In my knowledge csv format is always comma separated by default. Of course you can have custom rules like you mentioned to interpret the way you want.

 

So if you have specific rules use the expression to convert to your own format.

 

image.png

 

The above replace expression is as below.

replace(item()?['name'],',','.')
 
Here I am replacing comma to decimal like you mentioned.
 
Finally once all done you have a comma separated csv file.  If you want to change the comma to semi-colon then use a replace expression in a string or compose statement.
 
Definitely it is possible to achieve custom CSV formats via Power Automate.
 
Thanks
 
 


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Hi abm,

 

Because we use commas as decimal separators in Europe, CSV files are mostly semicolon separated. And Excel wont open the American formatted CSV well without some conversion steps.

 

Just replacing commas with semicolons for the field separator isn't a solution for us unfortunately; all comma's within text fields (and decimal separators) will be changed as well. And text fields with semicolons in it will not get quotation marks.

 

Thanks,

Elowy.

Hi @Elowy 

 

Thanks for your quick reply.

 

If you have multiple replace you need to extend the replace with multiple replace expressions.

 

Like you mentioned new excel filenames won't be dynamic.  Also iterating and deleting can cause some performance issue depending on how many records you are dealing with.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

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.

Users online (1,862)