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

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
Highlighted
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. 

View solution in original post


All Replies
Highlighted
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. 

View solution in original post

Highlighted
Five Stars

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

That worked!! Finally!

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog