Replacing \N (aka NULL) yes, tha's right, capital N... not \n for newline

Highlighted
Five Stars

Replacing \N (aka NULL) yes, tha's right, capital N... not \n for newline

Hi,

 

I'm having quite a struggle trying to parse a csv file that looks like this, in which the "\N" represents a null:

"16053","961","5","optional",\N,\N,\N
"14793","969","0","optional",\N,\N,\N
"17781","1039","12","optional","12",\N,\N

tFileInputDelimited is set up as follows:

 - Row Separator: "\n"

 - Field Separator: ","

 - CSV Options unchecked.(also tried with this enabled and for escape "\\", and text enclosure "\"")

 

I've tried many ways within a tMap, none of which worked, and the latest attempt was through tJavaRow with the following:

output_row.teamID = input_row.teamID.replaceAll("\"","").replaceAll("\\N","");

 

The above throws an error that states:

Exception in component tJavaRow_1 (testReplace)
java.util.regex.PatternSyntaxException: Illegal/unsupported escape sequence near index 1
\N
 ^
	at java.util.regex.Pattern.error(Unknown Source)
	at java.util.regex.Pattern.escape(Unknown Source)
	at java.util.regex.Pattern.atom(Unknown Source)
	at java.util.regex.Pattern.sequence(Unknown Source)
	at java.util.regex.Pattern.expr(Unknown Source)
	at java.util.regex.Pattern.compile(Unknown Source)
	at java.util.regex.Pattern.<init>(Unknown Source)
	at java.util.regex.Pattern.compile(Unknown Source)
	at java.lang.String.replaceAll(Unknown Source)

 


Accepted Solutions
Community Manager

Re: Replacing \N (aka NULL) yes, tha's right, capital N... not \n for newline

You're going to hate this solution because it seems ridiculous. Unfortunately you are hitting a problem with both Java and Regex escape characters. Both use \. Now when you want these expressed as literal values, you need to escape them as well. Ultimately in this scenario it means you need code like this....

 

output_row.teamID = input_row.teamID.replaceAll("\\\\N","");

Try it and you'll see. When I first hit this one, it stumped me for ages. 


All Replies
Community Manager

Re: Replacing \N (aka NULL) yes, tha's right, capital N... not \n for newline

You're going to hate this solution because it seems ridiculous. Unfortunately you are hitting a problem with both Java and Regex escape characters. Both use \. Now when you want these expressed as literal values, you need to escape them as well. Ultimately in this scenario it means you need code like this....

 

output_row.teamID = input_row.teamID.replaceAll("\\\\N","");

Try it and you'll see. When I first hit this one, it stumped me for ages. 

Five Stars

Re: Replacing \N (aka NULL) yes, tha's right, capital N... not \n for newline

That worked!! Finally!

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog