Search code examples
u-sql

Getting error with basic trim() function in U-SQL script

I want to apply .trim() function on a column but getting the error. Sample data: Product_ID,Product_Name 1, Office Supplies 2,Personal Care

I have to do some data manipulation but can't get the basic trim() function right.

@productlog =   
EXTRACT Product_ID string,
    Prduct_Name string
FROM "/Staging/Products.csv"
USING Extractors.Csv();
@output = Select Product_ID, Product_Name.trim() from @productlog;
OUTPUT @output  
TO "/Output/Products.csv"
USING Outputters.Csv();

Error: Activity U-SQL1 failed: Error Id: E_CSC_USER_SYNTAXERROR, Error Message: syntax error. Expected one of: '.' ALL ANTISEMIJOIN ANY AS BEGIN BROADCASTLEFT BROADCASTRIGHT CROSS DISTINCT EXCEPT FULL FULLCROSS GROUP HASH HAVING INDEXLOOKUP INNER INTERSECT JOIN LEFT LOOP MERGE ON OPTION ORDER OUTER OUTER UNION PAIR PIVOT PRESORT PRODUCE READONLY REQUIRED RIGHT SAMPLE SEMIJOIN SERIAL TO UNIFORM UNION UNIVERSE UNPIVOT USING WHERE WITH ';' '(' ')' ',' .

Solution

  • Got it right finally, in case someone else face the same issue. U-SQL is more like C# so it will be a bit tricky for people like me, coming from pure SQL background.

    Code:

    @productlog =   
EXTRACT Product_ID string,
    Prduct_Name string
FROM "/Staging/Products.csv"
USING Extractors.Csv();

@output =
        SELECT 
            T.Product_ID,
            T.Prduct_Name.ToUpper().Trim() AS Prduct_Name
        FROM @productlog AS T;

OUTPUT @output  
TO "/Output/Products.csv"
USING Outputters.Csv();