How to Create Excel from CDS records

(0) Share

Report

Posted on by Elowy

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):

Gather previous data rows from Excel file
Delete previous data rows
Add new data rows
Mail the Excel file

Steps 1 and 2:

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:

Copy template file to filename+guid
Add new data rows to new file
Mail the Excel file

Steps 1, 2 and 3:

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.

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @Elowy

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

Thanks

Was this reply helpful? Yes No
Elowy 82 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

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.

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

Was this reply helpful? Yes No
Elowy 82 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No