We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Write the String (double value) as numeric value(2 deciaml point) in the Excel sheet



  • Avatar

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

    12.03 -> 12.03

    01.03-> 01.03



    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.


    Comment actions Permalink
  • Avatar
    Adrian Williams

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




    Comment actions Permalink

Please sign in to leave a comment.