[resolved] Null column in Databse

[AbiJeev]

Hi ,
  I have nullable columns in database(Integer/Date). When I pass null from file (csv file), its throwing error like ,, is not a type of integer like that.
Input FIle:
A,B,,D.
kindly let me know how to handle this.

[Anonymous]

Hi,
You can use the expression in tMap to rule out the null data( row.Column==null?0:row.Column )
This can also be done in the Advanced settings in tFilterRow component.
Best regards
Sabrina

[AbiJeev]

hi Sabrina,
  Thank you for the reply, but I want to store the null value as it is. (not as '0'). Moreover am using dynamic schema for loading table from file. Kindly let me know how to achieve this.
Regards,
AbiJeev

[Anonymous]

Hi,
p

Moreover am using dynamic schema for loading table from file. Kindly let me know how to achieve this.

In your job requirment, you need add more transformation logic in dynamic schema?
If so, please have a look at a related forum https://community.talend.com/t5/Design-and-Development/resolved-Generic-Talend-Job/td-p/92354 to see if it is satisfying your needs.
Best regards
Sabrina

[AbiJeev]

Hi Sabrina,
    Dynamic schema is not a problem for me, from File to tMysqlOutput (using dynamic schema), so that i will directly load values from to Mysql for multiple tables. But I am facing problem only during null values, I have defined a column in table as Nullable, but in file it is coming with (A,B ,,D) like ",,". And while loading that empty string it is not considering as Null. Its throwing error like cannot load Table,  ,, is not a type of integer like that.

[Anonymous]

Hi
In order to use dynamic schema, the first line of the file must be header. As topic: https://community.talend.com/t5/Design-and-Development/resolved-Generic-Talend-Job/td-p/92354showed, try to access the column value, print the value to see what it is if needed, and then convert the value to null or other value if it is ,, or special characters.
Best regards
Shong

[Anonymous]

Hi,
As of now there is no way to handle null values for file in Talend.
For e.g. If a File has a null value for a field, Talend can read only as a blank string. Same is the case when talend tries to insert null value in a file. It automatically converts null into an empty string.
This is in contrast to other ETL tools like SAP BODS and Datastage where specific option of null handling with files are provided.
As temporary measure, you may keep the integer column as varchar for file.

[AbiJeev]

Hi Shong,
  The problem here, I don't know which column will come as null (,,) in file. Now in File am getting 0000 instead of null(,,). Is there any way to convert 0000 to null  for Dynamic schema, am not sure how to do this in tJavaFlex.

[Anonymous]

You have to hard code to convert 0000 to null if the component read empty string as "0000", for example:
...
ncount=row7.c.getColumnCount();
column=row7.c.getColumnMetadata(ncount-1);
String columnName=column.getName();
String columnValue=(String)row7.c.getColumnValue(columnName);
if(columnValue.contains("0000")){
row7.c.setColumnValue(ncount-1, null);
row9.c=row7.c;

Best regards
Shong

[AbiJeev]

hi Shong,
   Thanks for your reply, but column name is not 0000 only the column value is 0000 and also I don't know the exact column with value 0000, it may be in column 1 or column 2 or etc. (Processing many files with this dynamic schema). SO is there any way to replace that 0000 with null in dynamic Metadata.
Or is there any way to convert dynamic to String , then use replace All function and convert String to Dynamic?  I have changed Dynamic to String, but not sure how to convert String to Dynamic again.