Page 1 of 1

Rejecting Null values during file import

Posted: Fri Jan 14, 2011 11:23 am
by romil_ds
Hi all,
my job is reading the data from sequential file and then loading it into a database.

I need to reject the record if the incoming column is null at the source sequential file stage itself.

The stage is rejecting the records if null is found in Integer type column but not for string type columns even if the column property is not nullable. by default it is replacing Null with ''(empty) value and reading the data into the job.

Can you please share if you have an example of how to reject null values for string data type columns?


the schema of the file is

record
{final_delim=end, delim=',' quote=none}
(
Col_One:nullable int32;
Col_two:string[10];
Col_Three:string[15];
)

Posted: Fri Jan 14, 2011 2:36 pm
by ray.wurlod
Set the Null Field Value property in the Sequential File stage. Filter out the rows with Col1 is null in a downstream Filter or Transformer stage.

Posted: Fri Jan 14, 2011 4:04 pm
by romil_ds
Hi Ray,

Actually I am using the schema file to read the sequential file and hence there is no column defined in the job. The record contents I have sent are of schema file. So I have the only option to reject the record during import. but It is not working for string type column.

I also tried putting the ''(empty) value for null to value property, but that options is also not doing what I want.

Posted: Fri Jan 14, 2011 4:17 pm
by jwiles
romil_ds wrote:Actually I am using the schema file to read the sequential file and hence there is no column defined in the job.
It sounds like you have RCP enabled (yes?) within Designer, so a Filter stage would work in your case, as Ray suggested. Filter does not require that the column be defined in the job metadata. Just set the Where clause as shown and dump the rejected records to a second output link if you need to capture them for auditing purposes.

If the job is used for multiple layouts, is the column name you're checking for consistent (the same from job to job)? If not, you could pass the name as a job parameter to use in the Where clause of the Filter.

Regards,