Description:

The Transform String to Number step is an ETL transformation component that converts one or more string-based fields into numeric values.

Many enterprise systems exchange numeric information as text. Examples include CSV files, Excel workbooks, XML documents, JSON payloads, legacy applications, and third-party integrations where values such as prices, quantities, percentages, invoice amounts, or account balances are stored as strings. Before these values can participate in mathematical operations or be written to numeric database columns, they must be converted into appropriate numeric formats.

The step is configured through a transformation list that defines the columns requiring conversion and the corresponding number formatting rules.

During execution, the ETL engine processes each incoming record, evaluates the configured transformation mappings, converts the selected string fields into numeric values, and forwards the transformed dataset to the next ETL component.

The Transform String to Number step preserves all non-selected columns while converting only the configured fields.

The step supports:

• String-to-number conversion
• Multiple column transformations
• Configurable transformation mappings
• Numeric data preparation
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

Typical business uses include:

• Financial data imports
• Sales amount conversion
• Inventory quantity processing
• Payroll calculations
• Budget analysis
• ERP integrations
• Business intelligence preparation

Usage:

The Transform String to Number step is typically positioned after an ETL input component and before calculations, aggregations, validations, or database output components.

During execution:

1. The incoming ETL dataset is received.
2. The configured transformation list is evaluated.
3. Selected string columns are converted into numeric values.
4. All remaining columns pass through unchanged.
5. The transformed dataset continues to the downstream ETL components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Importing sales values from CSV files
• Converting invoice totals stored as text
• Preparing inventory quantities for aggregation
• Transforming payroll figures before calculations
• Preparing financial values for reporting
• Loading numeric business data into SQL Server
• Normalizing imported datasets before analytics

Once converted, numeric values can participate in mathematical calculations, sorting, filtering, grouping, averaging, and reporting.

Prerequisite:

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

Example:

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

• Create a new ETL definition called “clsTransformFormatStringToNumberDef” and open the definition in designer mode. 
• Drag “clsInputCSV, clsTransformFormatStringToNumber, 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 "clsTransformFormatStringToNumber" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the transform list, then click the Save button. 

• Click the "clsTransformFormatStringToNumber" step to configure the transform list. The configuration pop-up window appears. Click the Add Row (+) button to insert an empty row. Select the string as the input source column and “integer or value” as the output target column. Click the Save button. You may add multiple transformation rows 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 and specify the variable or global reference to hold 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 “clsTransformFormatStringToNumber” step should transform the string into a value and store it in the output file. The variable or global holds the output file’s virtual path as shown below. 

Tips:

• Configure transformations only for columns that contain numeric string values.
• Validate imported data before performing numeric conversions.
• Ensure that source values use consistent numeric formatting.
• Test transformation mappings with representative production data.
• Perform numeric conversion before calculations, grouping, or aggregation.
• Keep transformation mappings organized when processing large datasets.
• Combine this step with CSV Input File, Excel Input File, Validate Data, Group By Transform, Aggregate Transform, Sorting Transform, MSSQL Output File, or CSV Output File to create complete enterprise ETL workflows.

Notes:

• Only the columns defined in the transformation list are converted.
• All other columns remain unchanged.
• Invalid numeric values may cause conversion failures during ETL execution.
• The transformation list should match the incoming dataset schema.
• Because this is an ETL transformation component, it requires both an incoming and an outgoing connection.
• Conversion errors and processing statistics are recorded in the ETL execution log.
• This step is commonly used in financial processing, inventory management, ERP integrations, customer imports, business intelligence, reporting, and enterprise data migration projects.

Transform String to Number vs. Transform Number to String:

Both components perform format conversions, but they operate in opposite directions.

As a general guideline:

• Use Transform String to Number when imported numeric values are stored as text and must participate in calculations or be stored in numeric database fields.
• Use Transform Number to String when numeric values need to be formatted as text for reports, exports, or external integrations.

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:

• Transformation mappings
• Source column names
• Numeric conversion requirements
• Upstream input configuration
• Downstream output configuration
• Validation rules

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

Click here to download the sample file.