Write the String (double value) as numeric value(2 deciaml point) in the Excel sheet
Hi,
I have a input data with the below values as string, I want to write the data into excel file as numeric with 2 decimal point.
Input Data(String) > Excel file value (Numeric with 2 decimal point)
Also I need to know the conversion of string to double.

Not all floating point numbers can be expressed exactly so you cannot always guarantee to have two decimal places for a all numbers. You can round numbers to two decimal places and that will be approximately the value you want. You can output the number of cents as an integer value and then (within Excel) divide this by 100 as part of your formula.
Below are some options for generating string, float (double) and integer representations of floating point numbers using the Transform node. The input data is in the 'Data' field.
Which produces the following results when viewed in the Analyze Data Viewer:
When the data is output to Excel, the corrsponding data is shows as:
Note that the 'Data' field and the 'Decimal_String' columns are formatted in Excel as strings containing numeric values.
Also note that in the process of formatting a float value to a string with a specified number of decimal places, the number has been rounded implicitly (see rows 8 and 9 above).
The code used in the Transform node scripts are:
#### ConfigureFields
#Configure all fields from input 'in1' to be mapped
#to the corresponding fields on the output 'out1'
out1 += in1
out1.Numeric_value = float
out1.Rounded_value = float
out1.Decimal_String = unicode
out1.Cents_Value = int#### ProcessRecords
#Copy all fields from input 'in1' to the corresponding output fields
#in output 'out1'. Copies the values of the fields that have been setup
#in the mapping defined in the ConfigureFields property
out1 += in1
if in1.Data is Null:
## Defaults for missing data
out1.Numeric_value = Null
out1.Rounded_value = Null
out1.Decimal_String = ""
out1.Cents_Value = Null
else:
NumVal = float(in1.Data)
out1.Numeric_value = NumVal
out1.Rounded_value = round(NumVal, 2)
## Format the value as a string with 2 DP
out1.Decimal_String = "{0:.2f}".format(NumVal)
##Output the number of cents as an integer
out1.Cents_Value = int("{0:.0f}".format(100 * NumVal))
Please sign in to leave a comment.
Comments
2 comments