none
strange data type Conversion problem

    Question

  • Hi !

    Reading a JSON file and exporting as a CSV using the JSON extractor. I thought every thing would be a string unless cast! But

    Why should the input:

    {"ID":226552,"DetailCost":-0.000066}
    {"ID":226552,"DetailCost":-0.312519}

    Create the output:

    "BillLocationInvoiceProductId","DetailCost"
    226552,-6.6E-05
    226552,-0.312519


    @InBillLocationInvoiceProduct = 
        EXTRACT jsonString string
        FROM @inputBillLocationInvoiceProduct
        USING Extractors.Text(delimiter:'\b', quoting:false);
    
    
    @jsonifyBillLocationInvoiceProduct = 
        SELECT Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(jsonString) AS recBillLocationInvoiceProduct
        FROM @InBillLocationInvoiceProduct;
    
    
    @InBillLocationInvoiceProductJSON =
        SELECT
            recBillLocationInvoiceProduct["ID"]  AS BillLocationInvoiceProductId,
            recBillLocationInvoiceProduct["DetailCost"]  AS DetailCost
        FROM @jsonifyBillLocationInvoiceProduct;
    
    
    
    @InBillLocationInvoiceProduct =
        SELECT (int) Int32.Parse(BillLocationInvoiceProductId) AS BillLocationInvoiceProductId,
        DetailCost
        FROM @InBillLocationInvoiceProductJSON;
    
    
    OUTPUT @InBillLocationInvoiceProduct TO @outBillLocationInvoiceProduct  ORDER BY BillLocationInvoiceProductId USING Outputters.Csv(outputHeader: true);
    



    • Edited by iahe Thursday, September 27, 2018 10:21 AM
    Thursday, September 27, 2018 10:17 AM

All replies

  • Hi,

      Since you are using a JSON parser, it converts into float because the value is not within double quotes. you can try wrapping the value in double quotes in your input file or use a converter to convert from float to string like below:

    Convert.ToString("DetailCost") AS DetailCost;

    string.Format("{0:N3}", DetailCost) AS DetailCost; //upto 3 decimal places

    DetailCost.ToString() AS DetailCost;

    Thursday, September 27, 2018 7:08 PM
    Moderator