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
Sixteen Stars

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
Highlighted
Sixteen Stars

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!

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.