Azure / U-SQL - convert string to float

I'm having some issues while loading files in Azure data lake / U-SQL. I think there might be an issue with some of the values in one of the columns (I can load them with the column as a string but not as a float) so I'd like to load them as a string, make some changes using CASE statements on the offending values and then convert the new values to floats. However, I can't find how to do this outside of an EXTRACT statement.

Below is how I'd do it in SQL.

Any suggestions?

@inputInfo = 
EXTRACT
var1 string,
var2 string,
var3 string
FROM "~/infile.txt"
USING Extractors.Text(quoting : false, delimiter : '|');

@t1 = 
SELECT CASE WHEN var1 == "-0" THEN 0 ELSE var1 END AS var1
       , var2
       , var3
FROM @inputInfo;

@t2 = 
SELECT CAST(var1 AS FLOAT) AS var1
        , var2
        , var3
FROM @t1;

1 answer

  • answered 2017-11-15 09:10 wBob

    You could try casting it, eg

    @departments = 
        SELECT * FROM 
            ( VALUES
            ("Newton",  23.00m),
            ("Susan",   25.1234m),
            ("Emma",    25.9999m),
            ("Bradley", 25.9900m)
            ) AS T(Cutomer, Balance);
    
    
    @result =
        SELECT Cutomer,
               (float)Balance AS Balance1
        FROM @departments;
    
    
    OUTPUT @result
    TO "/output/output.txt"
    USING Outputters.Tsv();
    

    If that does not work for you, please provide some sample data and expected results.