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.
@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;
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.