topic Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25"" in Talend Studio

Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

Anonymous — Sat, 16 Nov 2024 05:56:39 GMT

Hi ,

I have inserted rows in big query date field with format yyy-mm-dd when trying to access it talend I am getting this error message .

Starting job RunJobMay01 at 00:05 02/05/2019.

[statistics] connecting to socket on port 4044
[statistics] connected
Running Query : SELECT * FROM `sample-237304.dataset1.sample_emp`
Exception in component tBigQueryInput_1 (RunJobMay01)
java.lang.NumberFormatException: For input string: "2017-10-25"
at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source)
at sun.misc.FloatingDecimal.parseDouble(Unknown Source)
at java.lang.Double.parseDouble(Unknown Source)
at routines.system.ParserUtils.parseTo_Date(ParserUtils.java:299)
at project1.runjobmay01_0_1.RunJobMay01.tBigQueryInput_1Process(RunJobMay01.java:1497)
at project1.runjobmay01_0_1.RunJobMay01.runJobInTOS(RunJobMay01.java:2633)
at project1.runjobmay01_0_1.RunJobMay01.main(RunJobMay01.java:2482)
[statistics] disconnected

Job RunJobMay01 ended at 00:05 02/05/2019. [exit code=1]

Thanks and Regards,

Aarthi Mohan

Re: Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

manodwhb — Thu, 02 May 2019 09:55:17 GMT

@maarthi , since you were getting data as 2017-10-25 this,this when you were try to convert to number then you will get this error.

My question is why are you converting to number since the data looks like date data yyyy-mm-DD format?

Re: Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

vapukov — Thu, 02 May 2019 10:15:32 GMT

Hi,

in 99.99% of cases it is mean - or wrong schema or order of columns in query not the same as in schema

example:

select
     date,
     int,
     string
from table

with a schema:

int,
date,
string

return similar error

this is why select * not always good approach

Re: Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

Anonymous — Thu, 02 May 2019 10:54:18 GMT

Hi I am not converting just defined a schema

my date field column in bigquery has join_dt column with Date data type -- date '2013-10-11'

In edit schema screen of talend I a created the same field with Date pattern as "yyyy-mm-dd"

is that correct please let me know

or should i make any changes to my data please let me know

Thanks in advance

Screenshot (2).png

Re: Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

vapukov — Thu, 02 May 2019 11:04:21 GMT

@maarthi wrote:

Hi I am not converting just defined a schema

my date field column in bigquery has join_dt column with Date data type -- date '2013-10-11'

In edit schema screen of talend I a created the same field with Date pattern as "yyyy-mm-dd"

but right on your picture - you use MM-dd-yyyy

this is one of the possible reasons - wrong schema

Re: Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

Anonymous — Thu, 02 May 2019 11:15:01 GMT

Even after removing the date pattern value I face the same issue

Screenshot (3).png

Re: Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

vapukov — Thu, 02 May 2019 11:38:03 GMT

could you attach few rows from your table?

Re: Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

Anonymous — Thu, 02 May 2019 20:38:59 GMT

I have attached the Source data which I have used for my mapping .

and my insert query would be

insert into `sample-237304.dataset1.src_emp1`(`empno`,`firstname`,`lastname`,`location`, `salary`, `join_dt`)values(100,'Teresa', 'Willbert', 'Santa Clara', 6700.00, date '2013-10-10'),(101, 'Tarun', 'Kumar', 'Los Angeles', 7800.00, date '2008-07-17'),(102, 'Savitha', 'Venkat', 'San Dieago', 7100.00, date '2008-04-20'),(103, 'Harish', 'Kumar', 'Glendale', 8100.00,date '2009-11-20'),(104, 'Nancy', 'Grey', 'Fresno', 5600.00, date '2009-09-22'),(105,'Banu', 'Sameera', 'Fremont', 3100.00, date '2015-05-13'), (106, 'Clinton', 'Rozario', 'Dublin', 6700.00, date '2016-09-13'),(107, 'Yamini', 'Shankar', 'Cupertino', 6500.00, date '2018-11-26'),(108, 'Deepika', 'Charan', 'Long Beach', 6170.00, date '2013-10-14'),(109, 'Sam', 'Andreson', 'San Francisco', 4100.00, date '2016-10-25')

Screenshot (5).png

Re: Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

vapukov — Fri, 03 May 2019 01:46:45 GMT

you have an error in input component, so on this step - it is not important what in insert query

also in your input query, you use sample_empl dataset, and on the picture - src_empl1, so again - we can not be sure both are the same