topic Re: [resolved] Null column in Databse in Talend Studio

[resolved] Null column in Databse

AbiJeev — Sat, 16 Nov 2024 11:27:59 GMT

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.

Re: [resolved] Null column in Databse

Anonymous — Fri, 17 Oct 2014 08:52:51 GMT

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

Re: [resolved] Null column in Databse

AbiJeev — Thu, 23 Oct 2014 08:11:47 GMT

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

Re: [resolved] Null column in Databse

Anonymous — Fri, 24 Oct 2014 08:05:51 GMT

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

Re: [resolved] Null column in Databse

AbiJeev — Fri, 24 Oct 2014 08:38:33 GMT

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.

Re: [resolved] Null column in Databse

Anonymous — Fri, 24 Oct 2014 08:49:28 GMT

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

Re: [resolved] Null column in Databse

Anonymous — Fri, 24 Oct 2014 11:18:30 GMT

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.

Re: [resolved] Null column in Databse

AbiJeev — Fri, 24 Oct 2014 15:23:44 GMT

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.

Re: [resolved] Null column in Databse

Anonymous — Mon, 27 Oct 2014 01:45:58 GMT

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

Re: [resolved] Null column in Databse

AbiJeev — Mon, 27 Oct 2014 06:53:41 GMT

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.

Re: [resolved] Null column in Databse

Anonymous — Tue, 28 Oct 2014 05:03:29 GMT

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

Re: [resolved] Null column in Databse

AbiJeev — Tue, 28 Oct 2014 06:20:43 GMT

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

Re: [resolved] Null column in Databse

Anonymous — Tue, 28 Oct 2014 07:23:12 GMT

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

Re: [resolved] Null column in Databse

AbiJeev — Tue, 28 Oct 2014 07:53:18 GMT

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.

Re: [resolved] Null column in Databse

Anonymous — Wed, 29 Oct 2014 10:26:07 GMT

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

Re: [resolved] Null column in Databse

AbiJeev — Thu, 30 Oct 2014 15:03:03 GMT

hi Shong,
Thank you so much.

Re: [resolved] Null column in Databse

AbiJeev — Thu, 06 Nov 2014 06:30:15 GMT

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?