Description:

The Transform Number Formats step formats numeric values according to configurable formatting rules. The transformation is configured through the Configure Transform List property, where ETL designers define one or more number formatting rules for selected columns.

Enterprise data often requires numbers to be displayed in consistent formats before being exported or presented to downstream systems. Examples include formatting currency values, percentages, decimal precision, quantities, account balances, invoice totals, and measurement values. This transformation provides a centralized way to apply those formatting rules without requiring custom scripting.

Unlike Transform Number Operations, which performs arithmetic calculations, Transform Number Formats changes only how numeric values are presented. The numeric meaning remains unchanged while the output becomes consistent with business reporting or integration requirements.

The Transform Number Formats step is commonly used after numeric calculations have been completed and before reporting, exporting, or loading data into downstream applications.

The step supports:

• Configurable number formatting
• Decimal precision formatting
• Currency formatting
• Percentage formatting
• Multiple formatting rules
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

Typical business uses include:

• Financial reporting
• Currency presentation
• Sales reports
• KPI dashboards
• Inventory reporting
• Executive reporting
• Standardized export formatting

Usage:

The Transform Number Formats step is typically inserted after numeric validation and calculations but before reporting, exporting, dashboard generation, or loading formatted values into downstream systems.

During execution, each configured numeric column is formatted according to the selected formatting rule. The formatted values become immediately available to downstream ETL components for presentation or export.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Formatting invoice totals as currency
• Displaying percentages with two decimal places
• Formatting account balances
• Standardizing quantity values
• Preparing financial exports
• Creating executive dashboards
• Producing business intelligence reports

The formatted values can then be exported, grouped, filtered, reported, or loaded into downstream systems.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsTransformNumberFormatsDef” example.          

• Create a new ETL definition called “clsTransformNumberFormatsDef” and open the definition in designer mode.
• Drag “clsInputCSV, clsTransformNumberFormats, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” step and other steps, as shown above.
• Define a variable or a global variable to hold the virtual path. 
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps you understand the schema at a glance.  

• Click the "clsTransformNumberFormats" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns and operation type, then click Save. 

• Click the "clsTransformNumberFormats" step to configure its "Required" properties. Click the button to open the transform list configuration window. Click the Add Row (+) button to insert an empty row. Select the In column from the drop-down list. Select the number format operator. Select the Out column from the drop-down list. You may also type a new Out column name and press Enter to add it. Click the Save button. You may add multiple columns for transformation by using the Add Row button. 

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformNumberFormats” step should format and transform the numbers as configured and write them to the output file. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Apply numeric calculations before formatting values for presentation.
• Use consistent formatting standards across ETL definitions to simplify reporting.
• Match decimal precision with business and regulatory requirements.
• Verify currency and percentage formats expected by downstream applications.
• Preserve original numeric values if subsequent calculations are required.
• Test formatting rules using representative production datasets before deployment.
• Combine this transform with Validate Data, Transform Number Operations, Transform Number Range, Group By Transform, Sorting Transform, JSON Output File, or SQL Output components to create comprehensive reporting and analytics workflows.

Notes:

• Multiple number formatting rules can be configured within a single transformation.
• Source columns must contain valid numeric values.
• Formatting affects the presentation of numeric values rather than their underlying numeric meaning.
• Successfully formatted values immediately become available to downstream ETL components.
• Invalid formatting rules or column mappings may result in ETL execution errors.
• This transform is commonly used for financial reporting, invoice generation, ERP integration, CRM reporting, business intelligence dashboards, regulatory reporting, inventory management, and enterprise analytics.
• Review downstream reports whenever number formatting standards are modified.

Transform Number Formats vs. Transform Number Operations:

Although both transformations operate on numeric data, they are intended for different purposes:

As a general guideline:

• Use Transform Number Formats when numeric values need standardized presentation for reporting or export.
• Use Transform Number Operations when numeric values must be mathematically calculated or adjusted before further processing.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Number formatting rules
• Source column mappings
• Formatting patterns
• ETL schema validation
• Downstream transformation mappings
• Destination component configuration
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.