strange data type Conversion problem RRS feed

  • 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:


    Create the output:


    @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 =
            recBillLocationInvoiceProduct["ID"]  AS BillLocationInvoiceProductId,
            recBillLocationInvoiceProduct["DetailCost"]  AS DetailCost
        FROM @jsonifyBillLocationInvoiceProduct;
    @InBillLocationInvoiceProduct =
        SELECT (int) Int32.Parse(BillLocationInvoiceProductId) AS BillLocationInvoiceProductId,
        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