One Star

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

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!
7 REPLIES
One Star

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

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

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

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

hope it'll help you
++
One Star

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

It works.. Thanks!!
One Star

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

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...
Seven Stars

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

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.
One Star

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

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.
Seven Stars

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

It works fine for me...