Dealing with trailing minus signs while importing/converting text files

[rlandolt]

Hi all,

I have an automated Flow which imports space delimited text files sent via email, aggregates dollar figures from each line for an invoice total, then creates and emails a CSV file for import into another program. The data is simple, only six columns with no header, and looks like this:

38657 02/06/2021 59537 1.32 0.00 1.32

38658 02/06/2021 78535 23.10 0.00 23.10

38666 02/06/2021 NOPO 22.40 0.00 22.40

38668 02/06/2021 NOPO 16.58 0.00 16.58

38702 02/07/2021 NOPO 138.42 0.00 138.42

51390 03/05/2021 1075/1001 449.00- 0.00 449.00-

The problem is now I'm getting negative values, which are valid, but the minus signs are trailing and that makes my aggregate function crash because it's type text instead of float, and the final generated CSV would have to be fixed manually before its sent to the next process.

I'm wracking my brain trying to come up with some kind of replace expression or string function that will move the minus on the fly when it's encountered, either en masse when the file is brought in as a big text field or when its split line by line into an array for processing. I'm also thinking maybe I could separate the rows with negative values, strip the minus signs out and then append them back in one at a time with the minus signs in front of the values.

Any suggestions?

Thanks!

[Paulie78]

Try starting with something like this:

if
(
	contains(outputs('number'), '-'),
	mul(float(replace(outputs('number'), '-','')), -1),
	outputs('number')
)

Explaination:

• If the number does not contain a minus, then just output the number.
• If it contains a minus then:
  • Remove the minus sign and replace it with nothing.
  • Convert the now positive number to a float.
  • Multiply the float by -1, to make it negative again. 

Replace outputs('number') with whatever value you have.

[Paulie78]

This is a more simple expression, it just moves the minus sign to the front, if it is found:

if
(
	contains(outputs('number'), '-'),
	concat('-',replace(outputs('number'), '-','')),
	outputs('number')
)

[rlandolt]

Hi Paulie, thanks for the quick response. This is brilliant and works perfectly for my aggregate function! Thank you!

I'm still trying to work out how to create the CSV but this has given me some ideas. Again, I'm taking the data as it comes in as a big text field, swapping the spaces for commas, and saving the results as a CSV file. I'd like t do some massive creative replace function for the entire file, but maybe what I need to do is set up an Apply to Each for each line of the file and build the csv file line by line with a variant of the expression you gave me.

Thanks again!

[Paulie78]

What you could to do is watch this nice video I done on the subject: 

https://www.tachytelic.net/2021/02/power-automate-parse-csv/

You just need to split on a space instead of a comma in the apply-each section.