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

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

Anonymous — Tue, 08 Oct 2013 17:45:45 GMT

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

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

Anonymous — Tue, 08 Oct 2013 20:44:14 GMT

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

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

Anonymous — Tue, 08 Oct 2013 21:20:45 GMT

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.

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

Anonymous — Wed, 09 Oct 2013 09:50:33 GMT

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

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

Anonymous — Wed, 09 Oct 2013 10:27:44 GMT

Relational.ISNULL(row1.Norm)?null row1.Norm).trim().contains("%") ? null : row1.Norm

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

Anonymous — Wed, 09 Oct 2013 13:16:41 GMT

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.

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

Anonymous — Wed, 06 Nov 2013 09:35:02 GMT

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

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

Anonymous — Tue, 21 Jan 2014 05:34:59 GMT

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

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

Anonymous — Tue, 21 Jan 2014 07:30:47 GMT

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

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

Anonymous — Wed, 22 Jan 2014 05:54:10 GMT

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