CODEWITHSUNDEEP
azureu-sql
screechOwl
screechOwl

Reputation: 28159

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;

Upvotes: 2

Views: 4433

Answers (1)

wBob
wBob

Reputation: 14379

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.

Upvotes: 2

Related Questions