You’re offline. This is a read only version of the page.
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!
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:
Share
Report
All responses (
Answers (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!
@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:
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
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?
#1
WarrenBelz
146,745
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,091
Most Valuable Professional
