topic How to replace and filter blank cells from a column in a csv file in Talend Studio

How to replace and filter blank cells from a column in a csv file

Anonymous — Sat, 16 Nov 2024 13:52:31 GMT

Hi!
I have a csv file from that has a string column. At some places it is blank, so how can i replace these blank columns with the value "NULL" ? I tried StringHandling.EReplace (row20.Name, " ", "NULL") But it doesnt take blank for " ".
I am also trying to filter these NULL columns by putting expression filter row20.Name!="NULL" in tMap. But it doesnt seem to work.
How can I do this?
Thanks!

Re: How to replace and filter blank cells from a column in a csv file

Anonymous — Tue, 14 Jul 2009 10:16:55 GMT

Hi,
if you want to check for NULL value you have to write row20.Name != null , without "". If you write row20.Name != "null" you are looking for the actual word null. (well assuming you use java, row20.Name != "null" wouldn't even work, you'd have to use row20.Name.matches("word") instead)
You also have the option to trim your CSV file (advanced settings, tfileinputdelimited) which removes the blanks.

I hope that helps!
Greets

Re: How to replace and filter blank cells from a column in a csv file

Anonymous — Wed, 15 Jul 2009 10:22:24 GMT

hi,
try

row20.Name == null || row20.Name.length()==0 ? "null":row20.Name

hope it'll help you
++

Re: How to replace and filter blank cells from a column in a csv file

Anonymous — Wed, 15 Jul 2009 13:07:37 GMT

It works.. Thanks!!

Re: How to replace and filter blank cells from a column in a csv file

Anonymous — Fri, 26 Aug 2011 09:24:19 GMT

Hi,
I want to know how to convert the blank data into null?
I am loading data from a file into MySql through some validations.
I'm using tMySqlOutput component.
For some columns, they don't have any data, so, it is just "" for such columns.
and when it was written into MySql, this data went into as blank and not as null.
Is there any way to convert to null values???
Thanks...

Re: How to replace and filter blank cells from a column in a csv file

alevy — Sat, 27 Aug 2011 09:42:25 GMT

In tFileInputDelimited just set the Default value for the column in the schema to null (not "null"). Wherever it then doesn't find a value it will be replaced with a null.

Re: How to replace and filter blank cells from a column in a csv file

Anonymous — Mon, 29 Aug 2011 04:11:04 GMT

Hi, Alevy,
Thanks for the information,
but I've tried to put null for the default values, but
the value doesn't change.
it is still left as a blank.

Re: How to replace and filter blank cells from a column in a csv file

alevy — Mon, 29 Aug 2011 04:30:29 GMT

It works fine for me...