Data360 Analyze

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.

Input Data (String)  -> Excel File (Numeric)

12.03 -> 12.03

01.03-> 01.03

23.45->23.45

The reason that I'm looking to write the date into Excel as Numeric to apply the excel formula once the output file created by the Dataverse.

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))