NULL value in U-SQL replaces zero-length string when giving nullEscape Parameter

Question

I had used nullEscape parameter in the U-SQL query. It replaced the \N to zero-length string. Is there any way to transform \N to "\N" using U-SQL query.

Value1,(zero-length string),value2,value3,(zero-length string),(zero-length string)

I'm also having a doubt of I want to \N as well as don't need to change \N to Zero-length string in the Extractor while skiping the null values like \N?

I have tried the following but it didn't worked,
USING Extractors.Csv(nullEscape:"\N");

Sample CSV Data:

Name,,,"",Address,Pin

\N has already been replaced with Zero-length String.

wBob wBob · Accepted Answer · 2017-08-09T13:21:04

You could use a conditional expression to convert the values, eg

DECLARE @path string = "input/input81.csv";

@data =
    EXTRACT
        a string,
        b string,
        c string,
        d string,
        e string,
        f string
    FROM  @path
    USING 
        Extractors.Csv();


// Convert string \N to zero-length string for selected columns
@output =
    SELECT a,
           b == "\\N"? "" : b AS b,
           c,
           d == "\\N"? "" : d AS d,
           e == "\\N"? "" : d AS e
    FROM @data;


OUTPUT @output
TO "output.csv"
USING Outputters.Csv(quoting : true, outputHeader: true);

My input file is here and these were my results:

NULL value in U-SQL replaces zero-length string when giving nullEscape Parameter

1 Answers