One Star

String to Double conversion, null/empty string issue.(Excel/SQLServer)

Hello Talend forums, I've run into a bit of an issue that I've been searching Google and these forums for for hours now, and have tried just about everything I've seen with unfinished results.
What I'm attempting to do is to take data from Excel and place it into a SQL Server table. Everything is fine except one column. The SQL Server column it represents needs to be a Double, and is checked Nullable in the tMap, and it's brought in from the Excel file as a String, also checked Nullable in the tMap. Now the issue becomes the data itself, and the fact that I can't really get it correct. In the Excel file, there is a row named "Norm", that contains a percentage as double(45.2838104) and it is very easy to just Double.parseDouble(row1.Norm) and get all of the columns that have values, however when a row has no value for that column it will appear as % in the Excel file. Now I've tried various ways of getting around this, including (row1.Norm == "%" ? null : row1.Norm) and then parsing that as a Double, creating another column in Excel that has an IF statement to turn any "%" into a "" and using that one in Excel instead.
Depending on the change attempt, I will get various errors or complete row skips. For the row skips, the message is in red text and says "For input string: "%"". For the errors, I've had "java.lang.NumberFormatException: For input string: "%"" when I attempt to parse the string as a Double without any code in between, and I've even gotten the empty string error a few times, which I saw the solution for, but could not find the checkbox for "allow empty strings" from another thread.
The job is designed as a tFileInputExcel ---> tMap ---> tMSSqlOutput, The tMap can be all over the board depending on what I try. Unfortunately the Excel data cannot be changed in any way, or else I would have just created some VBA code to change any "%" sign to a deleted cell.
If anyone can help me, I would greatly appreciate it. If you have any questions or specific screenshot requests, I will be happy to post them.
Thank you.
Edit: Sorry, Talend Open Studio for MDM(5.3.1.r104014), Windows 7, Microsoft Office 2007 and MS SqlServer 2008 Express
9 REPLIES
Seventeen Stars

Re: String to Double conversion, null/empty string issue.(Excel/SQLServer)

This compare (row1.Norm == "%" ? null : row1.Norm) does not work.
Please compare Strings with the equals method:
("%".equals(row1.Norm) ? null : row1.Norm)
One Star

Re: String to Double conversion, null/empty string issue.(Excel/SQLServer)

Thanks jlolling.
Upon doing as you suggested, I appear to still get the same results with the NullPointerException. Every field is still set to Nullable (the output and input is Double, not double and the box is checked) and it still will not accept it. If you have any other suggestions I would greatly appreciate it.
One Star

Re: String to Double conversion, null/empty string issue.(Excel/SQLServer)

I think it should be
(row1.Norm).contains("%") ? null : row1.Norm
or
(row1.Norm).equals("%") ? null : row1.Norm
One Star

Re: String to Double conversion, null/empty string issue.(Excel/SQLServer)

Relational.ISNULL(row1.Norm)?nullSmiley Sadrow1.Norm).trim().contains("%") ? null : row1.Norm
One Star

Re: String to Double conversion, null/empty string issue.(Excel/SQLServer)

Unfortunately with both of those, I still get the same NullPointerException. Is anyone aware of some potential options or settings I may have missed?
I do appreciate the help, though.
One Star

Re: String to Double conversion, null/empty string issue.(Excel/SQLServer)

Hi jxb9288,
Show some screenshots of your job/component? Probably some other column or field is causing this issue.
G
One Star

Re: String to Double conversion, null/empty string issue.(Excel/SQLServer)

hi ,

in tmap expression filter how to check for string column not having null value
something like this expression filter :
!row1.sp_id.equals("null") , i want only not null values to be passed for output
its not working. how to check not null for string
Moderator

Re: String to Double conversion, null/empty string issue.(Excel/SQLServer)

Hi naranibhanu,
Please try this expression in your tMap
row1.sp_id==null ||row1.sp_id.isEmpty())?"Unknown":row1.sp_id

let me know if it is OK with you.
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: String to Double conversion, null/empty string issue.(Excel/SQLServer)

You can use Ralational.ISNULL(row1.sp_id) to check null.