Formatting Currency and Numbers

Get help with installation and running here.

Moderators: DataMystic Support, Moderators, DataMystic Support, Moderators, DataMystic Support, Moderators

Post Reply
othni
Posts: 34
Joined: Tue Mar 01, 2005 1:47 am
Location: San Juan, Puerto Rico
Contact:

Formatting Currency and Numbers

Post by othni »

I have a CSV file that have some currency fields.

These fields do not have the dollar ($) sign or comma (,) for separating thousands.

I want to be able to tell TextPipe to format those numeric fields with currency symbol and thousands separator (comma). And also would be nice to have the option of specifying decimal places, rounding, adding or removing sign, etc. in a filter for number formatting.

Is this something that can be done another way right now?

Thank you

Othni
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Formatting Currency and Numbers

Post by DataMystic Support »

Hi Othni,

Use a CSV field restriction (Filters\Restrict\Delimited fields (CSV, Tab, Pipe, etc)) to restrict to just the content of the field you need.

Inside this filter, add a Filters\Add\Left margin filter with text of '$'.

As for the commas, the best approach would be to reverse the field, add a comma every 3 characters, and then reverse it back (and then add the $ sign).

To reverse a field, use Filters\Special\Reverse each line
Regards,

Simon Carter, https://www.DataMystic.com
https://www.JadeDiabetes.com - Insulin dose calculator for Type 1 diabetes
https://www.DownloadPipe.com - 250,000 free software downloads
othni
Posts: 34
Joined: Tue Mar 01, 2005 1:47 am
Location: San Juan, Puerto Rico
Contact:

Re: Formatting Currency and Numbers

Post by othni »

Thank you for the quick response.
othni
Posts: 34
Joined: Tue Mar 01, 2005 1:47 am
Location: San Juan, Puerto Rico
Contact:

Re: Formatting Currency and Numbers

Post by othni »

Ok, I was not able to find how to insert a comma after each three numbers.
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Formatting Currency and Numbers

Post by DataMystic Support »

Use Filters\Convert\End of line characters
- set the input to 'Fixed length: 3'
and set the output to a comma.
Regards,

Simon Carter, https://www.DataMystic.com
https://www.JadeDiabetes.com - Insulin dose calculator for Type 1 diabetes
https://www.DownloadPipe.com - 250,000 free software downloads
Post Reply