One Star

[resolved] Null column in Databse

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: [resolved] Null column in Databse

Hi 
Yes, the above code only check the last column, if the problem might exist in any a column, you need to iterate each column, for example:
ncount=row1.c.getColumnCount();
for(int i=0;i<ncount;i++){
column=row1.c.getColumnMetadata(i);
columnName=column.getName();
String columnValue=(String)row1.c.getColumnValue(columnName);
if(columnValue.contains("0000")){
row1.c.setColumnValue(i, null);
}
}


Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
16 REPLIES
Moderator

Re: [resolved] Null column in Databse

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Null column in Databse

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
Moderator

Re: [resolved] Null column in Databse

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 http://www.talendforge.org/forum/viewtopic.php?id=27719 to see if it is satisfying your needs.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Null column in Databse

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.
Community Manager

Re: [resolved] Null column in Databse

Hi
In order to use dynamic schema, the first line of the file must be header. As topic:27719 showed, 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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Null column in Databse

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

Re: [resolved] Null column in Databse

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.
Community Manager

Re: [resolved] Null column in Databse

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Null column in Databse

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.
Community Manager

Re: [resolved] Null column in Databse

Hi
I have written a wrong Java code in my previous post, it is column value that should be checked and convert to null if it is '0000', I have edited my post. There is no a direct way to replace 0000 with null, you have to write Java code to do it.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Null column in Databse

Hi Shong,
  Thank you so much for the code, will it replace only the last before column value as null?, because Am not sure of the column 
Community Manager

Re: [resolved] Null column in Databse

Hi Shong,
  Thank you so much for the code, will it replace only the last before column value as null?, because Am not sure of the column 

I don't understand your question. BTW, are you a Java programmer?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Null column in Databse

hi Shong,
The below code will replace only the last column value correct? I don't know which column I will get that 0000 value it may be 1st column or 4th or last. And I am not a Java programmer, I know only the basics of Java.
Community Manager

Re: [resolved] Null column in Databse

Hi 
Yes, the above code only check the last column, if the problem might exist in any a column, you need to iterate each column, for example:
ncount=row1.c.getColumnCount();
for(int i=0;i<ncount;i++){
column=row1.c.getColumnMetadata(i);
columnName=column.getName();
String columnValue=(String)row1.c.getColumnValue(columnName);
if(columnValue.contains("0000")){
row1.c.setColumnValue(i, null);
}
}


Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Null column in Databse

hi Shong,
Thank you so much.
One Star

Re: [resolved] Null column in Databse

hi ,
  One more doubt is there a way to remove one column from dynamic schema from incoming file based on column name before loading into database?