Answered

Convert values to specific number format while generating CSV table

(0) Share

Report

Posted on by Michal_O

Hello Everyone!

I am pretty new to this forum, and I need your help. I am building a flow to "convert" Excel xlsx file into csv file. The thing that I am struggling with is how to get specific number format for a column within csv file that I generate?

I am having an excel file on sharepoint with numbers like this:

and that's exactly the format I want to keep in my csv file. When I build my csv file "somehow" I get this:

So the space is removed and I only see a full number. I am not Power Automate expert and I am building majority of the stuff in "just build & try" way, and I tried to find a solution in google, and some other forums, and still can't get it work. If I would open the xlsx file in excel, and then save it as csv myself locally - I would get exactly what I need, but that's the case - I want it to be automated.

my flow looks like this:

And then of course create file action with output from Create CSV table

Column 15 is the one containing only numbers which I want to be in specific format when it's in CSV.

Any help with this? Thank you!

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

v-bofeng-msft on at

Like (0)

Report
Hi @Michal_O ,

Please try:

formatNumber(item()['Column15'],'0 000')

Best Regards,

Bof

Was this reply helpful? Yes No
Michal_O 8 on at

Like (0)

Report
Hey @v-bofeng-msft, thanks for quick reply! Unfortunately it didn't work, as I get following error:

The execution of template action 'Create_CSV_table' failed. The column values could not be evaluated: 'The template language function 'formatNumber' expects its first parameter to be an integer or a decimal number. The provided value is of type 'String'. Please see https://aka.ms/logicexpressions#FormatNumber for usage details.'.

Which I don't entirely get as this is the output of Column15 values (which I added just to see what's in there):

So I guess that when rows are listed, and I get values of column 15 - it's stored as string, and I just can't convert it to numbers? How it is that for some people doing exactly the same thing with
formatNumber(item()['Column15'],'0 000')
it works, and it's recognized as numebr and for me it's not...

Is there anything else I can do to make these numbers and format it in the way I want?

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft on at

Like (0)

Report
Hi @Michal_O ,

Please try:

formatNumber(int(item()['Column15']),'0 000')

Best Regards,
Bof

Was this reply helpful? Yes No
Michal_O 8 on at

Like (0)

Report

@v-bofeng-msft - You are a star! Thanks for pointing the right direction :). I am almost getting what I wanted but there is just one more issue. To make it work better I used this expression:

formatNumber(int(item()['Column15']),'# ###')

When I open the CSV file in excel, I get what I wanted:
Seems to be ok, but when I open the file in text editor (like KED) to see CSV file structure I get this:
So when it's 4/5/6 digit number it's all good, but when it's less it seems it's putting an extra space at the begining of each number - what I don't want :/. The result which I need must look like this:
So I am almost there, but I need to get rid of this extra space " " from the begining of 3 digit numbers

Was this reply helpful? Yes No
Michal_O 8 on at

Like (0)

Report
Ok, it seems that I got it working :). At least it seems so considering how numbers look like.

So the expression I used is this:
if(greater(length(item()['Column15']),3),formatNumber(int(item()['Column15']),'# ###'),formatNumber(int(item()['Column15']),'###'))
Basically to check if vlaue is 3 (less than 1000) digits or less then format should be ###, if it's greater than 3 (more than 1000) then it should be # ###.

Result in the file looks fine in both Excel & KED:
No not needed spaces or anything like this :).

Thank you @v-bofeng-msft fot your help and pointing me into the right direction!

Was this reply helpful? Yes No